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

 

[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?

 

[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