#ERROR in textbox

[BruceM via AccessMonster.com]

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.

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

[quoted text clipped - 5 lines]

calculated textbox doesn't have the same name as any field in your form's
recordsource.

 

[Alan]

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

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.

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.

--



.

 

[RonaldoOneNil]

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
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

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.

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.

--



.

 

[Alan]

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.

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

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

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.

--



.

 

[Alan]

Correction its bound to column 0 i understand that part,

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
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

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.

--



.

 

[Dirk Goldgar]

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.

 

[Daryl S]

Alan -

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

--
Daryl S

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

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

I just Get #Name error now??

Any suggestion

:

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


:

Apologies Posting Date period is a text field??

:

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

 

[BruceM via AccessMonster.com]

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.

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]

 

[BruceM via AccessMonster.com]

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.

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]