Automate a Date Field

[simon4amiee]

Hello again, the statement below is a snippet from a union query and is repeated 6 times within the SQL Statement for various reasons, I have to manually change this every month, again is there i tiny bit of code I can insert to replace the following so that it automatically runs the previous months data.

WHERE ArrivalDateTime >= 'February 1 2014'

 

[plog]

Union queries? Updating criteria monthly? I smell the whiff of an improperly structured database.

Can you post the full UNION query?

 

[simon4amiee]

Hi I've PM'd you, I just wanted to automate this without having to edit every month an rreplace February with March etc 6 times (its a 5 minte job) but still there must be a way.

 

[plog]

Here's what you should do:

1. Make a backup of your UNION query.
2. Remove that portion of the WHERE clause from the UNION query.
3. Create another query using the UNION query as its data source.
4. Apply the criteria that you removed to the DischDate field of that new query.

Run that and it should produce the same results as the UNION query did before. You have now moved that date criteria to one spot, making it easier to update. But what, there's more.

You can automate this by calculating what that value should be each month. Look into Access data functions (http://www.techonthenet.com/access/functions/).

 

[namliam]

Where arrivaldate > dateserial(Year(date()), Month(date())-1,1)

Perhaps add a Format around the dateserial if the arrivaldate is actually a text field, however if it is a text field I strongly suggest you reconsider your design.

 

[simon4amiee]

Cant thank you guys enough, Im only and analyst and dont have the SQL knowledge but know the right questions to ask to get there. namlian and plog have been invaluable recently to me.