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
  #1  
Old March 25th, 2010, 10:57 AM posted to microsoft.public.access.forms
Alan
external usenet poster
 
Posts: 459
Default #ERROR in textbox


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







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

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

  #3  
Old March 25th, 2010, 12:45 PM posted to microsoft.public.access.forms
RonaldoOneNil
external usenet poster
 
Posts: 345
Default #ERROR in textbox

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

"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







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

No Customer Number is a Text Field and posting Date is a Date 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

.

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

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


"RonaldoOneNil" wrote:

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

"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







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

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

.

  #7  
Old March 25th, 2010, 02:27 PM posted to microsoft.public.access.forms
RonaldoOneNil
external usenet poster
 
Posts: 345
Default #ERROR in textbox

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 & "'")





"Alan" wrote:

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


"RonaldoOneNil" wrote:

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

"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







  #8  
Old March 25th, 2010, 03:09 PM posted to microsoft.public.access.forms
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default #ERROR in textbox

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

.

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

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.


RonaldoOneNil wrote:
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


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

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

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

.

 




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 06:17 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.