Simple Dlookup? it appears not

[Gastank]

I am trying to populate a form control Field1 ["DailyHireRate"], by looking up a value from a table ["Rates"], based on the date specified in another control Field2 ["ChargeDate"] on the same form.

Form = "Transactions"
Field1 = "DailyHireRate" - Format: General Number
Control Source: =DLookUp"[DailyHireRate]","[Rates]","[BDate]<=#" & [Forms]![Transactions]![ChargeDate] & "# AND [EDate] >#" & [Forms]![Transactions]![ChargeDate] & "#")

Field2 = "ChargeDate" - Format: ShortDate (=UK dd/mm/yyyy)

Table = "Rates" - 3 field thus:

DailyHireRate BDate EDate
10 01/11/1998 31/12/1999
20 01/01/2000 31/12/2000
30 01/01/2001 10/02/2002
40 11/02/2002 10/02/2003


The lookup finds the correct rate ruling as per the date shown in "ChargeDate" on the Transactions form with the exception of the following dates:
04/02/2002, 05/02/2002, 06/02/2002, 07/02/2002, 08/02/2002, 01/05/2002 and 02/05/2002.

Is Access applying the US date format somehow and if so how do I correct it?

Any help would be greatly appreciated

 

[Pat Hartman]

Yes, Access uses US Date format in SQL. Try converting the form fields to date/time data types rather than variants:

CDate([Forms]![Transactions]![ChargeDate])

 

[Randomblink]

WAIT A SECOND!

DLookup can pull more than one answer back?!?!?

If your expression is >= then you could get more than one reply!

I didn't know this...!
Grrrr

 

[Gastank]

Pat,

thanks for the suggestion. If I understand correctly, I amend my DLookup expression to read CDate([Forms]![Transactions]![ChargeDate]) rather than [Forms]![Transactions]![ChargeDate] etc.,

If so I get the same "errors" on the dates previously mentioned. Am I still missing something somewhere?

 

[Pat Hartman]

Since I use US date format, I don't have this problem so I'm not sure of the most efficient way to solve it. It sounds like my suggestion is not having the desired effect. To fix the problem, move the form control references out of the DLookup(). Store the form dates as variables defined as Date/time and reference the variables rather than the form fields. Put a stop in the code on the DLookup() statement so that you can examine the value of the variables and make sure that they are correct.

Randomblink,
DLookup() CANNOT return multiple values. Help describes what happens when the selection criteria does not result in a single row being returned. Even though the DLookup() in this thread uses >= and <= operators, it should return a single row if the rate table is correctly loaded.

 

[Tim K.]

Will DateSerial() help?

Dim dtMyDate As Date
dtMyDate = [Forms]![Transactions]![ChargeDate]
dtMyDate = DateSerial(Year(dtMyDate),month(dtMyDate),Day(dtMyDate))

Then use the date variable in the DLookup()

=DLookUp"[DailyHireRate]","[Rates]","[BDate]<=#" & dtMyDate & "# AND [EDate] >#" & dtMyDate & "#")

 

[Gastank]

Pat, Tim,

I am a very low-level user of Access VBA so I will need time to study your latest suggestions. Hopefully I will be able to interpret them correctly in a reasonable amount of time.

Many Thanks.

 

[Gastank]

Simple Dlookup? I Thinks so

Pat, Tim,

I think I've found a solution to my DLookup/Date format problem.
By inserting the Date Format Function into my Dlookup expression I appear to have corrected the "rogue dates".

=Nz(DLookUp("[DailyHireRate]","[Rates]","[EDate]>#" & Format([Forms]![Transactions]![txtChargeDate],"mm/dd/yyyy") & "# AND [BDate] <=#" & Format([Forms]![Transactions]![txtChargeDate],"mm/dd/yyyy") & "#"))

thanks for your suggestions