Access: query criteria from tables??

[ctrlaltdel]

I have a several similar queries. Each month I have to change the date range criteria in each one to run my report (ie: >=03/01/04 and <=03/31/04).

Doing this in each query takes too long, and leaves the door open for mistakes.

Is there a way I can set my criteria in each query to tell it to go and read the values from other tables? Ideally, I'd like to see something like this for my criteria: " >=[startdatetable] and <=[enddatetable] " in all of my queries.

Then, all I'd have to do is change the value in the table called "startdatetable" to 03/01/04, and the value in the "enddatetable" to 03/01/04, and that would cover it for all the queries.

I am using Access 2000 on Win98 and am kind of new to Access (to this depth anyway).

[r937]

yes you can do that

or you can write the query to run for the month preceeding today's date, so you don't have to do anything, just run it any day in the following month to get the previous month's report

or something like that

[ctrlaltdel]

yes you can do that

or you can write the query to run for the month preceeding today's date, so you don't have to do anything, just run it any day in the following month to get the previous month's report

or something like that

Oh wow! That would work better! Um.... how might I do that?

[r937]

select foo
     , bar
     , datecol
  from yourtable 
 where datecol 
       between dateadd("m",-1
             , dateadd("d",-day(date())+1,date())
                      )
           and dateadd("d",-day(date()),date())

[ctrlaltdel]

Thanks so much for the help, 937, but would you be so kind as to treat me like a total newbie?

Where exactly do I put that code? I've only ever used the normal 'select query' grid to make queries. I assume this is supposed to go somewhere in the SQL view of the query?

If so, exactly where?

I'm sorry to make you spell it out for me...

[r937]

when creating a query, go to View > SQL View

paste it into the window, and run it

it will then be in Design View if you go there

[MarcusJT]

Is there a way I can set my criteria in each query to tell it to go and read the values from other tables? Ideally, I'd like to see something like this for my criteria: " >=[startdatetable] and <=[enddatetable] " in all of my queries.

Just so that you know, you CAN use parameters in Access stored queries, simply put a "?" in each place, and then supply the values (in the same order) when you call the query, like this:

EXECUTE MyQuery #03/01/04#, #03/02/04#

However, the solution that r937 presents is probably what you *actually* need.

[DaveMaxwell]

Just so that you know, you CAN use parameters in Access stored queries, simply put a "?" in each place, and then supply the values (in the same order) when you call the query

You can also do it just like it was listed earlier and just use the names like that. When you do your call, it will replace those fields in the same order they're listed.

I do it that way so I have a very readable query when I'm accessing the database directly, but yet it works through ado just fine.