P
PeterM
An AC2003 table contains journal entries. Each entry contains a date
(DJ_Date), a time they woke up (DJ_Wake_Time) and a Time they went to sleep
(DJ_Sleep_Time). The DJ_Date is formatted as a general date. The two times
are formatted as short time in the table definitions. The table name is
DailyJournal.
In order to compute the total time slept I need to compute the difference
between the time they went to sleep the night before and the time they woke
up today. I'm using the Diff2Dates routine (I use it everywhere, it's
great). The problem I'm having is with the DLookup. It's saying that I have
an illegal format in the criteria expression of the DLookUp and I can't
figure it out. Below is the code.
Dim PreviousDate As Date
Dim PreviousNightSleepTime As Date
PreviousDate = DateAdd("d", -1, Me.DJ_Date) 'decrease by 1 day
PreviousNightSleepTime = DLookup("[DJ_Sleep_Time]", "[DailyJournal]",
"DJ_Date = '" & [PreviousDate] & "'")
Me.DJ_Hours_Slept = Diff2Dates("ymdhn", Me.DJ_Date & " " & Me.DJ_Wake_Time,
PreviousDate & " " & PreviousNightSleepTime, False)
When I run it in debug mode, the PreviousDate is being calculated correctly.
I've tried to put format statements for each date and time and I still get
the error in the DLookup. I tested the Diff2Dates function using literals
and it works fine so it has to be the PreviousNightSleepTime that's wrong.
Can anyone see what I'm doing wrong?
Thanks in advance.
(DJ_Date), a time they woke up (DJ_Wake_Time) and a Time they went to sleep
(DJ_Sleep_Time). The DJ_Date is formatted as a general date. The two times
are formatted as short time in the table definitions. The table name is
DailyJournal.
In order to compute the total time slept I need to compute the difference
between the time they went to sleep the night before and the time they woke
up today. I'm using the Diff2Dates routine (I use it everywhere, it's
great). The problem I'm having is with the DLookup. It's saying that I have
an illegal format in the criteria expression of the DLookUp and I can't
figure it out. Below is the code.
Dim PreviousDate As Date
Dim PreviousNightSleepTime As Date
PreviousDate = DateAdd("d", -1, Me.DJ_Date) 'decrease by 1 day
PreviousNightSleepTime = DLookup("[DJ_Sleep_Time]", "[DailyJournal]",
"DJ_Date = '" & [PreviousDate] & "'")
Me.DJ_Hours_Slept = Diff2Dates("ymdhn", Me.DJ_Date & " " & Me.DJ_Wake_Time,
PreviousDate & " " & PreviousNightSleepTime, False)
When I run it in debug mode, the PreviousDate is being calculated correctly.
I've tried to put format statements for each date and time and I still get
the error in the DLookup. I tested the Diff2Dates function using literals
and it works fine so it has to be the PreviousNightSleepTime that's wrong.
Can anyone see what I'm doing wrong?
Thanks in advance.