467,211 Members | 1,211 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,211 developers. It's quick & easy.

using Dlookup with dates and > sign

reginaldmerritt
100+
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.

Expand|Select|Wrap|Line Numbers
  1. formateddate = format(Me.SelectedDate,"mmddyy")
  2.  
  3. 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.
Nov 25 '07 #1
  • viewed: 2134
Share:
3 Replies
ADezii
Expert 8TB
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.

Expand|Select|Wrap|Line Numbers
  1. formateddate = format(Me.SelectedDate,"mmddyy")
  2.  
  3. 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:

Expand|Select|Wrap|Line Numbers
  1. Dim formateddate As Date
  2. formateddate = format(Me.SelectedDate,"mmddyy")
  3.  
  4. VarX = DLookup("[PKDateField]", "tbChargeRate", "[PKDateField] >=  #" & formateddate & "#")
Nov 26 '07 #2
reginaldmerritt
100+
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:

Expand|Select|Wrap|Line Numbers
  1. Dim formateddate As Date
  2. formateddate = format(Me.SelectedDate,"mmddyy")
  3.  
  4. VarX = DLookup("[PKDateField]", "tbChargeRate", "[PKDateField] >=  #" & formateddate & "#")
If i declare formateddate as a Date type, it gives a run time error - "type mismatch"
Nov 28 '07 #3
ADezii
Expert 8TB
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:
    Expand|Select|Wrap|Line Numbers
    1. 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.
Nov 28 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Sue | last post: by
4 posts views Thread by thx606@cablespeed.com | last post: by
1 post views Thread by Colin Mardell | last post: by
8 posts views Thread by Christine Henderson | last post: by
6 posts views Thread by bjaj@vestas.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.