Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-24-2019, 08:14 AM   #1
Reverso4182
Newly Registered User
 
Join Date: Sep 2019
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Reverso4182 is on a distinguished road
DLookUp and Control Source for Date

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.

Reverso4182 is offline   Reply With Quote
Old 09-24-2019, 08:18 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,896
Thanks: 57
Thanked 1,288 Times in 1,269 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: DLookUp and Control Source for Date

Hi. The DLookup() function might work, but try using this criteria instead:
Code:
...,"[Date]=DateAdd('yyyy',-1,Date())")
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Reverso4182 (09-24-2019)
Old 09-24-2019, 08:26 AM   #3
Reverso4182
Newly Registered User
 
Join Date: Sep 2019
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Reverso4182 is on a distinguished road
Re: DLookUp and Control Source for Date

That worked! Thank you.

Reverso4182 is offline   Reply With Quote
Old 09-24-2019, 09:37 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,896
Thanks: 57
Thanked 1,288 Times in 1,269 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: DLookUp and Control Source for Date

Quote:
Originally Posted by Reverso4182 View Post
That worked! Thank you.
Hi. Youíre welcome. Glad to hear you got it to work. Good luck with your project.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Dlookup in control source thescottsman General 1 09-26-2014 02:01 AM
DLookUp in Text Box Control Source tmw4889 Forms 3 02-25-2014 07:18 AM
Dlookup in Form's Control Source !!! Kasher Forms 3 02-24-2013 12:16 AM
dlookup as control source hcpjk Forms 3 07-23-2007 09:25 AM
DLookup in Control Source yuccakid Forms 3 05-25-2000 04:08 PM




All times are GMT -8. The time now is 11:44 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World