#ERROR in textbox

[Alan]

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]

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

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

 

[RonaldoOneNil]

Because the DSum arguments are enclosed in quotes and you have quotes in your
Format statements.

 

[Alan]

No Customer Number is a Text Field and posting Date is a Date Field.

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

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

 

[Alan]

I Take the quotes out like this

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

Then I get Name error in the text feild

Because the DSum arguments are enclosed in quotes and you have quotes in your
Format statements.

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

 

[Alan]

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?

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

 

[RonaldoOneNil]

You need them around each argument in the DSum statement and it is because of
this that it errors when you have them in your Format statement. It needs to
be something like this

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

I Take the quotes out like this

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

Then I get Name error in the text feild

Because the DSum arguments are enclosed in quotes and you have quotes in your
Format statements.

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

 

[Daryl S]

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?

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]

There is no problem with the quotes in the format statement. I tested before
I posted the suggestion, which was confirmed by an MVP (Douglas Steele?) who
posted in an earlier thread on this topic. Your suggestion looks for an
exact date match, not a month/year match as the OP wants. If an exact date
match is needed it would be best to use date delimiters (#), but again that
is not what is requested here.

The OP stated (although with question marks in the statement, so I'm not
quite sure what to make of that) that Posting Date Period is a text field.
If so, it should be changed to a Date/Time field. The Format function seems
to be able to apply date/time formatting to a text string that "looks" like a
date.

To the OP, be sure the exact field names, not any table field captions you
may have used, are included in the DSum expression. That is typically the
source of the #Name error.

Also, please post some sample data (from just a few records) of the actual
values in Posting Date Period.

You need them around each argument in the DSum statement and it is because of
this that it errors when you have them in your Format statement. It needs to
be something like this

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

I Take the quotes out like this

[quoted text clipped - 38 lines]

 

[Alan]

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

 

[RonaldoOneNil]

Clearly there is a problem because he can't get it to work.
I have just recreated it in a test DB. I got it to work by having single
quotes on the first Format statement and double quotes on the second. My
customer No and Posting Date Period are both text but the Posting Date period
is stored as a dd/mm/yyyy
The folllowing then worked

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

There is no problem with the quotes in the format statement. I tested before
I posted the suggestion, which was confirmed by an MVP (Douglas Steele?) who
posted in an earlier thread on this topic. Your suggestion looks for an
exact date match, not a month/year match as the OP wants. If an exact date
match is needed it would be best to use date delimiters (#), but again that
is not what is requested here.

The OP stated (although with question marks in the statement, so I'm not
quite sure what to make of that) that Posting Date Period is a text field.
If so, it should be changed to a Date/Time field. The Format function seems
to be able to apply date/time formatting to a text string that "looks" like a
date.

To the OP, be sure the exact field names, not any table field captions you
may have used, are included in the DSum expression. That is typically the
source of the #Name error.

Also, please post some sample data (from just a few records) of the actual
values in Posting Date Period.

You need them around each argument in the DSum statement and it is because of
this that it errors when you have them in your Format statement. It needs to
be something like this

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

I Take the quotes out like this

[quoted text clipped - 38 lines]

Thanks in advanced

 

[BruceM via AccessMonster.com]

See my reply in another part of this thread.

I just Get #Name error now??

Any suggestion

Alan -

[quoted text clipped - 45 lines]

 

[RonaldoOneNil]

Forget previous post. It works in VBA on the after update of the combo boxes
but not in the control source of the textbox.

Clearly there is a problem because he can't get it to work.
I have just recreated it in a test DB. I got it to work by having single
quotes on the first Format statement and double quotes on the second. My
customer No and Posting Date Period are both text but the Posting Date period
is stored as a dd/mm/yyyy
The folllowing then worked

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

There is no problem with the quotes in the format statement. I tested before
I posted the suggestion, which was confirmed by an MVP (Douglas Steele?) who
posted in an earlier thread on this topic. Your suggestion looks for an
exact date match, not a month/year match as the OP wants. If an exact date
match is needed it would be best to use date delimiters (#), but again that
is not what is requested here.

The OP stated (although with question marks in the statement, so I'm not
quite sure what to make of that) that Posting Date Period is a text field.
If so, it should be changed to a Date/Time field. The Format function seems
to be able to apply date/time formatting to a text string that "looks" like a
date.

To the OP, be sure the exact field names, not any table field captions you
may have used, are included in the DSum expression. That is typically the
source of the #Name error.

Also, please post some sample data (from just a few records) of the actual
values in Posting Date Period.

You need them around each argument in the DSum statement and it is because of
this that it errors when you have them in your Format statement. It needs to
be something like this

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

I Take the quotes out like this

[quoted text clipped - 38 lines]

Thanks in advanced

 

[Daryl S]

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

 

[Alan]

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

 

[Alan]

Bruce, The cboCustomerNumber is a Text Field and the Posting Date is also a
text field,

any suggestions

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

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]

As I wrote in another part of this thread:

Posting Date Period should be changed to a Date/Time field. The Format
function seems
to be able to apply date/time formatting to a text string that "looks" like a
date, but if it is a date it should be stored in a date/time field.

Be sure the exact field names, not any table field captions you
may have used, are included in the DSum expression. Naming problems are
typically the
source of the #Name error.

Also, please post some sample data (from just a few records) of the actual
values in Posting Date Period.

You have been exchanging some postings with Daryl S. I suggest that you stay
in one part of the thread (the Daryl S part would be good), as it is
difficult to know what you have tried when you questions are replies are
scattered around the thread.

Bruce, The cboCustomerNumber is a Text Field and the Posting Date is also a
text field,

any suggestions

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

[quoted text clipped - 30 lines]

 

[Alan]

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

i'm gonna start again tomorrow morning an see what happens,

i know its something i'm doing an it really fustrating lol

As I wrote in another part of this thread:

Posting Date Period should be changed to a Date/Time field. The Format
function seems
to be able to apply date/time formatting to a text string that "looks" like a
date, but if it is a date it should be stored in a date/time field.

Be sure the exact field names, not any table field captions you
may have used, are included in the DSum expression. Naming problems are
typically the
source of the #Name error.

Also, please post some sample data (from just a few records) of the actual
values in Posting Date Period.

You have been exchanging some postings with Daryl S. I suggest that you stay
in one part of the thread (the Daryl S part would be good), as it is
difficult to know what you have tried when you questions are replies are
scattered around the thread.

Bruce, The cboCustomerNumber is a Text Field and the Posting Date is also a
text field,

any suggestions

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

[quoted text clipped - 30 lines]

Thanks in advanced

 

[Dirk Goldgar]

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

i'm gonna start again tomorrow morning an see what happens,

i know its something i'm doing an it really fustrating lol

If you have verified that the expression should work, make sure that this
calculated textbox doesn't have the same name as any field in your form's
recordsource.

 

[Alan]

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