using Dlookup with dates and > sign

[reginaldmerritt]

I'm using Dlookup to search a table for the first record it comes across that has a date >= the date i specifiy.

e.g.

formateddate = format(Me.SelectedDate,"mmddyy")
 
VarX = DLookup("[PKDateField]", "tbChargeRate", "[PKDateField] >=  #" & formateddate & "#")

The problem i have is that there maybe several records in tbChargeRate that have PKDateField > Me.SelectedDate. But i only want the first one it comes across, which is what i thought DLookup did.It does not act as i think it should.


I have four test records in tbChargeRate with PKDateField

Record1 : 01/08/06
Record2 : 01/09/06
Record3 : 01/04/07
Record4 : 07/06/08


These are some test results
If i use Me.SelectedDate as 01/05/07 DLookup returns Record1(01/06/07) correct
If i use Me.SelectedDate as 01/01/05 DLookup returns Record1(01/04/07) wrong
if i use Me.SelectedDate as 01/05/07 DLookup returns Record1(07/06/08) correct

As you can see when using 01/01/05 should return Record1, but instead it returns Record3.

I'm very confused on whats going on here, any suggestions and help welcome.

[ADezii]

I'm using Dlookup to search a table for the first record it comes across that has a date >= the date i specifiy.

e.g.

formateddate = format(Me.SelectedDate,"mmddyy")
 
VarX = DLookup("[PKDateField]", "tbChargeRate", "[PKDateField] >=  #" & formateddate & "#")

The problem i have is that there maybe several records in tbChargeRate that have PKDateField > Me.SelectedDate. But i only want the first one it comes across, which is what i thought DLookup did.It does not act as i think it should.


I have four test records in tbChargeRate with PKDateField

Record1 : 01/08/06
Record2 : 01/09/06
Record3 : 01/04/07
Record4 : 07/06/08


These are some test results
If i use Me.SelectedDate as 01/05/07 DLookup returns Record1(01/06/07) correct
If i use Me.SelectedDate as 01/01/05 DLookup returns Record1(01/04/07) wrong
if i use Me.SelectedDate as 01/05/07 DLookup returns Record1(07/06/08) correct

As you can see when using 01/01/05 should return Record1, but instead it returns Record3.

I'm very confused on whats going on here, any suggestions and help welcome.

The Format() Function returns a Variant of Type String which means you may be comparing a String Value with leading and trailing # signs. Explicitly Declare formateddate as a Date Variable, then it should be able to be coerced to a Date for comparisons:

Dim formateddate As Date
formateddate = format(Me.SelectedDate,"mmddyy")
 
VarX = DLookup("[PKDateField]", "tbChargeRate", "[PKDateField] >=  #" & formateddate & "#")

[reginaldmerritt]

The Format() Function returns a Variant of Type String which means you may be comparing a String Value with leading and trailing # signs. Explicitly Declare formateddate as a Date Variable, then it should be able to be coerced to a Date for comparisons:

Dim formateddate As Date
formateddate = format(Me.SelectedDate,"mmddyy")
 
VarX = DLookup("[PKDateField]", "tbChargeRate", "[PKDateField] >=  #" & formateddate & "#")

If i declare formateddate as a Date type, it gives a run time error - "type mismatch"

[ADezii]

If i declare formateddate as a Date type, it gives a run time error - "type mismatch"

1. Are you sure [PKDateField] is defined as a Date Data Type in the Table?
2. If it is, and you're still getting the Error, try the conversion within DLookup() as in:
   

   VarX = DLookup("[PKDateField]", "tbChargeRate", "[PKDateField] >=  #" & CDate(formateddate) & "#")

3. This is strange since I had no trouble obtaining the proper results.
4. Let me know how you make out.