Calculating a Date

[MarcoPerez21]

I want to calculate a date "year a go" from "today" in my master_qry.
I have a master_qry that when you run it you have to give a "date".
I have then created a another qry that has the same information as the master_qry bt I want to gather a year ago's information..... Can I "join" these two queries to get the information at the same time?

Marco

 

[PaulBricker]

You can use the DateDiff Function to get the date from a year ago. On the Criteria Line for your date field put
=DateDiff("yyyy",-1,[DateField])

Then to join the two queries, use a union query as long as the fields are the same.

Select Query1.Name, Query1.Address, Query1.PurchaseDate From Query1
Union Select Query2.Name, Query2.Address, Query2.PurchaseDate
From Query2

That should do it.

Paul

 

[scriverb]

Copy and paste the code below into a new column in your Master queryno prompt necessary)

year a go: DateAdd("yyyy",-1,Date())

Now you can create another to link the two tables together using this new column to match to the dates from the second query.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[MarcoPerez21]

Bob,
this is my dql to the Master Qry.... when I place your code in my query it returns not 11/1/02 but instead it returns 5/30/03...
why is this so?

PARAMETERS [Enter Beginning Date] DateTime;
SELECT qry_salesinput_day1.Period, qry_salesinput_day1.Week, qry_salesinput_day1.Day, qry_salesinput_day1.[Store Am sales], qry_salesinput_day1.[Store Pm sales], qry_salesinput_day1.[Store Praline Sales], qry_salesinput_day1.[Store Merch Sales], qry_salesinput_day1.[Store Postage Sales], qry_salesinput_day1.[MailOrder Praline Sales], qry_salesinput_day1.[MailOrder Merch Sales], qry_salesinput_day1.[MailOrder Postage Sales], qry_salesinput_day1.[MailOrder Total Sales], qry_salesinput_day1.[Wholesale Praline Sales], qry_salesinput_day1.[Wholesale Merch Sales], qry_salesinput_day1.[Wholesale Postage Sales], qry_salesinput_day1.[WholeSale Total sales], qry_salesinput_day1.[Planned Store hours], qry_salesinput_day1.[Actual Store hours], qry_salesinput_day1.[Planned Store Dollars], qry_salesinput_day1.[Actual Store Dollars], qry_salesinput_day1.[Overtime Dollars], qry_salesinput_day1.[Training Dollars], qry_salesinput_day1.[Am # of Tickets], qry_salesinput_day1.[Pm # of Tickets], qry_salesinput_day1.[Peak Ticket Hour], qry_salesinput_day2.Day, qry_salesinput_day2.[Store Am sales], qry_salesinput_day2.[Store Pm sales], qry_salesinput_day2.[Store Praline Sales], qry_salesinput_day2.[Store Merch Sales], qry_salesinput_day2.[Store Postage Sales], qry_salesinput_day2.[MailOrder Praline Sales], qry_salesinput_day2.[MailOrder Merch Sales], qry_salesinput_day2.[MailOrder Postage Sales], qry_salesinput_day2.[MailOrder Total Sales], qry_salesinput_day2.[Wholesale Praline Sales], qry_salesinput_day2.[Wholesale Merch Sales], qry_salesinput_day2.[Wholesale Postage Sales], qry_salesinput_day2.[Wholesale Total Sales], qry_salesinput_day2.[Planned Store hours], qry_salesinput_day2.[Actual Store hours], qry_salesinput_day2.[Planned Store Dollars], qry_salesinput_day2.[Actual Store Dollars], qry_salesinput_day2.[Overtime Dollars], qry_salesinput_day2.[Training Dollars], qry_salesinput_day2.[Am # of Tickets], qry_salesinput_day2.[Pm # of Tickets], qry_salesinput_day2.[Peak Ticket Hour], qry_salesinput_day3.Day, qry_salesinput_day3.[Store Am sales], qry_salesinput_day3.[Store Pm sales], qry_salesinput_day3.[Store Praline Sales], qry_salesinput_day3.[Store Merch Sales], qry_salesinput_day3.[Store Postage Sales], qry_salesinput_day3.[MailOrder Praline Sales], qry_salesinput_day3.[MailOrder Merch Sales], qry_salesinput_day3.[MailOrder Postage Sales], qry_salesinput_day3.[MailOrder Total Sales], qry_salesinput_day3.[Wholesale Praline Sales], qry_salesinput_day3.[Wholesale Merch Sales], qry_salesinput_day3.[Wholesale Postage Sales], qry_salesinput_day3.[Wholesale Total Sales], qry_salesinput_day3.[Planned Store hours], qry_salesinput_day3.[Actual Store hours], qry_salesinput_day3.[Planned Store Dollars], qry_salesinput_day3.[Actual Store Dollars], qry_salesinput_day3.[Overtime Dollars], qry_salesinput_day3.[Training Dollars], qry_salesinput_day3.[Am # of Tickets], qry_salesinput_day3.[Pm # of Tickets], qry_salesinput_day3.[Peak Ticket Hour], qry_salesinput_day4.Day, qry_salesinput_day4.[Store Am sales], qry_salesinput_day4.[Store Pm sales], qry_salesinput_day4.[Store Praline Sales], qry_salesinput_day4.[Store Merch Sales], qry_salesinput_day4.[Store Postage Sales], qry_salesinput_day4.[MailOrder Praline Sales], qry_salesinput_day4.[MailOrder Merch Sales], qry_salesinput_day4.[MailOrder Postage Sales], qry_salesinput_day4.[Wholesale Praline Sales], qry_salesinput_day4.[MailOrder Total Sales], qry_salesinput_day4.[Wholesale Merch Sales], qry_salesinput_day4.[Wholesale Postage Sales], qry_salesinput_day4.[Wholesale Total Sales], qry_salesinput_day4.[Planned Store hours], qry_salesinput_day4.[Actual Store hours], qry_salesinput_day4.[Planned Store Dollars], qry_salesinput_day4.[Actual Store Dollars], qry_salesinput_day4.[Overtime Dollars], qry_salesinput_day4.[Training Dollars], qry_salesinput_day4.[Am # of Tickets], qry_salesinput_day4.[Pm # of Tickets], qry_salesinput_day4.[Peak Ticket Hour], qry_salesinput_day5.Day, qry_salesinput_day5.[Store Am sales], qry_salesinput_day5.[Store Pm sales], qry_salesinput_day5.[Store Praline Sales], qry_salesinput_day5.[Store Merch Sales], qry_salesinput_day5.[Store Postage Sales], qry_salesinput_day5.[MailOrder Praline Sales], qry_salesinput_day5.[MailOrder Merch Sales], qry_salesinput_day5.[MailOrder Postage Sales], qry_salesinput_day5.[MailOrder Total Sales], qry_salesinput_day5.[Wholesale Praline Sales], qry_salesinput_day5.[Wholesale Merch Sales], qry_salesinput_day5.[Wholesale Postage Sales], qry_salesinput_day5.[Wholesale Total Sales], qry_salesinput_day5.[Planned Store hours], qry_salesinput_day5.[Actual Store hours], qry_salesinput_day5.[Planned Store Dollars], qry_salesinput_day5.[Actual Store Dollars], qry_salesinput_day5.[Overtime Dollars], qry_salesinput_day5.[Training Dollars], qry_salesinput_day5.[Am # of Tickets], qry_salesinput_day5.[Pm # of Tickets], qry_salesinput_day5.[Peak Ticket Hour], qry_salesinput_day6.Day, qry_salesinput_day6.[Store Am sales], qry_salesinput_day6.[Store Pm sales], qry_salesinput_day6.[Store Praline Sales], qry_salesinput_day6.[Store Merch Sales], qry_salesinput_day6.[Store Postage Sales], qry_salesinput_day6.[MailOrder Praline Sales], qry_salesinput_day6.[MailOrder Merch Sales], qry_salesinput_day6.[MailOrder Postage Sales], qry_salesinput_day6.[MailOrder Total Sales], qry_salesinput_day6.[Wholesale Praline Sales], qry_salesinput_day6.[Wholesale Merch Sales], qry_salesinput_day6.[Wholesale Postage Sales], qry_salesinput_day6.[Wholesale Postage Sales], qry_salesinput_day6.[Planned Store hours], qry_salesinput_day6.[Actual Store hours], qry_salesinput_day6.[Planned Store Dollars], qry_salesinput_day6.[Actual Store Dollars], qry_salesinput_day6.[Overtime Dollars], qry_salesinput_day6.[Training Dollars], qry_salesinput_day6.[Am # of Tickets], qry_salesinput_day6.[Pm # of Tickets], qry_salesinput_day6.[Peak Ticket Hour], qry_salesinput_day7.Day, qry_salesinput_day7.[Store Am sales], qry_salesinput_day7.[Store Pm sales], qry_salesinput_day7.[Store Praline Sales], qry_salesinput_day7.[Store Merch Sales], qry_salesinput_day7.[Store Postage Sales], qry_salesinput_day7.[MailOrder Praline Sales], qry_salesinput_day7.[MailOrder Merch Sales], qry_salesinput_day7.[MailOrder Postage Sales], qry_salesinput_day7.[MailOrder Total Sales], qry_salesinput_day7.[Wholesale Praline Sales], qry_salesinput_day7.[Wholesale Merch Sales], qry_salesinput_day7.[Wholesale Postage Sales], qry_salesinput_day7.[Wholesale Total Sales], qry_salesinput_day7.[Planned Store hours], qry_salesinput_day7.[Actual Store hours], qry_salesinput_day7.[Planned Store Dollars], qry_salesinput_day7.[Actual Store Dollars], qry_salesinput_day7.[Overtime Dollars], qry_salesinput_day7.[Training Dollars], qry_salesinput_day7.[Am # of Tickets], qry_salesinput_day7.[Pm # of Tickets], qry_salesinput_day7.[Peak Ticket Hour], DateAdd("yyyy",-1,Date()) AS [year a go]
FROM (((((qry_salesinput_day1 INNER JOIN qry_salesinput_day2 ON (qry_salesinput_day1.Period = qry_salesinput_day2.Period) AND (qry_salesinput_day1.Week = qry_salesinput_day2.Week)) INNER JOIN qry_salesinput_day3 ON (qry_salesinput_day2.Week = qry_salesinput_day3.Week) AND (qry_salesinput_day2.Period = qry_salesinput_day3.Period)) INNER JOIN qry_salesinput_day4 ON (qry_salesinput_day3.Week = qry_salesinput_day4.Week) AND (qry_salesinput_day3.Period = qry_salesinput_day4.Period)) INNER JOIN qry_salesinput_day5 ON (qry_salesinput_day4.Week = qry_salesinput_day5.Week) AND (qry_salesinput_day4.Period = qry_salesinput_day5.Period)) INNER JOIN qry_salesinput_day6 ON (qry_salesinput_day5.Week = qry_salesinput_day6.Week) AND (qry_salesinput_day5.Period = qry_salesinput_day6.Period)) LEFT JOIN qry_salesinput_day7 ON (qry_salesinput_day6.Week = qry_salesinput_day7.Week) AND (qry_salesinput_day6.Period = qry_salesinput_day7.Period);

I hope that this code can help you help me

thanks,
Marco

 

[scriverb]

You state. .

his is my dql to the Master Qry.... when I place your code in my query it returns not 11/1/02 but instead it returns 5/30/03...

Don't you mean it returns 5/20/03? That is exactly what you asked for. One year prior to todays date. Yesterday was May 20, 2004. This code gives you one year earlier.

Don't understand why you want 11/1/02? Please explain.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]