Dates in access

[NeilPattison]

The table I want to run the query on has a date in the format of dd/mm/yyyy. I want to be able to run a query so that the user can type in a month and all records with dates in that month will be shown.

I have done this before many years ago but now have a mental block and cant figure it out.

Any help with this would be greatly appreciated

 

[Remou]

You can format the date in the query, for example:

[tt]Select * From tblT Where Month(DateField)=Forms!frmForm!txtMonth And Year(DateField)=Forms!frmForm!txtYear[/tt]

 

[lameid]

Anotherway to go...

Select * From tblT 
Where Datefield Between 
Dateserial(Forms!frmForm!txtYear,Forms!frmForm!txtMonth,1) 

And 

DateAdd("d",-1, 
Dateadd("m",1,
Dateserial(Forms!frmForm!txtYear,Forms!frmForm!txtMonth,1)))

Forgive the extra whitespace in the query. It was the only way I could keep all the terms together right.

While this is harder to follow, it should be faster as any calculations on the fields in the table used as criteria like Remou suggested has to be evaluated for every record and will probably bypass the indexes.

In essence I get the first of the month and use the same expression, add a month to it and subtract a day to get the last day of the month.

You could even use the same logic to put invisible data into controls on your form and then Access would not have a chance to do anything stupid with the criteria.

If you want to start with one date and get the entire month, look into the month and year functions.

 

[PHV]

A shorter way for lameid suggestion:

SELECT *
FROM tblT
WHERE Datefield Between
DateSerial(Forms!frmForm!txtYear,Forms!frmForm!txtMonth,1)
And 
DateSerial(Forms!frmForm!txtYear,1+Forms!frmForm!txtMonth,0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

I like PHV's solution.

Wow, I never bothered to read the text for the dateserial function. It accepts relative values to for the month and day parameters, good to know.