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 |
#1
|
|||
|
|||
Combo look up, format from number to text field
Hi I was given this code to look up on two combo box name Customer Number Posting Date Period which then a third combo box would look up on these two combo box an sum the value Amount field in a table for example Customer Number C00001 Posting Date 2009/07 Amount £5000 This is my code so far =DSum("Amount", " July 09 - On Billings and Adjustments Data Query ", " Format (Customer No#, "text) = " & Format (cboCustomerNumber.Value, "text") & " AND Format(Posting Date Period, "yyyymm) = "'" & Format(cboPostingDate.Value, "yyyymm") & "'") i tired changing the format of the customer number because it begins with a C so must be a text field, but i keep getting the same old error Invaild syntax You may have enetred an operand without and operator can anyone help many thanks |
#2
|
|||
|
|||
Combo look up, format from number to text field
Since July 09 - On Billings and Adjustments Data Query has spaces in it,
you need to put square brackets around it (and presumably there aren't actually spaces at the beginning and end of the name (same with field names, btw). Also, I don't think there's a need to format Customer Number. =DSum("Amount", "[July 09 - On Billings and Adjustments Data Query]", " [Customer No#] = " & cboCustomerNumber & " AND Format([Posting Date Period], "yyyymm) = "'" & Format(cboPostingDate, "yyyymm") & "'") If Customer No# is a text field, you'll need =DSum("Amount", "[July 09 - On Billings and Adjustments Data Query]", " [Customer No#] = '" & cboCustomerNumber & "'" & " AND Format([Posting Date Period], "yyyymm) = "'" & Format(cboPostingDate, "yyyymm") & "'") Incidentally, do yourself a HUGE favour, and stop using special characters (like # and spaces) in your field and table names! -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Alan" wrote in message ... Hi I was given this code to look up on two combo box name Customer Number Posting Date Period which then a third combo box would look up on these two combo box an sum the value Amount field in a table for example Customer Number C00001 Posting Date 2009/07 Amount £5000 This is my code so far =DSum("Amount", " July 09 - On Billings and Adjustments Data Query ", " Format (Customer No#, "text) = " & Format (cboCustomerNumber.Value, "text") & " AND Format(Posting Date Period, "yyyymm) = "'" & Format(cboPostingDate.Value, "yyyymm") & "'") i tired changing the format of the customer number because it begins with a C so must be a text field, but i keep getting the same old error Invaild syntax You may have enetred an operand without and operator can anyone help many thanks |
#3
|
|||
|
|||
Combo look up, format from number to text field
Look out for removing spaces insided double quotes, single quotes around text
values & square brackets on field names with spaces in. =DSum("Amount","July 09 - On Billings and Adjustments Data Query","[Customer Number] = '" & cboCustomerNumber & "' AND [Posting Date Period] = '" & cboPostingDate & "'") "Alan" wrote: Hi I was given this code to look up on two combo box name Customer Number Posting Date Period which then a third combo box would look up on these two combo box an sum the value Amount field in a table for example Customer Number C00001 Posting Date 2009/07 Amount £5000 This is my code so far =DSum("Amount", " July 09 - On Billings and Adjustments Data Query ", " Format (Customer No#, "text) = " & Format (cboCustomerNumber.Value, "text") & " AND Format(Posting Date Period, "yyyymm) = "'" & Format(cboPostingDate.Value, "yyyymm") & "'") i tired changing the format of the customer number because it begins with a C so must be a text field, but i keep getting the same old error Invaild syntax You may have enetred an operand without and operator can anyone help many thanks |
#4
|
|||
|
|||
Combo look up, format from number to text field
Because quotes tend to confound me, and the quotes didn't look quite right, I
tried an experiment, except I used StartDate in one of my tables. In a text box double click event on an existing form: Dim strWhere As String strWhere = "Format([StartDate], "yyyymm) = "'" & Format(txtStartDate, "yyyymm") & "'" Debug.Print strWhere MsgBox = DCount("*", "tblBatch", strWhere) I tried adding closing quotes around the first yyyymm, and a lot of other combinations, until I found: strWhere = "Format([StartDate], ""yyyymm"") = " & Format(Me.txtStartDate, "yyyymm") The second part of the expression behaves as if it is a string variable. In fact, it worked when I substituted a string variable for the part of the expression after the ampersand: Dim str1 as String, strWhere as String str1 = Format(Me.txtStartDate, "yyyymm") strWhere = "Format([StartDate], ""yyyymm"") = " & str1 I expect there are about a dozen other ways to do that. Just making an observation, and expressing some frustration with quote marks. Douglas J. Steele wrote: Since July 09 - On Billings and Adjustments Data Query has spaces in it, you need to put square brackets around it (and presumably there aren't actually spaces at the beginning and end of the name (same with field names, btw). Also, I don't think there's a need to format Customer Number. =DSum("Amount", "[July 09 - On Billings and Adjustments Data Query]", " [Customer No#] = " & cboCustomerNumber & " AND Format([Posting Date Period], "yyyymm) = "'" & Format(cboPostingDate, "yyyymm") & "'") If Customer No# is a text field, you'll need =DSum("Amount", "[July 09 - On Billings and Adjustments Data Query]", " [Customer No#] = '" & cboCustomerNumber & "'" & " AND Format([Posting Date Period], "yyyymm) = "'" & Format(cboPostingDate, "yyyymm") & "'") Incidentally, do yourself a HUGE favour, and stop using special characters (like # and spaces) in your field and table names! Hi I was given this code to look up on two combo box name [quoted text clipped - 33 lines] many thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201003/1 |
#5
|
|||
|
|||
Combo look up, format from number to text field
Oops, that actually needs to be
=DSum("Amount", "[July 09 - On Billings and Adjustments Data Query]", " [Customer No#] = " & cboCustomerNumber & " AND Format([Posting Date Period], ""yyyymm"") = " & Format(cboPostingDate, "yyyymm")) or =DSum("Amount", "[July 09 - On Billings and Adjustments Data Query]", " [Customer No#] = " & cboCustomerNumber & " AND Format([Posting Date Period], ""yyyymm"") = '" & Format(cboPostingDate, "yyyymm") & "'") -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Douglas J. Steele" wrote in message ... Since July 09 - On Billings and Adjustments Data Query has spaces in it, you need to put square brackets around it (and presumably there aren't actually spaces at the beginning and end of the name (same with field names, btw). Also, I don't think there's a need to format Customer Number. =DSum("Amount", "[July 09 - On Billings and Adjustments Data Query]", " [Customer No#] = " & cboCustomerNumber & " AND Format([Posting Date Period], "yyyymm) = "'" & Format(cboPostingDate, "yyyymm") & "'") If Customer No# is a text field, you'll need =DSum("Amount", "[July 09 - On Billings and Adjustments Data Query]", " [Customer No#] = '" & cboCustomerNumber & "'" & " AND Format([Posting Date Period], "yyyymm) = "'" & Format(cboPostingDate, "yyyymm") & "'") Incidentally, do yourself a HUGE favour, and stop using special characters (like # and spaces) in your field and table names! -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Alan" wrote in message ... Hi I was given this code to look up on two combo box name Customer Number Posting Date Period which then a third combo box would look up on these two combo box an sum the value Amount field in a table for example Customer Number C00001 Posting Date 2009/07 Amount £5000 This is my code so far =DSum("Amount", " July 09 - On Billings and Adjustments Data Query ", " Format (Customer No#, "text) = " & Format (cboCustomerNumber.Value, "text") & " AND Format(Posting Date Period, "yyyymm) = "'" & Format(cboPostingDate.Value, "yyyymm") & "'") i tired changing the format of the customer number because it begins with a C so must be a text field, but i keep getting the same old error Invaild syntax You may have enetred an operand without and operator can anyone help many thanks |
#6
|
|||
|
|||
Combo look up, format from number to text field
"Douglas J. Steele" wrote: Since July 09 - On Billings and Adjustments Data Query has spaces in it, you need to put square brackets around it (and presumably there aren't actually spaces at the beginning and end of the name (same with field names, btw). Also, I don't think there's a need to format Customer Number. =DSum("Amount", "[July 09 - On Billings and Adjustments Data Query]", " [Customer No#] = " & cboCustomerNumber & " AND Format([Posting Date Period], "yyyymm) = "'" & Format(cboPostingDate, "yyyymm") & "'") If Customer No# is a text field, you'll need =DSum("Amount", "[July 09 - On Billings and Adjustments Data Query]", " [Customer No#] = '" & cboCustomerNumber & "'" & " AND Format([Posting Date Period], "yyyymm) = "'" & Format(cboPostingDate, "yyyymm") & "'") Incidentally, do yourself a HUGE favour, and stop using special characters (like # and spaces) in your field and table names! -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Alan" wrote in message ... Hi I was given this code to look up on two combo box name Customer Number Posting Date Period which then a third combo box would look up on these two combo box an sum the value Amount field in a table for example Customer Number C00001 Posting Date 2009/07 Amount £5000 This is my code so far =DSum("Amount", " July 09 - On Billings and Adjustments Data Query ", " Format (Customer No#, "text) = " & Format (cboCustomerNumber.Value, "text") & " AND Format(Posting Date Period, "yyyymm) = "'" & Format(cboPostingDate.Value, "yyyymm") & "'") i tired changing the format of the customer number because it begins with a C so must be a text field, but i keep getting the same old error Invaild syntax You may have enetred an operand without and operator can anyone help many thanks .I'm still getting the operand error, i've taken out they # in customer no as well. please help |
#7
|
|||
|
|||
Combo look up, format from number to text field
Have you seen the most recent postings in this thread? If they do not solve
the problem, please be specific about where help is needed. Alan wrote: Since July 09 - On Billings and Adjustments Data Query has spaces in it, you need to put square brackets around it (and presumably there aren't [quoted text clipped - 57 lines] .I'm still getting the operand error, i've taken out they # in customer no as well. please help -- Message posted via http://www.accessmonster.com |
#8
|
|||
|
|||
Combo look up, format from number to text field
"Douglas J. Steele" wrote: Oops, that actually needs to be =DSum("Amount", "[July 09 - On Billings and Adjustments Data Query]", " [Customer No#] = " & cboCustomerNumber & " AND Format([Posting Date Period], ""yyyymm"") = " & Format(cboPostingDate, "yyyymm")) or =DSum("Amount", "[July 09 - On Billings and Adjustments Data Query]", " [Customer No#] = " & cboCustomerNumber & " AND Format([Posting Date Period], ""yyyymm"") = '" & Format(cboPostingDate, "yyyymm") & "'") -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Douglas J. Steele" wrote in message ... Since July 09 - On Billings and Adjustments Data Query has spaces in it, you need to put square brackets around it (and presumably there aren't actually spaces at the beginning and end of the name (same with field names, btw). Also, I don't think there's a need to format Customer Number. =DSum("Amount", "[July 09 - On Billings and Adjustments Data Query]", " [Customer No#] = " & cboCustomerNumber & " AND Format([Posting Date Period], "yyyymm) = "'" & Format(cboPostingDate, "yyyymm") & "'") If Customer No# is a text field, you'll need =DSum("Amount", "[July 09 - On Billings and Adjustments Data Query]", " [Customer No#] = '" & cboCustomerNumber & "'" & " AND Format([Posting Date Period], "yyyymm) = "'" & Format(cboPostingDate, "yyyymm") & "'") Incidentally, do yourself a HUGE favour, and stop using special characters (like # and spaces) in your field and table names! -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Alan" wrote in message ... Hi I was given this code to look up on two combo box name Customer Number Posting Date Period which then a third combo box would look up on these two combo box an sum the value Amount field in a table for example Customer Number C00001 Posting Date 2009/07 Amount £5000 This is my code so far =DSum("Amount", " July 09 - On Billings and Adjustments Data Query ", " Format (Customer No#, "text) = " & Format (cboCustomerNumber.Value, "text") & " AND Format(Posting Date Period, "yyyymm) = "'" & Format(cboPostingDate.Value, "yyyymm") & "'") i tired changing the format of the customer number because it begins with a C so must be a text field, but i keep getting the same old error Invaild syntax You may have enetred an operand without and operator can anyone help many thanks Sorry to be a pain, but i'm just getting #error in the text box i have Customer Number box pulling the Customer number from the customer listing table, and the rest coming from the query table , would this give me my error?? . |
#9
|
|||
|
|||
Combo look up, format from number to text field
On Wed, 24 Mar 2010 08:34:01 -0700, Alan
wrote: i've taken out they # in customer no as well. please help Please post your actual fieldnames, datatypes (you're not using Lookup fields are you?) and your current actual code. -- John W. Vinson [MVP] |
#10
|
|||
|
|||
Combo look up, format from number to text field
Customer No is a text feild because it beingthey being with C Posting date is a Date field Amount is a number field "John W. Vinson" wrote: On Wed, 24 Mar 2010 08:34:01 -0700, Alan wrote: i've taken out they # in customer no as well. please help Please post your actual fieldnames, datatypes (you're not using Lookup fields are you?) and your current actual code. -- John W. Vinson [MVP] . |
|
Thread Tools | |
Display Modes | |
|
|