Combo look up, format from number to text field

A

Alan

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
 
D

Douglas J. Steele

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!
 
R

RonaldoOneNil

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

Douglas J. Steele

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 said:
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 said:
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
Click to expand...
Click to expand...
 
A

Alan

Douglas J. Steele said:
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 said:
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
Click to expand...


.I'm still getting the operand error,
Click to expand...

i've taken out they # in customer no as well.

please help
 
A

Alan

Douglas J. Steele said:
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 said:
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 said:
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
Click to expand...
Click to expand...

Sorry to be a pain,
Click to expand...

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??
 
J

John W. Vinson

i've taken out they # in customer no as well.

please help
Click to expand...

Please post your actual fieldnames, datatypes (you're not using Lookup fields
are you?) and your current actual code.
 
A

Alan

Customer No is a text feild because it beingthey being with C
Posting date is a Date field
Amount is a number field
 
J

John W. Vinson

Customer No is a text feild because it beingthey being with C
Posting date is a Date field
Amount is a number field
Click to expand...

Then correct the error in your expression. If you would like help doing so
please post it. I can't see it from here!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

combo box look up text box error 1
#ERROR in textbox 21
Looking up 2 combo boxes and then calculate another field 1
#error in text box 3
sum combo box based on other combo box 2
Combo Box Look up a date 1
Combo Box to look up autonumber field in a form 2
Trying to save auto-populated data in a text table 3

Top