Prev: Is it possible to create a form that resembles a table?
Next: Can you add a tab control inside another tab control
From: Dirk Goldgar on 26 Mar 2010 09:28
"Alan" <Alan(a)discussions.microsoft.com> wrote in message
news:B97C84D7-CC3E-4E2F-A68B-CE382998D50C(a)microsoft.com...
>
> 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)

From: Daryl S on 26 Mar 2010 09:58
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
> > > > > >
> > > > > > .
> > > > > >
From: BruceM via AccessMonster.com on 26 Mar 2010 10:25
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

From: BruceM via AccessMonster.com on 26 Mar 2010 10:36
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/Forums.aspx/access-forms/201003/1

First  |  Prev  | 
Pages: 1 2 3 4 5 6
Prev: Is it possible to create a form that resembles a table?
Next: Can you add a tab control inside another tab control