Help using DSUM with Textboxes

[RunTime91]

Greetings Experts ~

I am having difficulty getting a second textbox to display the correct total of hours based on a date entered into a first textbox.

Setup:

Table Name: TestTable1
Fields: 'RequestDate' & 'Hours'
Form: 'Form1'
2 Textboxes Unbound: Named 'Date' & 'Total'


What I am trying to accomplish:

Based upon a date entered into the "Date" textbox I want the "Total" textbox to display the total hours associated with that date.


What I have tried:

I have tried using, in the control source property of the "Total" textbox, many iterations of both Sum(IIF & DSum(

I am currently using the following:

=DSum("[Hours]","TestTable1","[RequestDate]='Forms! Form1!'Me.Date'")

I have tried this without the Forms designation; without the "Me" designation; Etc.


Some attempts return the Error or Name error while other efforts return a blank textbox

As always, any help is greatly appreciated

[pbaldy]

Syntax is a little off:

http://access.mvps.org/access/general/gen0018.htm

[RunTime91]

Syntax is a little off:

http://access.mvps.org/access/general/gen0018.htm

pbaldy - Thanks for chiming in ~

Since I am trying to extract a numerical value I changed what I had to:

=DSum("[PTOHours]","TestTable1","[RequestDate]= " & Forms! Form1! Date)

However, when I click 'OK' Access changes it to the following

=DSum("[PTOHours]","TestTable1","[RequestDate]= " & [Forms]![Form1]![Date])

And while it doesn't give me an error - it also doesn't give me any value at all.


I did try the following:

=DSum("[PTOHours]","TestTable1","[RequestDate]= Date")

And it would return the correct value, however, it would only return the correct value if I went from design view to form view.
Once in form view I could enter a new date and nothing would happen, but if I went to design view then back to form view the correct total for the new date would appear.

Getting closer but still need help

Thanks in advance ~

[June7]

Date parameters need # delimiter.

=DSum("[PTOHours]","TestTable1","[RequestDate]= #" & [Forms]![Form1]![Date] & "#")


Date is a reserved word. Should not use reserved words as names. A better convention for naming controls is something like: tbxDate, cbxCustomer, etc.

[RunTime91]

Date parameters need # delimiter.

=DSum("[PTOHours]","TestTable1","[RequestDate]= #" & [Forms]![Form1]![Date] & "#")


Date is a reserved word. Should not use reserved words as names. A better convention for naming controls is something like: tbxDate, cbxCustomer, etc.

pblady & June7 ~ Thank You both very much for your assistance. Naturally, June7, your suggestion worked perfectly. Paul, I realize now the link you posted in your reply also contained the correct answer but I was looking at the problem the wrong way. Instead of realizing I was working with a date parameter, I thought since I was trying to extract a numerical value I should use the example for numerical value. June7 I have since changed my textbox 'Date' to: 'TxtDate' - Thank You both very much for your help. This case is solved. Now, onto the next lesson.