Date Query via DoCMD

[Tim Loeffelholz]

I am writing a small application for a coffee shop that
wants to replace their Punch Cards with the application.
They want to run a report for any given day in the past
to find out a) How many punches they did b) Who came in
multiple times for coffee.

The problem is that I don't know how to write the code to
allow them to press a Command button, and enter a date to
find their info. They said they would even do something
like "-3 days from today". I've been able to write code
for one date:
"SELECT [Punch].[ID], [Punch].[Punch], [Punch].[PunchDate]
FROM Punch WHERE ((([Punch].[PunchDate])=#11/9/2003#))",
but can't find a way for them to be able to enter a
particular date. I've tried DoCMD.RunSQL,
DoCMD.OpenQuery..and I get the "A RunSQL action requires
an argument consisting of an SQL statement. I'm at my
wits end. Please help!!!!

 

[Guest]

design a form with an unbound field ex. txtMyDate.
The user will type in the date they want.
then the Where clause will look more or less like this:

in your query in the criteria type:

Forms!frmMyForm!txtMyDate

or

"SELECT [Punch].[ID], [Punch].[Punch], [Punch].[PunchDate]
FROM Punch WHERE ((([Punch].[PunchDate])= Forms!frmMyForm!
txtMyDate))

if you want to have info for also 3 days before have a
look in the dateAdd function your criteria can be somthing
like:

Between DateAdd("d", -3 , MyDate) And MyDate
I don't rember the exact syntax, perhaps ( instead of (,)

 

[Tim Loeffelholz]

OK. I understand how that should work..

Private Sub txtMyDate_AfterUpdate()

DoCmd.RunSQL "SELECT [Punch].[ID], [Punch].[Punch],
[Punch].[PunchDate] FROM Punch WHERE ((([Punch].
[PunchDate])= Forms!frmJavaDreams!txtMyDate))"

End Sub

But, when I run this, I get a Run-Time error '2342;
A RunSQL action requires an argument consisting of an SQL
statement.