DLookup 2

[santastefano]

I have a routine that creates a timetable based on user input of the first date - it just adds 7 days.
As an enhancement I wanted to make it check for holidays when the business would be closed. I thought a good method would be a table of holiday dates and use DLokkup but I keep getting the "You cancelled the previous event" error message at the DLookup line.
I am using:

Dim DB As DAO.Database
    Dim rsTimeTable As DAO.Recordset
    Set DB = CurrentDb
    Set rsTimeTable = DB.OpenRecordset("tblTimetable", dbopendynaset)
    Dim varSessionsTotal As Integer
    Dim varSessionsPerWeek As Integer
    Dim varStartDate As Date
    Dim varCode As String
    Dim varTime1, varTime2 As Date
    varSessionsPerWeek = Forms!frmCourseStart!SessionsWeek
    varTime1 = Me.Time1
    varTime2 = Me.Time2
If varTime1 > varTime2 Then
        DoCmd.CancelEvent
        Reply = MsgBox("The finish time you entered for this session is earlier than the start time", 16, "School Run")
        DoCmd.GoToControl "Time1"
ElseIf varSessionsPerWeek = 1 Then
    varSessionsTotal = Forms!frmCourseStart!TotalSessions
    varCode = Forms!frmCourseStart!Code
    varStartDate = Me.Date1
        If varStartDate = DLookup("[Booking]", "tblHolidays", "[Booking] = varStartDate") Then
        varStartDate = Me.Date1 + 7
        Else
        End If

I have tried commenting out the CancelEvent in the time check
All references to dates and times are actual dates and times.
Any suggestions enthusiastically received.
Best regards
George

 

[danvlas]

If varStartDate = DLookup("[Booking]", "tblHolidays", "[Booking] = " & varStartDate)

or...

If varStartDate = DLookup("[Booking]", "tblHolidays", "[Booking] = DateValue(varStartDate))

?



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[swtrader]

Given: (1) Main form = frmMain (2) Sub form = frmSub (3) unbound text box on frmSub = DocNo

DocNo is concatenation of other user-input fields on frmSub. It's possible that there could be 200 frmSub rows

Needed: method of checking to verify that no two (or more) DocNo's are duplicated

 

[SJFoust]

Try changing your DLookup to read:

DLookup("[Booking]", "tblHolidays", "[Booking] = #" & Format$(varStartDate, "mm/dd/yyyy") & "#")

The error you are receiving simply informs you that there is *something* wrong within the DLookup call -- either the field you reference doesn't exist, the table doesn't exist, or there is an error in the conditions (WHERE) clause. By adding the pound signs, you are clearly identifying the date as being a date, which will remove that as a possible point of failure. By using the Format$, you are ensuring the date being passed is in a recognizable format. (Remember, the condition is being effectively passed to a WHERE clause, which means it has to be properly formatted as text.)