RST Findfirst date problem

[ColPat]

Hi,
I am trying to count number of days between dates and check a table of holidays at the same time. Trouble is the Findfirst is not finding dates in the table. Dates in the table and on the form are entered using calendar control 11.0. All date fields are set to short date with input mask 99/00/000;0;_

Can anyone see what I am doing wrong???

Thanks
Colin


Private Sub Command44_Click()
On Error GoTo Err_Command44_Click

Dim intCount, intPublic As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tbHolidays", dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0
intPublic = 0
Do While strStartDate <= strEndDate
rst.FindFirst "[HolidayDate] = #" & strStartDate & "#"
If rst.NoMatch Then
intCount = intCount + 1
Else
intPublic = intPublic + 1
End If
strStartDate = strStartDate + 1
Loop

Exit_Command44_Click:


Text42 = intCount
txPublic = intPublic
rst.Close
Set rst = Nothing
Set DB = Nothing
Exit Sub

Err_Command44_Click:
MsgBox Err.Description
Resume Exit_Command44_Click

End Sub

[RuralGuy]

I see this thread is marked as Solved. Is it, and will you post your solution so others can benefit?

[ColPat]

Had to set date format by putting "Const strJetDate = "\#mm\/dd\/yyyy\#" in the header section of the code and then altered the code as follows. Now works like a charm.

Private Sub coDaysCalc_Click()
   On Error GoTo Err_coDaysCalc_Click
   Dim intCount, intPublic As Integer
   Dim tbHolidays As DAO.Database
   Dim rst As DAO.Recordset
   Dim strLookDate As String
   Set tbHolidays = CurrentDb
   Set rst = tbHolidays.OpenRecordset("SELECT [HolidayDate] FROM tbHolidays", dbOpenDynaset)
   'strStartDate = strStartDate + 1
   'To count StartDate as the 1st day comment out the line above
   intCount = 0
   intPublic = 0
   strLookDate = Format(strStartDate, strJetDate)
   Do While strStartDate <= strEndDate
      rst.FindFirst "[HolidayDate] = " & strLookDate & ""
      If rst.NoMatch Then
         intCount = intCount + 1
      Else
         intPublic = intPublic + 1
      End If
      strStartDate = strStartDate + 1
      strLookDate = Format(strStartDate, strJetDate)
   Loop
Exit_coDaysCalc_Click:
   txDaysApplied = intCount - txRDO
   txPublic = intPublic
   rst.Close
   Set rst = Nothing
   Set tbHolidays = Nothing
   Exit Sub
Err_coDaysCalc_Click:
   MsgBox Err.Description
   Resume Exit_coDaysCalc_Click
End Sub

Sorry can't seem to get the code to indent properly here to make it easier to read.

[RuralGuy]

Thank you very much for your solution.