Parameter Query "HELP"

[pete61uk]

Hi All,

I need an Access2K query (Table[OrderDate]) to veiw reports going back a 'week' (7 days) based on (and including) the user-defined date.

I've been trying to get it going for hours without any success. Can anyone help?

[Narasinha]

Originally Posted by pete61uk

Hi All,

I need an Access2K query (Table[OrderDate]) to veiw reports going back a 'week' (7 days) based on (and including) the user-defined date.

I've been trying to get it going for hours without any success. Can anyone help?

Okay, you want to do something like this, right?

SELECT * from TABLE where ((Table.OrderDate) Between (selectedDate) And (selectedDate - 7))

In "Design View" for the query you would have in the "Criteria" section Is Not Null And Between [BeginningDate] And [EndingDate]

It's been a while since I created SQL queries in Access. I don't recall exactly how Access does computations with fields of type Date. Is this for a web-connected database?

[pete61uk]

Narasinha,

Thank you for your post. You wrote:

"SELECT * from TABLE where ((Table.OrderDate) Between (selectedDate) And (selectedDate - 7))

In "Design View" for the query you would have in the "Criteria" section Is Not Null And Between [BeginningDate] And [EndingDate]"

Sorry, its not a between dates function. What is needed is one of two things. Either:

1) A parameter query where the user inputs a single date and the result is a list displaying transactions covering a seven day period. Or (I'm guessing here):

2) An expression that will display a weeks worth of transactions on the day (date()) of the query.

This is tricky, I know. I've looked through the no-help files and a book on Access2K and the date functions are a nightmare.

[pete61uk]

No luck figuring-out a user-defined parameter query.

However, the following does give seven days worth of reports from the date queried.

>DateAdd("d",-7,Date())

[Narasinha]

Oh, wait a second. (I'm assuming you want this function locally, not web-based) You want a requestor to pop up asking for the specific date? I've done this before with invoicing database I made for work.

[paulhiles]

Hi Pete,

Originally Posted by Narasinha

(I'm assuming you want this function locally, not web-based)

Narasinha raises a valid point here... are you working with forms in Access itself? OR are you querying an Access database using ASP and SQL queries? I've basic experience of using both methods... but it would be useful if you gave a bit more detail on how your user would interact with the database.

Also, I deleted the Poll and corresponding thread in the Break Room.. as you pointed out... there was little, if any interest there! :c)

Paul

[pete61uk]

Narasinha wrote:
(I'm assuming you want this function locally, not web-based)

The function was required for a report generated by Access 2000 itself. Though, if there is a perameter query that works I'd still be interested.

However, there do seem to be a lot of cross-compatability issues as not all parameters migrate successfully to all Access versions?

Paul. To be honest, the poll was as much to find out just how much interest there is in Access (for future reference) as it was to draw attention to my need for help.

As it was, after way too many hours scratching my now receding and thinning hairline, I managed to find the solution. It is good to know that at least this time someone was kind enough to respond.

Thank you both.

[Narasinha]

Originally Posted by pete61uk

The function was required for a report generated by Access 2000 itself. Though, if there is a perameter query that works I'd still be interested.

I'm glad you found what you needed. I used something similar in a report in Access. When you run the report for the monthly invoices, a pop-up requests the month, then another yes/no choice.

Originally Posted by pete61uk

Thank you both.

You're quite welcome. I certainly don't consider myself an expert with Access (I used to do mostly SQL queries through Perl to Access databases), but I'm willing to lend a hand when I can.

[pete61uk]

The tricky thing with questions like this is in that if you need to ask the question at all, it implies a certain "sense of urgency".

As such, the timelyness of any feedback is (if only psychologically) important. Even if it doesn't provide the answer needed it can point in an otherwise not thought of direction that will?

I had spent a thoroughly frustrating two days before I (out of despair) submitted the post. However, in doing so, it cleared my head and, as luck would have it, the answer came to me.

Narasinha wrote:

"When you run the report for the monthly invoices, a pop-up requests the month, then another yes/no choice"

I'd be interested in knowing how you did this, and why the yes/know?

Again, my thanks