Me.Filter Date/Time field, exclude time

[jonnycakes]

Hello everyone,

Long story short, I have a button that i'm using on a form to allow the end user to filter by selection. This is working great for me, all except with my Date/Time fields.

My goal is to ignore the time in these fields and return every record that was entered that day, but I seem to be missing a key element.

For simplicity, both my field and my control are named "RecordSTS". Any ideas? You're help is greatly appreciated. Thank you in advance!

p.s. I know I need to add some more code to handle blank and null fields.

If Screen.PreviousControl = RecordSTS Then
Me.Filter = "[RecordSTS] like " & Format(Me.RecordSTS, "\#mm\/dd\/yyyy\#")
Me.FilterOn = True
Else
Screen.PreviousControl.SetFocus
DoCmd.RunCommand acCmdFilterBySelection
End If
 

[NeoPa]

There are two options available :

1. Add 1 day to the To DateTime value and use Between From and To.
2. Use the DateValue() function to convert the value stored into just its date constituent.

Stangely, option #1, which is more complicated, would yield more efficient results if/when dealing with a very large dataset. Option #2 would only be noticeably slower on a very large dataset though. Functions called from SQL slow things down, but VBA defined functions slow it down much more so.

[jonnycakes]

Yet again, NeoPa comes to the rescue. Please accept my apology for my delayed response. I hurt my back shortly after I posted this question. I went with option one, because this dataset will continue to grow. At least I think I went with option one, I've included the code that appears to be working. Do you see any issues with this approach, or did I use your suggestion correctly?

Thanks again!!

Dim StartDate as Date
Dim EndDate as Date
StartDate = Format(Me!RecordSTS, "mm-dd-yyyy")
EndDate = DateAdd("D", 1, Format(Me!RecordSTS, "mm-dd-yyyy"))
 
If Screen.PreviousControl = RecordSTS then
Me.Filter = "[RecordSTS] between #" & StartDate & "# AND #" & EndDate & "#"
Me.filter = true
Else
Screen.PreviousControl.SetFocus
DoCmd.RunCommand acCmdFilterBySelection
End if 
 

[NeoPa]

Hi there Jonny. Delays are never a problem. Let's have a look at your code, as you've posted it.

1. When posting code, it is almost always necessary to post the procedure wrapper. This is almost always the most useful part of any procedure as far as passing information is concerned. Never post code that hasn't had a compile at least attempted, without including a clear warning that you are posting air-code. Line #8 indicates this code has not been copied from the VBA Editor window (or else you have somehow managed to teach your database that True is spelled true). Both possibilities indicate problems.
2. Lines #3 & #4 both seem to be treating the string value returned from Format() as if it were a date. It can be converted for you automatically, but no reason to convert it just so that it can be converted back for use. I don't believe you need Date variables for this code.
3. Line #6 compares two controls, but is really only comparing the values. If you want to check that the previous control actually is Me.RecordSTS then use the Is comparison.
4. Line #7 seems to indicate your understanding of filtering dates in SQL has regressed. Post #1 illustrated the correct approach (See Literal DateTimes and Their Delimiters (#)).
5. Lines #10 & #11 seem so obscure to me that I was unable to determine what it is you were trying to achieve by them - hence I cannot comment intelligently on them.

I would be looking to see something like (assuming Me.RecordSTS represents a date only value) :

Dim strWhere As String
 
strWhere = Replace("([RecordSTS] Between #%F# And #%T#)", _
                   "%F", Format(CDate(Me.RecordSTS), "m\/d\/yyyy")
strWhere = Replace(strWhere, "%T", Format(CDate(Me.RecordSTS) + 1, "m\/d\/yyyy")
If Screen.PreviousControl Is Me.RecordSTS Then
    Me.Filter = strWhere
    Me.FilterOn = True
Else
    Screen.PreviousControl.SetFocus
    DoCmd.RunCommand acCmdFilterBySelection
End If

[jonnycakes]

NeoPa, I cannot thank you enough for the education that you've provided me, thank you! I have noted everything that you have shared with me. I've also learned a valuable lesson, and that is, never type out your code on your iPhone :)

The only thing that I can add to your great solution is that, lines 3 and 5 require an extra right parentheses to close the replace function.

NeoPa, you're doing a great service to this community, thanks again.

[NeoPa]

JonnyCakes:
The only thing that I can add to your great solution is that, lines 3 and 5 require an extra right parentheses to close the replace function.

Touché Jonny :-D You caught me out in breaking my own cardinal rule! And expressed it so delicately too. I couldn't laugh out loud as I would have had to explain to all the people around, but it fixed a big smile on my face nevertheless. I'm afraid I do post air-code quite a lot if truth be told. Nevertheless, I do go to great pains to ensure it is as accurate as I can without the necessity to build a matching project for every thread I answer (which could get old after a while).

PS. If you need to type it out on the iPhone then do, but always be as careful as you can, and do please indicate that you had to when you post. We may sometimes respond with a comment suggesting you post again later when you have full access to the project. Sometimes it really is absolutely necessary. Sometimes we can work with an approximation though, if we know it to be one.