Variable Referencing Problem

[Guest]

I always seem to have this problem, and can never figure out what I am doing
wrong.

I have a Date variable - gblWeekBegin
and I am using it in a DLookup statement, like so:

gblPrevHours = DLookup("Hrs7", "tblTimeEntries", "WeekBegin = _
'" & gblWeekBegin & "'")

WeekBegin, in tblTimeEntries, is a ShortDate. I keep getting a Data Type
Mismatch.

I'm certain I am calling the variable incorrectly, but I can't seem to get
the syntax correct.

Any assistance would be greatly appreciated.

TIA.

Sharkbyte

 

[Mike Labosh]

gblPrevHours = DLookup("Hrs7", "tblTimeEntries", "WeekBegin = _

'" & gblWeekBegin & "'")

WeekBegin, in tblTimeEntries, is a ShortDate. I keep getting a Data Type
Mismatch.

Welcome to the wonderful world of value delimiters:

To assign a number, it looks like this:

Dim i As Integer
i = 5

Simple enough. To assign a string, you have to put quotes around it:

Dim s As String
s = "abc"

Similarly, with dates, you have to put pund signs around it:

Dim d As Date
d = #19-Feb-2006#

So your statement above might more correctly look like this:

gblPrevHours = DLookup("Hrs7", "tblTimeEntries", "WeekBegin = #" &
gblWeekBegin & "#")

Note that if you are passing this through to a SQL Server, you must use a
single quote (') instead of a pound sign (#).
--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei

 

[Douglas J. Steele]

In the case where the user's Short Date format has been set to other than
mm/dd/yyyy, you'd be better off using

gblPrevHours = DLookup("Hrs7", "tblTimeEntries", "WeekBegin = " &
Format(gblWeekBegin, "\#mm\/dd\/yyyy\#"))