FindFirst works well only for the 1st half of a year

[Anatoly]

I have a splitted DB and it worked fine until I discovered this bug with FindFirst function

In a table tbdShifts, which is the base for the frmShiftsEnter form through the query qryShiftsEnter. On the form I have a "find" button for quick navigation to the desired date. The code behind the button is as follows:

Private Sub Command20_Click()

Dim rs As Object
Dim Produced As Date

Produced = InputBox("Enter the production date", "Find the production date", 0)
Set rs = Me.Recordset.Clone

rs.FindFirst "[Prod_Date] = " & "#" & Format(Produced, "dd\/mm\/yyyy") & "#"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

Everything worked fine and a user could go to the desired date until June 2011. For some reason this function doesn't want to find the records from 01.06.2011 till the end of the year, although it finds records well for the first half of the year. I inputted some few dummy entries to find out that any year has the same problem: either it 2010 or 2011 or 2012 - it finds records only until 31 of May but starting from 01 June you get nothing.

Please, help
Any ideas are greatly appreciated.

 

[MStef]

Try with Format "mm-dd-yyyy"

 

[Anatoly]

No, it didn't help. Still the same result.

 

[gemma-the-husky]

maybe it is to do with regional date formats. so instead of looking for 1/6/12 (1st June) it looks for 6/1/12 (6th Jan)

try this

rs.FindFirst "[Prod_Date] = " & "#" & Format(Produced, "long date") & "#"


this works fine in UK.



alternatively, maybe this bit of your code is OK, but your data is being stored with the wrong dates. (ie stored in a format for a different locale). Try displaying the existing data in long date format, and see what it really contains.

 

[spikepl]

Open your table, set the format of the Prod_Date-field to General Date, and check if the time component of date is 0 also after 1st June

 

[Anatoly]

I should say that the field property is "short date" and I also use the caption mask "00.00.0000;0;_".

I tried to change the field type to general date and it didn't work, unfortunately.

I should say that I entered a record for 07.07.2011 date and the function could find this record but 06.07.2011 is the same sad story. Why?

 

[Anatoly]

Sorry, Gemma-the-Husky, that code didn't help too.

 

[spikepl]

I asked you check what is in the field, and did not suggest that changing the fomat woul be a fix.
Please answer the question.

1. Open your table
2. Set the format of the date field as General Date
3. Inspect the time-component of the values AFTER 1st June
4. Tell us of it still is 0 or not.

 

[Anatoly]

I'm relatively new to Access and not that's why I don't understand your Step 3. Could you, please, explain.
Thank you in advance!

I asked you check what is in the field, and did not suggest that changing the fomat woul be a fix.
Please answer the question.

1. Open your table
2. Set the format of the date field as General Date
3. Inspect the time-component of the values AFTER 1st June
4. Tell us of it still is 0 or not.