Union Query Date pass through

[CJ]

Hi,

I have a union query that links 10 queries together and this code in the date field to alway run for last month
Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)

This works well...

I have being asked to copy the above and have it set up to run different month periods setups (muliple months) this means amending the code but then opening all 10 queries to amend the order date field.

My Question is there anyway I can just add the date code in the 1st query and have all the other 9 pick up that date code, it would save me so much time.

Thanks

 

[mole999]

I've been using this lately, it probably will adapt, but I don't know how

declare @BEGINDATE datetime, @ENDDATE datetime
SET @BEGINDATE = '20151201'
Set @ENDDATE = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@BEGINDATE)+1,0))

The end date allows me to get the last day of the month set in @begindate

It saved me editing both every time I ran for a bit more information, and adjusts for 28, 30 and 31 days automatically

 

[xenou]

I would suggest having your queries link to a date that is stored in a table or form record. Then you can use it in all the queries but update it "in one place". I like a form because that becomes my "tool" for running the queries - open the form, set the dates, use a button to kick off the query.

For example, using a form:

select * from Table1 
where TransDate >= (DateValue([Forms].[Form1].[StartDate])) AND TransDate <=(DateValue([Forms].Form1].[EndDate]))

IN this case, Form1 is the form and [StartDate] and [EndDate] are the fields on the form (textboxes).

One wrinkle is that textboxes can be free-form and I don't like text for dates. So my general practice is to have a table that the form is bound to, and the textboxes on the form are bound to two date fields in the table. In this case, you can then link to the form fields or the actual table (since they are bound, the value is the same). This ensures correct typing of data as valid date data types. Also since you have bona fide dates now, you don't need the date value function anymore.

One more wrinkle is you don't want that table to have multiple records. So I will give the table and ID field, with a numeric data type, with a validation rule that the value must be equal to 1, and make it the primary key. So there can never, ever be more than one record in this table. Potential disaster avoided.

So, in summary - have a location for you dates, in a table or form, so you can use it in your queries and update in in one place as needed.



Note: MSAccess does not have variables in SQL statements the way that SQL Server does.