DLookUp

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.
 
S

Steve

Hello Peter,

You are treating PreviousDate as a string. Try the following:
"DJ_Date = " & [PreviousDate])

Steve
(e-mail address removed)
 
P

PeterM

Thank you George... I feel like an idiot!

George Hepworth said:
The date delimiter is # not '



PeterM said:
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.
Click to expand...
Click to expand...
 
G

George Hepworth

Ah, we've all done that countless times. Best of luck with the rest of your
project.


PeterM said:
Thank you George... I feel like an idiot!

George Hepworth said:
The date delimiter is # not '



PeterM said:
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.
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

Hello Peter,

You are treating PreviousDate as a string. Try the following:
"DJ_Date = " & [PreviousDate])
Click to expand...

Wrong again, Steve. You need # as date delimiters.
 
P

PeterM

Just in case someone wants to use this code, I discovered a problem. If a
person went to sleep before midnight, it wasn't computing the time slept
correctly. I needed to see if the time they went to sleep was greater than
6:00PM in military time (short time). If so then the date factors in, if not
then the dates are the same. See my revised code below.

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] & "#")
If Format(PreviousNightSleepTime, "short time") > "18:00" Then
Me.DJ_Hours_Slept = Diff2Dates("hn", PreviousDate & " " &
PreviousNightSleepTime, Me.DJ_Date & " " & Me.DJ_Wake_Time, False)
Else
Me.DJ_Hours_Slept = Diff2Dates("hn", PreviousNightSleepTime,
Me.DJ_Wake_Time, False)
End If

Whew! Glad I got this finally figured out (with your guys help!)


George Hepworth said:
Ah, we've all done that countless times. Best of luck with the rest of your
project.


PeterM said:
Thank you George... I feel like an idiot!

George Hepworth said:
The date delimiter is # not '



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.
Click to expand...
Click to expand...
Click to expand...
 
D

De Jager

George Hepworth said:
The date delimiter is # not '



PeterM said:
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.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with DLookup! 3
Using Max or DMax in DLookUp criteria? 1
Dlookup - double booking 4
Automatic Dlookup update 5
Help With DLookUp 1
DLookup in a grouped footer 4
DLookUp Frustration 5
DLookup Date Issues 2

Top