Manipulating dates 2

[santastefano]

I have a loop which creates a timetable based on user input of the first date and the frequency.

Do While varSessionsTotal > 0
   With rsTimeTable
      .AddNew   'Creates new record in the table
      !Code = varCode
      !StartDate = varStartDate
      !From = varTime1
      !To = varTime2
      .Update   'Confirms the record to the table.
   End With
   varSessionsTotal = varSessionsTotal - 1
   varStsrtDate = varStartDate + 7
Loop

I need to refer varStartDate to a list of user maintained holidays before looping back to avoid bookings during holidays. I have tried

Dim strCriteria As Date
Dim rsHolidays As DAO.Recordset
    Set DB = CurrentDb
    Set rsHolidays = DB.OpenRecordset("tblHolidays", dbopendynaset)
    strCriteria = varStartDate
    With rsHolidays
    .FindFirst strCriteria
    End With

and receive an error message for the .Find instruction

I have tried opening a query based on the holiday table and user input and checking for an empty record set - empty meaning OK, some content meaning add 7 days.

I have tried adapting various date solutions posted here without success.

Can anybody please be a sheepdog and round me up and point me in the right direction?

If you got to here, thanks for reading.
George

 

[alvechurchdata]

varStsrtDate = varStartDate + 7

Is this typo in your code or have you just typed the mistake here?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[santastefano]

It'sd a typo here, sorry!
The code is OK and works without the breakout to check against a holiday table.

 

[PHV]

strCriteria = "[name of date field in tblHolidays]=#" & Format(varStartDate, "yyyy-mm-dd") & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

As PHV said.

FindFirst method, DAO or Find ADO, requires a WHERE statement, without the "WHERE".


strCountry = "Canada"
.FindFirst "txtCountry ='" & strCountry & "'"

dteChristmas = #12/25/06#
.FindFirst "txtHoliday =#" & dteChristmas & "#"

.FindFirst "txtScore = 45

 

[santastefano]

Thank you very much Zion7 and PHV.
This is a successful fix!!!