Date from to criteria to filter records

pablavo

Registered User.
Local time
Today, 00:35
Joined
Jun 28, 2007
Messages
189
Hi folks,

I have a form and use code to filter the records with varius drop down lists, for example, a name drop down list to filter records with only that name. At the end of the code I just use docmd.Applyfilter, strwhere

I want to use two dates to filter the records. I've only ever done this when it has been one date within the table, and I would use from and to txtboxes for that one date.

I've never done it so that the txtFrom relates to the "from" date in the table and the txtTo date relates to the "To" date in the table.

I've no idea how i'd do this. I mean, once I put the code down for the txtFrom "From", the code for the "To" date would have to take into consideration that it would have to start at the "From" date and not the beginning of time, if that makes sense!?

does anyone know how to do this or have spare code lying about that'll do the job?

Thanks for all help:)
 
If you want to have a date range as one of your filters you could use the following code. This code uses 4 fields to get the start date and Start Time as well as the End Date and End Time. I use 24 hour Time.
The 4 field names are StartDate, StartTime, EndDate, EndTime. I went through a little extra steps to put AM or PM on the Time.

If IsNull([StartTime]) Then [StartTime] = "0001"
If Left([StartTime], 2) = 12 Then
VarStart = [StartDate] & " " & Left([StartTime], 2) & ":" & Right([StartTime], 2) & " " & "PM"
GoTo EndTime
End If
If Left([StartTime], 2) > 12 Then VarStart = [StartDate] & " " & Left([StartTime], 2) - 12 & ":" & Right([StartTime], 2) & " " & "PM" Else VarStart = [StartDate] & " " & Left([StartTime], 2) & ":" & Right([StartTime], 2) & " " & "AM"
EndTime:
If IsNull([EndTime]) Then [EndTime] = 2359
If Left([EndTime], 2) = 12 Then
VarFinish = [EndDate] & " " & Left([EndTime], 2) & ":" & Right([EndTime], 2) & " " & "PM"
GoTo NextLine
End If
If Left([EndTime], 2) > 12 Then VarFinish = [EndDate] & " " & Left([EndTime], 2) - 12 & ":" & Right([EndTime], 2) & " " & "PM" Else VarFinish = [EndDate] & " " & Left([EndTime], 2) & ":" & Right([EndTime], 2) & " " & "AM"

The fields are all unbound on an entry form. If you just want to use dates you could use:
VarStart= [text23]
VarFinish= [Text43]

"Select 'fields you want' FROM 'Table' WHERE 'Date Field Here' between #" & VarStart & "# AND #" & VarFinish & "#;"

The pound signs are necessary to tell the code that what is in between the pound signs are dates. Hope this gives you a start. Post again if you need more help.
Tyler
 
Hey Tylar! wow, that's more than I could ask for, thanks for that... it's much appreciated.

Paul
 

Users who are viewing this thread

Back
Top Bottom