DLookUp and Control Source for Date (1 Viewer)

Reverso4182

New member
Local time
Today, 05:42
Joined
Sep 24, 2019
Messages
2
Hello,

I'm working on a database that is tracking sales. I have a Display Form which includes the total number of sales for the current FY; I would like to display the total number of sales from the same date from the previous year for comparison. The database includes a historical table which has archived data from the previous FY including the date and total number of sales.

I've been attempting to use the DLookUp function in the Control Source to report the number of sales from the previous year on the Display Form. The code I've attempted thus far is:

=DLookUp("[TOTAL_SALES]","tbl_FY_2019_SALES","[DATE]"=DateAdd("yyyy",-1,Date()))

The tbl_FY_2019_SALES has three columns:
DATE: formatted in m/d/yyyy
SALES_PER_DAY
TOTAL_SALES

Plainly, I would like to report the value from TOTAL_SALES where the DATE value is equal to the current date, minus one year.

I have no idea if the DLookUp function is the correct choice for what I'm attempting to do - any help would be greatly appreciated.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:42
Joined
Oct 29, 2018
Messages
21,358
Hi. The DLookup() function might work, but try using this criteria instead:
Code:
...,"[Date]=DateAdd('yyyy',-1,Date())")
 

Reverso4182

New member
Local time
Today, 05:42
Joined
Sep 24, 2019
Messages
2
That worked! Thank you.
 

Users who are viewing this thread

Top Bottom