DLookup with dates

[Ian Baker]

I am having the strangest problem with some code returning Null when using a
DLookup of dates to get a recordID number - please help

I have a table tmpCountSheet that has a Text field called CountDate (it is
text as it makes it easier importing the data from Excel)
containing data of many records all with 1/11/2005 in the CountDate field

I have another table called Quarter Period that has the following fields:
QuarterID (Number)
QuarterFrom (Date/Time)
QuarterTo (Date/Time)
containing data of:
QuarterID QuarterFrom QuarterTo

1 1/12/2005 30/12/2005

2 1/10/2005 31/12/2005

3 1/01/2006 31/03/2006



I have the following code where the QTRx = DLookup... is returning a Null
but should be returning 2 from the Quarter Period table:

Dim dt As String
Dim QTRx As Integer

dt = DLookup("Max(CountDate)", "tmpCountSheet")

QTRx = DLookup("[QuarterID]", "[Quarter Period]", "[QuarterTo]>=#" & dt & "#
AND [QuarterFrom]<=#" & dt & "#")


It all looks ok to me it is killing me why the DLookup is returning a Null
instead of 2



Thanks for any help



Regards

Ian

 

[Duane Hookom]

You should really import your data and then make a real date data type
rather than playing around with date text.

You might have your >= and <= reversed. Also keep in mind that much of
Access and queries expect the US format date of mm/dd/yyyy.