Excel Query to Access, w/ Variable Date

[Venom]

Hello,
I've got an Excel Query pulling data from an Access database. But I need a way to only pull items with a "Next_Due_Date" date of "<=NOW()" and a "Status" of "1".

However, "<=NOW()" is not allowed, I get a syntax error. It seems it only wants to use static values.

Any suggestions?

Thanks =D

 

[Gary McMaster]

You can create a "Parameter Query" in MS Query.

Create the selection criteria as you would normally. It will look something like the next line.

>=#3/23/2009#

Change the selection criteria to look like the next line.

>=[Enter Date]

The square brackets [] specify that this parameter will be obtained from the user.

After you save the query and then refresh it, Excel will pop up a dialog box asking for the "date" parameter.

In the dialog box you can specify that you want it to take the parameter from a certain cell for future refreshes. You can put any formula you like in the designated cell.

Hope it helps.

Gary

 

[Venom]

That works beautifully, thank you very much. =D

 

[Gary McMaster]

You're welcome. I'm glad my description of the process was adequate.

There is precious little regarding this topic in the MS Query help file. If you search for "Parameter" in that file you will find it.

I know that you can pass multiple parameters. I don't know if there is a limit to the number of parameters.

I have never tried hiding, locking or otherwise hacking the cell that contains the parameter. Don't know if any of that will work but its easy enough to try.

Gary

 

[Venom]

I placed =NOW() in cell A1... Then hid Column A. So hiding the cell seems to have no issues.

It also opened the door to drop-down lists for varying auto-queries... =)
Like searches between DateA and DateB... etc.

 

[Gary McMaster]

It also opened the door to drop-down lists for varying auto-queries... =)

Agreed ... It can be very powerful. I use it often to work around the shortcomings of our MRP system's reporting features (Btrieve Database).

Gary