Text Box Search Using FindFirst For Date

[CharissaBelle]

I have an unbound textbox (txtFilter) in the header of a form in which I want the user to be able to type in a date and use the findfirst method to find the first record with that date. The following is actually the code I used to findfirst Employee, I just changed it from Employee to StartDate and need help with the Like section.

Set rs = Me.Recordset.Clone
        rs.FindFirst "([StartDate] Like """ & Me.txtFilter & "*"")"
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark

[andy49]

Don't use like with date.

Are you looking for the specific date?
[startdate] = cdate(me.txtfilter)

Or similar (no pc here)


Sent from my iPhone using Tapatalk

[CharissaBelle]

Are you looking for the specific date?

I am looking to go to the closest date to the one typed in.


I changed my code to look like this:

Set rs = Me.Recordset.Clone        
rs.FindFirst "([StartDate] cDate(Me.txtFilter))"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Now when I type in an exact date I know is there, I get a Run Time error 3077 syntax error (missing operator) in expression.

[CharissaBelle]

Oh wait, I found what I missed.

Now my code looks like this

Set rs = Me.Recordset.Clone
rs.FindFirst "([StartDate] = cDate(Me.txtFilter))"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

And I'm getting a different error 3345 unknown or invalid field reference 'Me.txtFilter'

[Bob Fitz]

You will need to use the # date delimiters and I think you may need to use format on the date.
Take a look at: https://msdn.microsoft.com/en-us/lib...or=-2147217396

[jwhite]

@Charissa: Your first post stated "...find the first record with that date", but then in a later post you stated "...go to the closest date to the one typed in.". Please clarify for those already helping, which is it?

And going by what Bob suggested, your statement would be more like: rs.FindFirst "[StartDate] = #" & Me.txtFilter & "#"

[Bob Fitz]

From the link I posted earlier:

You should use the U.S. date format (month-day-year) when you search for fields containing dates, even if you're not using the U.S. version of the Microsoft Access database engine; otherwise, the data may not be found. Use the Visual Basic Format function to convert the date. For example:VBA



rstEmployees.FindFirst "HireDate > #" _
& Format(mydate, 'm-d-yy' ) & "#"

[CharissaBelle]

@Charissa: Your first post stated "...find the first record with that date", but then in a later post you stated "...go to the closest date to the one typed in.". Please clarify for those already helping, which is it?

I would like to go to the closest date to the one typed in.

Here's my code now. I'm getting close. The error I'm getting now is run time error 6 overflow.

Set rs = Me.Recordset.Clone
rs.FindFirst "StartDate > #" & Format(Me.txtFilter, mm / dd / yyyy) & "#"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

[jwhite]

Yes, Bob, but she also had the control name within the quotes which wouldn't work anyway, and thought it was worth pointing out.

[CharissaBelle]

Well, I've almost got this working.

Here is my code currently. StartDate is formatted as a Short Date. And it works if I type the date exactly like this mm/dd/yyyy. However, I don't want to have to input the '/' between mmddyyyy. I tried adding an input mask to the txtFilter for short date, but then I get a run time error 6 overflow. Any ideas what I can do so the user only has to enter the mmddyyyy without having to add the '/'s?

Set rs = Me.Recordset.Clone
rs.FindFirst "[StartDate] > #" & Format(Me.txtFilter, "mm\/dd\/yyyy") & "#"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

[CharissaBelle]

Solved it! Instead of using an input mask, I formatted txtFilter as a short date. Woohoo!!! Thanks everyone!!!

[andy49]

What about setting the input mask of the textbox?

[CharissaBelle]

What about setting the input mask of the textbox?

That's what I tried to do first, but I would get a runtime error 6 overflow. I don't know why, but it seems to work if I set the actual format property of txtFilter to Short Date.