Date/time values in Dlookup (MS Access 2010) VBA

Question

Is there a difference in the type of date/time values that access uses in Dlookup commands?

Example: Table tblreg with multiple records, having clientname (string) and regstart (string) Regstart is string but always uses the format dd-mm-yyyy hh:mm). This field can also have other values (depending on field typeindicator = <>T) Query qry1 points to table tblreg and contains reg1:Cdate(regstart) and typeindicator="T"

The controls RegStart and Regstop are defined as string variables (same reason as table)

VBA Code

Dim strOverlapClientName As String
Dim date1, date2 As Date
date1 = CDate(Me.RegStart)
date2 = CDate(Me.RegStop)
strOverlapClientName = DLookup("[ClientName]", 
                               "qryRegOverlap", 
                               "[Reg1]<= #" & date1 & "# AND [Reg2]>#" & date1 & "#")

Purpose: I want to query any existing clientname, where a new date/time registration overlaps an existing time registration of another client in the db.

Checked value: 05-10-2014 12:55

Error: the DLookup function returns an error code 94 (Invalid use of Null)

Expected result should be a client having RegStart 05-10-2014 12:00:00 and Regstop 05-10-2014 13:00:00

Using a single criteria (e.g. "[Reg1]>= #" & date1 & "# ") does not throw an error, but returns a wrong record (02-01-2014 11:45:00)

What am I doing wrong here?

Art

PaulFrancis PaulFrancis · Accepted Answer · 2014-10-06T09:33:06

For starters. You need to declare all variables explicitly. Unlike other sophisticated programming languages, where you use

int numVar, someNum;

In VBA you should declare each variable by type. So

Dim date1 As Date, date2 As Date

The one you have will result in declaring date1 as Variant type and date2 as Date type.

Now, if the controls are Format as Dates the Cast CDate is not really required. In my opinion, the variables are not even required.

Next, JET engine deals date in American format, not the regular DD/MM/YYYY HH:NN:SS. So you need to format the dates accordingly.

Finally, If the condition is not met DLookup will yield a Null value. So You need to either use a Nz function or declare the variable as Variant.

Dim strOverlapClientName As String

strOverlapClientName = Nz(DLookup("[ClientName]", 
                                  "qryRegOverlap", 
                                  "[Reg1] <= " & Format(Me.RegStart, "\#mm\/dd\/yyyy hh\:nn\:ss\#") & _
                                  " AND [Reg2] > " & Format(Me.RegStop, "\#mm\/dd\/yyyy hh\:nn\:ss\#")), "No Value Found")

Hope this helps.

Date/time values in Dlookup (MS Access 2010) VBA

1 Answers