newbie - adding a where clause

[cj_steve]

Hello,

I am editing a form and there is a text box with a Control Source listed as: =DSum("[Amt Pd]","County")

I need to adjust this statement where the field Amt Pd is based on a date field in the table named Date_Paid and a Date Field the user enters - Date.

In Sql it would be something like:

SELECT SUM(Amt Pd)
From County
Where Date_Paid <= Date;


How do I edit the Control source to accomodate the date restriction?

Thank you

 

[casey]

Is the criteria field [Date] located on the form? If so, try this...

=DSum("[Amt Pd]","County", "[Date_Paid]<=" & Me.[Date])

You may need to adjust the & Me.[Date] to include the formname where your criteria [Date] is located.

 

[cj_steve]

get prompted for me variable

Hello,

Thank you for your response, When I use the following

=DSum("[Amt Pd]","County", "[Date_Paid]<=" & Me.[Date])

I am prompted for a Me variable. More information:

The field [Date_Paid] is also located in the County table.

The variable Date is located on the current form [Combined Dues Ending]with the following syntax:
=[Forms]![Combined Dues]![ToDate]

I have also tried variations of the following with just empty spaces where the Amt Pd value should be

=DSum("[Amt Pd]","County", "[Date_Paid]<=" & [forms]![Combined Dues Ending]![Date])

hmmm, not quite sure what I am doing wrong

 

[shay]

This may be nothing to do with your actual problem but I'd always advise against using Date as a field or control name to be sure of avoiding conflicts with the Date function.

shay

 

[cj_steve]

Got it!!

Many Thanks,

I added "[County]![Date Pd] and that seems to work.

Thanks again