#ERROR in textbox
in [Form]
|
Prev: Is it possible to create a form that resembles a table?
Next: Can you add a tab control inside another tab control
From: Alan on 25 Mar 2010 08:38 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 > > . >
From: RonaldoOneNil on 25 Mar 2010 09:27 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 > > > > > > > > > > > > > > > > > > > > >
From: Daryl S on 25 Mar 2010 10:09 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 > > > > . > >
From: BruceM via AccessMonster.com on 25 Mar 2010 10:11 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
From: Alan on 25 Mar 2010 11:00
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 > > > > > > . > > > |