DLookup with Time arguments (1 Viewer)

[RogerCH]

I have created a table (tblRefWeekSchedule) that corresponds to a Weekly Class Schedule. It contains the fields Weekday(intWeekday) Start Time (dteStartTime), End Time (dteEndTime), Venue and Class.

The table was populated from an Excel spreadsheet and I note that when exporting the table it stores the times as 00/01/1900 09:30:00 etc. etc.

I then have a form to create Class records. Upon entering a Date (tbxDate) and Start Time (tbxStartTime) I am trying to use the DLookup function from the AfterUpdate event on the control tbxStartTime to pre-populate the controls for End Time (tbxEndTime) Venue and Class.


So firstly I have determined the day of the week ...
intDay = Weekday(tbxDate)

I then follow this with the table lookup ...
tbxEndTime = DLookup("[dteEndTime]", "tblRefWeekSchedule", "[intWeekDay] = " & intDay & "'" And "'" & "[dteStartTime] = " & Me.tbxStartTime)


This returns a Type Mismatch (tbxStartTime is 09:30:00)
I have unsuccessfully tried to re-format both dteStartTime and tbxStartTime but finding the appropriate formatting/conversion function and getting my single quotes and double quotes in the correct place has totally confused me.


Your help would be much appreciated

 

[Micron]

I have created a table (tblRefWeekSchedule) that corresponds to a Weekly Class Schedule. It contains the fields Weekday(intWeekday) Start Time (dteStartTime), End Time (dteEndTime), Venue and Class.

You really have fields or controls named Weekday, Class? These are reserved names. If you don't fix that you're only asking for issues. It may even be the problem here. Would advise NO spaces in names, or as a less attractive option, use underscore, and that you adopt a naming convention
http://access.mvps.org/access/general/gen0012.htm
https://access-programmers.co.uk/forums/showthread.php?t=225837

 

[Gasman]

Single quotes should surround strings

tbxEndTime = DLookup("[dteEndTime]", "tblRefWeekSchedule", "[intWeekDay] = " & intDay & " And [dteStartTime] = " & Me.tbxStartTime)

I'm not sure how the time should be enclosed, with dates it would be a # ?

tbxEndTime = DLookup("[dteEndTime]", "tblRefWeekSchedule", "[intWeekDay] = " & intDay & " And [dteStartTime] = # " & Me.tbxStartTime & "#")

HTH

 

[isladogs]

tbxEndTime = DLookup("dteEndTime", "tblRefWeekSchedule", "[intWeekDay] = " & intDay & " And [dteStartTime] = #" & Me.tbxStartTime & "#")

BUT another problem you will have is that times will also be storing a zero date so perhaps you need:

tbxEndTime = DLookup("dteEndTime", "tblRefWeekSchedule", "[intWeekDay] = " & intDay & " And Format([dteStartTime],"hh:nn:ss") = #" & Me.tbxStartTime & "#")

 

[RogerCH]

Hi Micron
My field names are in brackets using a naming convention that indicates the Data Type in their prefix. Because my issue is with the "Time" fields I chose not to provide the field names for Venue and Class.

 

[RogerCH]

My thanks to Gasman and isladogs

Your suggestion of enclosing the value of the textbox control tbxStartTime with date hashes has done the trick.

tbxEndTime = DLookup("dteEndTime", "tblRefWeekSchedule", "[intWeekDay] = " & intDay & " And [dteStartTime] = #" & Me.tbxStartTime & "#")

I have tested it on the 20 records in my Weekly Class Schedule and it has successfully returned the values I was expecting. It has also prompted me that I need to remember to use the hashes on the start and end time controls when I generate a record.