A REALLY hard Query Question

[ccflyer]

Hi,

Currently I have one query that prompts the user for a beginning date, and end date of a week. It then proceeds to show the user all records in that time frame and makes a table. Now I need another query that looks at the dates the user had entered and gets all the records from the week before the user's dates. This query would also be a table query, but separate from the first one.

If anyone at all is able to help me out, I will be deeply impressed and eternally grateful!

Thanks,
Chris C.

 

[QTNM]

Do you have any forms open and visible to the user when this query runs ?

It's a little work around, but you can hard code an input box to request the SDate and EDate ... set this to an invisible field on the form.

Then you can run your query based on these values that sit on the form, naked to the users eye.

I'm sure there is a better solution ... but this is one way I have worked around these types of problems.

QT = )

 

[ccflyer]

Currently, I don't have any forms open when the user types in the dates. What is actually happening is that the user opens a report based on the query, and that is where they type their dates. The reason I need the previous week is that on my report, I need an average of a value from the previous week.

-Chris

 

[mresann]

The method for finding the date a week earlier than a given date is quite easy. In your case, if a current week starts on Sdate, then the previous week encompasses ([Sdate]-7) to ([Sdate]-1) Keep in mind, the date field is actually a numeric field.

 

[ccflyer]

Ok, so now I do know how to 'find' the dates of the week before, but what about capturing the dates that the user enters the first time, so I can use '[Sdate]-7' and '[Sdate]-1'?

Thanks,
Chris

 

[mresann]

I am confused by your request. What do you mean by 'capturing' the dates? I though Sdate WAS the date you were capturing.

 

[ccflyer]

Sorry for not explaining totally.

When the first query runs, it prompts the user for Sdate (The Start date of week) and also the end date of the week, and then shows all records from that week. I want to somewhat "capture" or "save" that dates that they type in to be used in a second query.

The second query would take the dates from above and subtract 7 days in order to find the dates of the previous week. This query would show all records in the previous week.

Sorry for the confusion,
Chris

 

[sonny]

The easist way, is to create your own date prompts with popup forms or a single form. That way you can create both reports(one a subreport) based on the dates provided...

 

[mresann]

Create a field called "PDate" (for "Previous"), and assign it to the the value [SDate]-7

 

[ccflyer]

OK, so say I am going to go with the popup box, and then base my queries off that. I really don't know SQL at all, so if that is involved you guys might have to help me out a bit.

I went ahead and created a popup box with two text fields; one for SDate and one for EDate. I didn't think that they needed anything in the control source property, so I left it blank. Then in my query for the first week, I wasn't sure about the criteria but I tried this (where 'popupbox' is the name of my popup box):

Between ([popupbox]![SDate]) And ([popupbox]![EDate])

But this didn't really work.

Any suggestions?
-Chris

 

[ccflyer]

It is another form called 'popupbox'.

-Chris

 

[mresann]

Let me see if I have this straight.

1. You have a form with two dates
2. The first date is the start date for the query.
3. The second date is the end date for the query,
4. You want to create the query to return records from, and including, the first date, up to, and including, the second date.
5. You want a second query to return records from a week before the first start date, up to the day before the the first start date.
6. You want the records to be displayed in the same recordset.

Is that what you want?

 

[ccflyer]

That is excactly what I'm going for except I want the two weeks displayed in separate tables. One table for the current week, and another for the previous week.

-Chris