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. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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 | |
|
|