Question MS Query DLookup not returning desired results when looking up dates

[Nico]

Hi

I am using Dlookup in a Query and it's not showing the desired results when looking up dates i.e. some fields are showing the correct month, some fields are blank, and other fields are showing the incorrect month.

I have created a union query called Qry_Commission_Export that includes a lookup column titled Agent Month: DLookUp("RDMonth","Tbl_ReportingDates","RDDate = #" & [Agent Date] & "#").

RDMonth is a column in the ReportingDates table and the data type is short text. It contains the months of the year i.e. January, February etc.

RDDate is a column in the ReportingDates table and the data type is date/time. It includes dates that fall into months of the year.

Agent Date is a column in the Qry_Commission_Export Query and is a calculation based on two other date columns.

What I am trying to achieve is the following:

When I run the Qry_Commission_Export Query, I want the Agent Month Column to show the Month of the year located in the RDMonth column in the ReportingDates table, where the RDDate in the ReportingDates table is equal to the Agent Date column in the Qry_Commission_Export Query.

I hope this makes sense.

Your help would be much appreciated.

Kind Regards
Nick

 

[CJ_London]

The main issue you have is when dates are presented as text e.g. 30/08/2016 to be compared with a date datatype you have to first convert it to a date datatype - which is what the # do.

The problem is that the date text has to be unambiguous or presented in US format of mm/dd/yyyy. A date presented as 10/8/2016 is ambiguous because although you see it as 10th Aug, Access sees it as 8th Oct. A date of 10 Aug 2016 is unambiguous as is a date of 15/08/2016 (because there are not 15 months in the year).

So to change a date to the correct format, you need to use the format function.

"RDDate = #" & format([Agent Date],"mm/dd/yyyy") & "#").

However not sure why you need to lookup the month - you could just use

Agent Month:format([Agent Date],"mmmm") to return 'August'

Alternatively use a format string of
'mmm' for Aug
'yyyymm' for 201608

etc

 

[Nico]

Hi CJ_London

That worked. Thank you, you are a star.

Regards
Nico