Using DLOOKUP with dates

[Phantom_guitarist]

I have set up a database which has a list of miles done each month and
another table which has a list of all petrol reciepts. What I am looking at
doing is transfering the petrol reciepts across from one table to the
mileage table. Sounds simple. The problem occurs when for example petrol
is purchased over the weekend but no mileage was done for that day (as only
business miles are recorded). What I wont to happen is for it to be added
to the next working day or the next day I do business miles. Currently I am
trying to use DLOOKUP to bring the amounts across in a query but for some
reason it doesn't seem to work how I expect it to.

Table 1 tblDriver log

date destination miles petrol
21/2 Glasgow 56
22/2 Inverness 32
09/3 Ayr 87


etc


Table 2

date petrol reciept
21/2 £21.56
22/2 £30.23
08/3 £15.44

example of the expression in query

Expr1: DLookUp("[Date]","tblDriver log","[Date] = #"&[Date2]&"#")

where [date2] is the date from table2

I no this will just find the matching date but for some reason it doesn't
work. When I run the query it only matches some of the records. Any ideas?
Any one used Dlookup before with dates? I thought it should work. Or any
other suggestions?

 

[Ken Snell]

The "WHERE" criterion expressions involving Dates expect the value to be in
the US format: mm/dd/yyyy. Your data likely is being presented in the
dd/mm/yyyy format, right?

Change the DLookup to this and see if this works:

Expr1: DLookUp("[Date]","tblDriver log","[Date] = #" &
Format([Date2],"mm/dd/yyyy") & "#")

 

[Van T. Dinh]

From your post and your e-mail address, I am fairly sure your normal
DateTime format is dd/mm/yyyy. When you do comparisons, Access/JET expect
literal dates to be in the US format mm/dd/yyyy and enclosed in hashes (#).
Thus, you need to use the above format in the 3rd argument of the DLookUp as
per Ken's post.

Few more points you should note:

1. "Date" is a rather bad name to use for Field name since "Date" is a
reserved word for the in-built function Date() (note that in VBA, you don't
need the parentheses after Date since no argument is required). As you are
probably aware, you will need to use the square brackets all the times if
you want to use the Field name "Date".

Suggest you change the Field name to something else, e.g. "TripDate".

2. Don't include the Field "petrol" in Table tblDriverLog. Firstly, each
Record should only contain details / properties on a *single* entity. From
you description, "petrol" is certainly not a property of a Log Record.
Secondly, you already store Petrol details in the Table 2 (petrol). Thus,
the Field "petrol" in Table tblDriverLog seems to store duplicated data
which violates the Relational Database Design Principles.

 

[Phantom_guitarist]

Excellant. Job sorted. Thanks for your help.