A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

#ERROR in textbox



 
 
Thread Tools Display Modes
  #21  
Old March 26th, 2010, 12:12 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default #ERROR in textbox

Try removing the square brackets from around Me.

If still no success, simplify the expression:

=DSum("Amount","Table1")

If that works, try adding a single criteria:

=DSum("Amount","Table1","[Customer No] = ' " & Me.[cboCustomerNumber] & " ' ")


Keep keep building one element at a time until you find what is causing the
problem.


Alan wrote:
Well this morning i tired a new table completely
had three fields

Customer No as a text
Posting Date Period as a date/time
Amount as a number

two combo boxes

cbocustomerNumber
cboPostingDate
textbox

Tired this

=DSum("Amount","Table1","[Customer No] = '" & [Me].[cboCustomerNumber] & "'
And Format([Posting Date
Period],'yyyymm') = '" & Format([Me].[cboPostingDate],"yyyymm") & "'")

and still got name, now i know that all the field are spelt correcly and in
the right format


Yer i have tried it with Date/time and put the format to yyyy/mm.

[quoted text clipped - 5 lines]
calculated textbox doesn't have the same name as any field in your form's
recordsource.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

  #22  
Old March 26th, 2010, 01:05 PM posted to microsoft.public.access.forms
Alan
external usenet poster
 
Posts: 459
Default #ERROR in textbox


Customer No Posting Period Date Amount
C00001 2009/07 500
C00001 2009/07 500
C00002 2009/07 1000
C00002 2009/07 600
C00001 2009/08 500
C00001 2009/08 500

=DSum("Amount","Table1") That give me the value of 3600
which is correct

=DSum("Amount","Table1","[Customer No] = ' " & Me.[cboCustomerNumber] & " ' ")

This gives me #Name




"BruceM via AccessMonster.com" wrote:

Try removing the square brackets from around Me.

If still no success, simplify the expression:

=DSum("Amount","Table1")

If that works, try adding a single criteria:

=DSum("Amount","Table1","[Customer No] = ' " & Me.[cboCustomerNumber] & " ' ")


Keep keep building one element at a time until you find what is causing the
problem.


Alan wrote:
Well this morning i tired a new table completely
had three fields

Customer No as a text
Posting Date Period as a date/time
Amount as a number

two combo boxes

cbocustomerNumber
cboPostingDate
textbox

Tired this

=DSum("Amount","Table1","[Customer No] = '" & [Me].[cboCustomerNumber] & "'
And Format([Posting Date
Period],'yyyymm') = '" & Format([Me].[cboPostingDate],"yyyymm") & "'")

and still got name, now i know that all the field are spelt correcly and in
the right format


Yer i have tried it with Date/time and put the format to yyyy/mm.

[quoted text clipped - 5 lines]
calculated textbox doesn't have the same name as any field in your form's
recordsource.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

.

  #23  
Old March 26th, 2010, 01:29 PM posted to microsoft.public.access.forms
RonaldoOneNil
external usenet poster
 
Posts: 345
Default #ERROR in textbox

Take the spaces out between your single quotes and double quotes and also is
the Customer No "C00001" the bound column in your combo box.

=DSum("Amount","Table1","[Customer No] = '" & Me.cboCustomerNumber & "'")


"Alan" wrote:


Customer No Posting Period Date Amount
C00001 2009/07 500
C00001 2009/07 500
C00002 2009/07 1000
C00002 2009/07 600
C00001 2009/08 500
C00001 2009/08 500

=DSum("Amount","Table1") That give me the value of 3600
which is correct

=DSum("Amount","Table1","[Customer No] = ' " & Me.[cboCustomerNumber] & " ' ")

This gives me #Name




"BruceM via AccessMonster.com" wrote:

Try removing the square brackets from around Me.

If still no success, simplify the expression:

=DSum("Amount","Table1")

If that works, try adding a single criteria:

=DSum("Amount","Table1","[Customer No] = ' " & Me.[cboCustomerNumber] & " ' ")


Keep keep building one element at a time until you find what is causing the
problem.


Alan wrote:
Well this morning i tired a new table completely
had three fields

Customer No as a text
Posting Date Period as a date/time
Amount as a number

two combo boxes

cbocustomerNumber
cboPostingDate
textbox

Tired this

=DSum("Amount","Table1","[Customer No] = '" & [Me].[cboCustomerNumber] & "'
And Format([Posting Date
Period],'yyyymm') = '" & Format([Me].[cboPostingDate],"yyyymm") & "'")

and still got name, now i know that all the field are spelt correcly and in
the right format


Yer i have tried it with Date/time and put the format to yyyy/mm.

[quoted text clipped - 5 lines]
calculated textbox doesn't have the same name as any field in your form's
recordsource.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

.

  #24  
Old March 26th, 2010, 02:03 PM posted to microsoft.public.access.forms
Alan
external usenet poster
 
Posts: 459
Default #ERROR in textbox


WAHOOO

we have progress i had to change the bound to bound column 1

ID Customer No Posting Period Date Amount

These are my fields for that table

i only placed Customer no in the Row source, so why do i select bound column
1 and not 0??

Also now i'm trying to introduce the posting Period Date combo

=DSum("Amount","Table1","[CustomerNo] = '" & [cboCustomerNumber] & "'" And
Format([Posting Date Period],'yyyymm') = '" &
Format([cboPostingDate],"yyyymm") & "'")

but i get invalid string syntax ???


"RonaldoOneNil" wrote:

Take the spaces out between your single quotes and double quotes and also is
the Customer No "C00001" the bound column in your combo box.

=DSum("Amount","Table1","[Customer No] = '" & Me.cboCustomerNumber & "'")


"Alan" wrote:


Customer No Posting Period Date Amount
C00001 2009/07 500
C00001 2009/07 500
C00002 2009/07 1000
C00002 2009/07 600
C00001 2009/08 500
C00001 2009/08 500

=DSum("Amount","Table1") That give me the value of 3600
which is correct

=DSum("Amount","Table1","[Customer No] = ' " & Me.[cboCustomerNumber] & " ' ")

This gives me #Name




"BruceM via AccessMonster.com" wrote:

Try removing the square brackets from around Me.

If still no success, simplify the expression:

=DSum("Amount","Table1")

If that works, try adding a single criteria:

=DSum("Amount","Table1","[Customer No] = ' " & Me.[cboCustomerNumber] & " ' ")


Keep keep building one element at a time until you find what is causing the
problem.


Alan wrote:
Well this morning i tired a new table completely
had three fields

Customer No as a text
Posting Date Period as a date/time
Amount as a number

two combo boxes

cbocustomerNumber
cboPostingDate
textbox

Tired this

=DSum("Amount","Table1","[Customer No] = '" & [Me].[cboCustomerNumber] & "'
And Format([Posting Date
Period],'yyyymm') = '" & Format([Me].[cboPostingDate],"yyyymm") & "'")

and still got name, now i know that all the field are spelt correcly and in
the right format


Yer i have tried it with Date/time and put the format to yyyy/mm.

[quoted text clipped - 5 lines]
calculated textbox doesn't have the same name as any field in your form's
recordsource.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

.

  #25  
Old March 26th, 2010, 02:04 PM posted to microsoft.public.access.forms
Alan
external usenet poster
 
Posts: 459
Default #ERROR in textbox

Correction its bound to column 0 i understand that part,

"RonaldoOneNil" wrote:

Take the spaces out between your single quotes and double quotes and also is
the Customer No "C00001" the bound column in your combo box.

=DSum("Amount","Table1","[Customer No] = '" & Me.cboCustomerNumber & "'")


"Alan" wrote:


Customer No Posting Period Date Amount
C00001 2009/07 500
C00001 2009/07 500
C00002 2009/07 1000
C00002 2009/07 600
C00001 2009/08 500
C00001 2009/08 500

=DSum("Amount","Table1") That give me the value of 3600
which is correct

=DSum("Amount","Table1","[Customer No] = ' " & Me.[cboCustomerNumber] & " ' ")

This gives me #Name




"BruceM via AccessMonster.com" wrote:

Try removing the square brackets from around Me.

If still no success, simplify the expression:

=DSum("Amount","Table1")

If that works, try adding a single criteria:

=DSum("Amount","Table1","[Customer No] = ' " & Me.[cboCustomerNumber] & " ' ")


Keep keep building one element at a time until you find what is causing the
problem.


Alan wrote:
Well this morning i tired a new table completely
had three fields

Customer No as a text
Posting Date Period as a date/time
Amount as a number

two combo boxes

cbocustomerNumber
cboPostingDate
textbox

Tired this

=DSum("Amount","Table1","[Customer No] = '" & [Me].[cboCustomerNumber] & "'
And Format([Posting Date
Period],'yyyymm') = '" & Format([Me].[cboPostingDate],"yyyymm") & "'")

and still got name, now i know that all the field are spelt correcly and in
the right format


Yer i have tried it with Date/time and put the format to yyyy/mm.

[quoted text clipped - 5 lines]
calculated textbox doesn't have the same name as any field in your form's
recordsource.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

.

  #26  
Old March 26th, 2010, 02:28 PM posted to microsoft.public.access.forms
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default #ERROR in textbox

"Alan" wrote in message
...

Well this morning i tired a new table completely
had three fields

Customer No as a text
Posting Date Period as a date/time
Amount as a number

two combo boxes

cbocustomerNumber
cboPostingDate
textbox

Tired this

=DSum("Amount","Table1","[Customer No] = '" & [Me].[cboCustomerNumber] &
"'
And Format([Posting Date
Period],'yyyymm') = '" & Format([Me].[cboPostingDate],"yyyymm") & "'")


and still got name, now i know that all the field are spelt correcly and
in
the right format



"Me" is not valid in a controlsource, only in VBA code. Remove the "[Me]."
qualifiers.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

  #27  
Old March 26th, 2010, 02:58 PM posted to microsoft.public.access.forms
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default #ERROR in textbox

Alan -

This should be in the Control Source property of the text box.

--
Daryl S


"Alan" wrote:

Daryl

This is correct that i'm putting this code into the source data of a textbox??

i not where should i put it

thanks again i really do appricate you help, just need this to work


"Daryl S" wrote:

Alan -

I changed the cboCustomerNumber assuming it is also a combo box, and the
number is in the first field:

=DSum("[Amount]", "[July 09 - On Billings and Adjustments Data Query]",
"[Customer No] = '" & Me.[cboCustomerNumber].Column(0) & "' AND [Posting
Period Date] = '"
& Me.[cboPostingDate].Column(0) & "'")

If it doesn't work, I would suggest adding the following before the DSum so
you can see what is really being passed:

Debug.Print Me.[cboCustomerNumber].Column(0)
Debug.Print Me.[cboPostingDate].Column(0)

The results will be in the immediate window when the code runs.

Also check the exact spelling of the table and fieldnames, as we can't do
that remotely...

--
Daryl S


"Alan" wrote:

I just Get #Name error now??

Any suggestion

"Daryl S" wrote:

Alan -

If the field in the database is [Posting Period Date], then you have the
field name wrong. Try this:

=DSum("[Amount]", "[July 09 - On Billings and Adjustments Data Query]",
"[Customer No] = '" & [cboCustomerNumber] & "' AND [Posting Period Date] = '"
& Me.[cboPostingDate].Column(0) & "'")

I am assuming cboPostingDate is a combo box, and the date you want is in the
first column. If that date is a text value in the correct format (yyyymm),
then the above should work. If not, you will need to add formatting to that.

--
Daryl S


"Alan" wrote:

Apologies Posting Date period is a text field??

"BruceM via AccessMonster.com" wrote:

Is the bound column of cboCustomerNumber a number field, and are Posting Date
Period and cboPostingDate date fields?

Alan wrote:
I have this code in a text box

= dsum("[Amount]", "[July 09 - On Billings and Adjustments Data Query]",
"[Customer No] = " & [cboCustomerNumber] & " AND Format([Posting Date
Period], ""yyyymm"") = '" & Format([cboPostingDate], "yyyymm") & "'")

i keep getting #error in my text box

i'm struggling to find they answer to why

the query July 09 - On Billings and Adjustments Data Query
has fields of

customer Number
posting periood date
Amount

Now i have two combo boxes on my form which the text box should look up

Customer number and Name, are bound to another table

No (for customer Number)
Name ( for customer Name

Customer Listing Table

not linked to July 09 - On Billings and Adjustments Data Query

Is this what causing my error and how do i fix it

Thanks in advanced

--
Message posted via http://www.accessmonster.com

.

  #28  
Old March 26th, 2010, 03:25 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default #ERROR in textbox

I should have mentioned that I added the spaces for clarity in the posting.
The VBA editor will remove them automatically, and my tests indicate that
expressions don't care if they are there, but I agree that some explanation
would have been good.


RonaldoOneNil wrote:
Take the spaces out between your single quotes and double quotes and also is
the Customer No "C00001" the bound column in your combo box.

=DSum("Amount","Table1","[Customer No] = '" & Me.cboCustomerNumber & "'")

Customer No Posting Period Date Amount
C00001 2009/07 500

[quoted text clipped - 51 lines]
calculated textbox doesn't have the same name as any field in your form's
recordsource.


--
Message posted via http://www.accessmonster.com

  #29  
Old March 26th, 2010, 03:36 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default #ERROR in textbox

When you set the bound column in the combo box property sheet the first
column is identified as 1. There is no 0 column option for the property
sheet, AFAIK. In VBA code or in an expression the first column is 0. It's
inconsistent, but there it is.

Try this:

Dim strWhere as String

strWhere = "[CustomerNo] = '" & [cboCustomerNumber] & _
"'" And Format([Posting Date Period],'yyyymm') = '" & _
Format([cboPostingDate],"yyyymm") & "'"

Debug.Print strWhere

Debug.Print DSum("Amount","Table1",strWhere)

Even if you are using the DSum expression as the Control Source of a text box,
place this code in an event procedure. You could create a test command
button and place it in the Click event. The point is that after running the
code (by clicking the command button) you can press Ctrl + G to view strWhere
and the DSum result in the immediate window. Copy the string from the
immediate window and post it here if you are having trouble evaluating it.

Alan wrote:
WAHOOO

we have progress i had to change the bound to bound column 1

ID Customer No Posting Period Date Amount

These are my fields for that table

i only placed Customer no in the Row source, so why do i select bound column
1 and not 0??

Also now i'm trying to introduce the posting Period Date combo

=DSum("Amount","Table1","[CustomerNo] = '" & [cboCustomerNumber] & "'" And
Format([Posting Date Period],'yyyymm') = '" &
Format([cboPostingDate],"yyyymm") & "'")

but i get invalid string syntax ???

Take the spaces out between your single quotes and double quotes and also is
the Customer No "C00001" the bound column in your combo box.

[quoted text clipped - 56 lines]
calculated textbox doesn't have the same name as any field in your form's
recordsource.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:56 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.