Prev: Cannot bind a combo box to a memo field
Next: Refresh subform
From: Alan on 24 Mar 2010 11:34


"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" <Alan(a)discussions.microsoft.com> wrote in message
> news:CC199FBC-8EEE-4783-A76C-24000D9C843C(a)microsoft.com...
> >
> > 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
>
From: BruceM via AccessMonster.com on 24 Mar 2010 11:59
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

From: Alan on 24 Mar 2010 12:12


"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" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote in message
> news:%23vIzlF0yKHA.928(a)TK2MSFTNGP05.phx.gbl...
> > 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" <Alan(a)discussions.microsoft.com> wrote in message
> > news:CC199FBC-8EEE-4783-A76C-24000D9C843C(a)microsoft.com...
> >>
> >> 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??

> .
>
From: John W. Vinson on 24 Mar 2010 13:09
On Wed, 24 Mar 2010 08:34:01 -0700, Alan <Alan(a)discussions.microsoft.com>
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]
From: Alan on 25 Mar 2010 07:10

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 <Alan(a)discussions.microsoft.com>
> 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]
> .
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Cannot bind a combo box to a memo field
Next: Refresh subform