DLookup Date

[gmazza via AccessMonster.com]

Hey there,
I am trying to do a DLookup on a Date field.
Do I declare the result as a date?
Dim Result as Date

This keeps returning 12:00:00 AM
answer = DLookup("VisitDate", "PatientData", "VisitDate = '" & Me.VisitDate &
"'")

 

[Klatuu]

You are passing VisitDate as a string. It needs to be passed as a Date:
answer = DLookup("VisitDate", "PatientData", "VisitDate = #" & Me.VisitDate &

 

[gmazza via AccessMonster.com]

I am still getting a result of 12:00:00 AM with the following:

answer = DLookup("VisitDate", "PatientData", "VisitDate = #" & Me.VisitDate &
"#")

You are passing VisitDate as a string. It needs to be passed as a Date:
answer = DLookup("VisitDate", "PatientData", "VisitDate = #" & Me.VisitDate &

"#")

Hey there,
I am trying to do a DLookup on a Date field.

[quoted text clipped - 4 lines]

answer = DLookup("VisitDate", "PatientData", "VisitDate = '" & Me.VisitDate &
"'")

 

[Klatuu]

What is the value of Me.VisitDate?
Do your field values have time in them?

--
Dave Hargis, Microsoft Access MVP

I am still getting a result of 12:00:00 AM with the following:

answer = DLookup("VisitDate", "PatientData", "VisitDate = #" & Me.VisitDate &
"#")

You are passing VisitDate as a string. It needs to be passed as a Date:
answer = DLookup("VisitDate", "PatientData", "VisitDate = #" & Me.VisitDate &

"#")

Hey there,
I am trying to do a DLookup on a Date field.

[quoted text clipped - 4 lines]

answer = DLookup("VisitDate", "PatientData", "VisitDate = '" & Me.VisitDate &
"'")

 

[Douglas J. Steele]

Also, what's your Short Date format? If it's not mm/dd/yyyy, you need to use

answer = DLookup("VisitDate", "PatientData", "VisitDate = " &
Format(Me.VisitDate, "\#yyyy\-mm\-dd\#")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

What is the value of Me.VisitDate?
Do your field values have time in them?

--
Dave Hargis, Microsoft Access MVP

I am still getting a result of 12:00:00 AM with the following:

answer = DLookup("VisitDate", "PatientData", "VisitDate = #" &
Me.VisitDate &
"#")

You are passing VisitDate as a string. It needs to be passed as a Date:
answer = DLookup("VisitDate", "PatientData", "VisitDate = #" &
Me.VisitDate &
"#")

Hey there,
I am trying to do a DLookup on a Date field.
[quoted text clipped - 4 lines]
answer = DLookup("VisitDate", "PatientData", "VisitDate = '" &
Me.VisitDate &
"'")

 

[gmazza via AccessMonster.com]

The value of Me.VisitDate is 10/07/2009.
I entered it on the form as 10/Jul/09.
I don't believe my fields have time.

What is the value of Me.VisitDate?
Do your field values have time in them?

I am still getting a result of 12:00:00 AM with the following:

[quoted text clipped - 10 lines]

 

[gmazza via AccessMonster.com]

I don't know what my date format is at all.
I tied that statement and received a syntax error.

Also, what's your Short Date format? If it's not mm/dd/yyyy, you need to use

answer = DLookup("VisitDate", "PatientData", "VisitDate = " &
Format(Me.VisitDate, "\#yyyy\-mm\-dd\#")

What is the value of Me.VisitDate?
Do your field values have time in them?

[quoted text clipped - 16 lines]

 

[Douglas J. Steele]

If you're saying that 10/07/2009 represents 0/Jul/09, then your Short Date
format likely is dd/mm/yyyy.

There was a typo in my previous response: I was missing a closing
parenthesis:

answer = DLookup("VisitDate", "PatientData", "VisitDate = " &
Format(Me.VisitDate, "\#yyyy\-mm\-dd\#"))

Sorry about that...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I don't know what my date format is at all.
I tied that statement and received a syntax error.

Also, what's your Short Date format? If it's not mm/dd/yyyy, you need to
use

answer = DLookup("VisitDate", "PatientData", "VisitDate = " &
Format(Me.VisitDate, "\#yyyy\-mm\-dd\#")

What is the value of Me.VisitDate?
Do your field values have time in them?

[quoted text clipped - 16 lines]

Me.VisitDate &
"'")

 

[gmazza via AccessMonster.com]

Sorry, shoulda caught that parenthesis. No syntax error, but still getting
the same result:
12:00:00 AM
The error is saying Invalid use of a Null.

If you're saying that 10/07/2009 represents 0/Jul/09, then your Short Date
format likely is dd/mm/yyyy.

There was a typo in my previous response: I was missing a closing
parenthesis:

answer = DLookup("VisitDate", "PatientData", "VisitDate = " &
Format(Me.VisitDate, "\#yyyy\-mm\-dd\#"))

Sorry about that...

I don't know what my date format is at all.
I tied that statement and received a syntax error.

[quoted text clipped - 10 lines]

 

[Klatuu]

Doug has you on the right track.
--
Dave Hargis, Microsoft Access MVP

The value of Me.VisitDate is 10/07/2009.
I entered it on the form as 10/Jul/09.
I don't believe my fields have time.

What is the value of Me.VisitDate?
Do your field values have time in them?

I am still getting a result of 12:00:00 AM with the following:

[quoted text clipped - 10 lines]

answer = DLookup("VisitDate", "PatientData", "VisitDate = '" & Me.VisitDate &
"'")

 

[Douglas J. Steele]

Does your VisitDate field in your table include time, or is it strictly a
date value? Does the control VisitDate on your form have a value in it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry, shoulda caught that parenthesis. No syntax error, but still getting
the same result:
12:00:00 AM
The error is saying Invalid use of a Null.

If you're saying that 10/07/2009 represents 0/Jul/09, then your Short Date
format likely is dd/mm/yyyy.

There was a typo in my previous response: I was missing a closing
parenthesis:

answer = DLookup("VisitDate", "PatientData", "VisitDate = " &
Format(Me.VisitDate, "\#yyyy\-mm\-dd\#"))

Sorry about that...

I don't know what my date format is at all.
I tied that statement and received a syntax error.

[quoted text clipped - 10 lines]

Me.VisitDate &
"'")

 

[Douglas J. Steele]

Doug has you on the right track.

I'll make you a citizen of the world yet, Dave! <g>

 

[Klatuu]

lol
okay, go for it.

 

[gmazza via AccessMonster.com]

Yes but I am still getting the same error.

lol
okay, go for it.

 

[gmazza via AccessMonster.com]

Yes but I am still getting the same error.

I'll make you a citizen of the world yet, Dave! <g>

 

[Douglas J. Steele]

You haven't answered the questions I asked:

Does your VisitDate field in your table include time, or is it strictly a
date value? Does the control VisitDate on your form have a value in it?

 

[gmazza via AccessMonster.com]

Sorry, I answered the time question that Klatuu asked a little above.
No it does not include time. Its just a date/time field with no format.
Yes, the control VisitDate has a value of 10/Jul/09.

 

[Douglas J. Steele]

See whether this makes any difference:

answer = DLookup("VisitDate", "PatientData", "VisitDate = " &
Format(CDate(Me.VisitDate), "\#yyyy\-mm\-dd\#"))

 

[gmazza via AccessMonster.com]

Same thing. Its really weird, I must be doing something wrong??

See whether this makes any difference:

answer = DLookup("VisitDate", "PatientData", "VisitDate = " &
Format(CDate(Me.VisitDate), "\#yyyy\-mm\-dd\#"))

Sorry, I answered the time question that Klatuu asked a little above.
No it does not include time. Its just a date/time field with no format.

[quoted text clipped - 10 lines]

 

[Douglas J. Steele]

Hold on. VisitDate is a field in the form's underlying recordset, correct?
If it's also the name of the text box to which that field is bound, you may
need to rename the text box (say, to txtVisitDate), and use Me.txtVisitDate
in your lookup.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Same thing. Its really weird, I must be doing something wrong??

See whether this makes any difference:

answer = DLookup("VisitDate", "PatientData", "VisitDate = " &
Format(CDate(Me.VisitDate), "\#yyyy\-mm\-dd\#"))

Sorry, I answered the time question that Klatuu asked a little above.
No it does not include time. Its just a date/time field with no format.

[quoted text clipped - 10 lines]

I'll make you a citizen of the world yet, Dave! <g>