Eliminate hard-coded dates in Access queries

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
I have an Access application containing a lot of queries that select records based on a date range; e.g., between 10/1/01 and 9/30/02. There are over 100 queries in the database, and it's a real maintenance hassle to have to go thru them all the time to change the date ranges each year (the one above, for example, needs to be changed this year to between 10/1/02 and 9/30/03).

What I'd like to do is to eliminate the need to constantly update the hard-coded dates with an expression that would automatically use dates based on the system date. This is not a valid expression, but illustrates what I want: "between 10/1/Year()-1 and 9/30/Year()".

Is there any way to do this in Access?
 
Joined
Jun 24, 2001
Messages
49
I don't know if 'datediff' could be used or not. But I think that you could create a table with the dates needed, then have the queries reference the date in the table. When you neeed to change your date parameter just update the table.
 

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
Originally posted by Moxy1:
I don't know if 'datediff' could be used or not. But I think that you could create a table with the dates needed, then have the queries reference the date in the table. When you neeed to change your date parameter just update the table.
Thanks for the fast response, Moxy. DateDiff won't do what I need, but the separate table will. We had discussed doing that, but I wanted to make sure there wasn't a simple way to do it with expressions... but I guess not.

Thanks!
 
Joined
Jul 23, 1999
Messages
49
If you are comfortable working with forms and objects, you could create a simple form and add two instances of the MS Date and Time Picker (that's the happy control that looks like a combo box but when you click it it will show you a calendar). This would allow you to choose the dates you wanted. Then in your query, set the criteria to something like

=form1.DateBox.Value (names for example only)

The above type of entry would go into the criteria field for each of your dates fields in your query, with each referencing the appropriate date control.

You would need to play with the format of the value that is returned by the date picker control and be sure it works with your query.
 
Joined
Jul 23, 1999
Messages
49
Ooooops. Didnt remember that you wanted between, so change
your example of "between 10/1/Year()-1 and 9/30/Year()" to

"between form1.DatePicker.value and form1.DatePicker2.value"

That's pretty close. I dont have a sample handy to post but will look for one.
 

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
Originally posted by Moxy1:
... I think that you could create a table with the dates needed, then have the queries reference the date in the table.
OK... but how. I just created a small table with fields containing the beginning and ending dates for some often used date ranges. However, the query won't run unless there's a JOIN between it and the other tables/queries in the query. However, there is not field I can join them on.

To be more specific, the dates table is DatesTable (ain't that original?!). Two of the fields are "audityrstart" and audityrend", and they're currently set to 11/1/02 and 10/31/03, respectively. I want the criteria for the date in another table in the query to be "Between [datestable]![audityrstart] And [datestable]![audityrend]". That expression doesn't work, and you get an error message about the SQL statement not being able to work because of "an ambiguous join".

How can I use the dates from DatesTable in any query?
 
Joined
Jul 23, 1999
Messages
49
Heres a sample i just threw together and tested. This is the syntax for the criteria in your query:

"Between [Forms]![Form3]![DatePicker].[Value] And [Forms]![Form3]![DatePicker2].[value]"
 

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
Originally posted by Big Otis:
Ooooops. Didnt remember that you wanted between, so change
your example of "between 10/1/Year()-1 and 9/30/Year()" to

"between form1.DatePicker.value and form1.DatePicker2.value"

That's pretty close. I dont have a sample handy to post but will look for one.
Otis: Just replied to Moxy as your message came in. This doesn't work. Instead of a form, I'm using a table, so I tried "between table.datestable.audityrstart And table.datestable.audityrend", and it doesn't work... ambiguous join... with the DatesTable in the query. If I take it out of the query, it doesn't know what the fields are, and the "enter parameter" box appears.

The problem is this is a LOOKUP table only; there is no way to join it to anything else in the query. So how do I get the query to acknowledge the existence of DatesTable and take the values from the appropriate fields in it?
 
Joined
Jul 23, 1999
Messages
49
Quickest way i know how is with the form. If you want i can walk you thru the steps and have it working for you in just a few minutes. May not be the best solution for long term but will beat the heck out of editing a bunch of queries.
 
Joined
Jul 23, 1999
Messages
49
ok, i mispoke.

Here is how you can do it with an expression. I just tested this and it works. In the criteria of your query use something like the following:

"Between "06-01-" & Year(Now())-1 And "06-30-" & Year(Now())"
 
Joined
Oct 13, 2000
Messages
941
Yeah, or I'd actually do it

Between DateSerial(Year(Date())-1,10,1) and DateSerial(Year(Date()),9,30)

But Otis's example will work just fine.
 
Joined
Jul 23, 1999
Messages
49
Having never seen (that i recall) that function, i just looked it up. That's cool. I have a few places i can use that to clean up some code.
 

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
Originally posted by Big Otis:
ok, i mispoke.

Here is how you can do it with an expression. I just tested this and it works. In the criteria of your query use something like the following:

"Between "06-01-" & Year(Now())-1 And "06-30-" & Year(Now())"
Otis: Tried what you suggested, and it works! Simple, intuitive, and don't have to remember much... Great. Thanks.
 
Joined
Jun 24, 2001
Messages
49
follow up question: if this query if for a report that is being run for a fiscal year of 10/1 to 9/30 when it is run from 10/1 through 12/31 the year should be the current year. How would you adjust for this piece?
 

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
Originally posted by Moxy1:
follow up question: if this query if for a report that is being run for a fiscal year of 10/1 to 9/30 when it is run from 10/1 through 12/31 the year should be the current year. How would you adjust for this piece?
An excellent point, Moxy. It effectively kills this approach as a fix. So I still need to set up a table or form that can be referenced for the dates (which can be updated at will by the user).
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top