Conditional Query Criteria

[paulkc]

I would like to be able to run a query based on the date in a particular field. I want to run certain days of the week such as Thursday - Wednesday beginning with the current week. I cannot seem to figure out how to get this work.

I have also tried to use a parameter but for some reason the query does not return any records when I put the date in through a parameter input. If I put that same date in as criteria, it works fine. Any thoughts on that?

What I would really like to do is be able to enter a desired start date and run 7 days beginning with that date.

 

[Joe4]

As long as your date is in Date format (and not Text), you can create a paramter query by enterting criteria like this:

Between [enter start date] And [enter end date]

This will prompt you to enter a start date and end date and return all records that fall in between the two. Just enter the dates like a normal date (do not need to surround with #'s).

 

[paulkc]

I've tried that. Just to clarify the original date fields is in a Text format. I then use CDate function to convert it to date format so I can query it. I don't know if that is why I am having trouble or not but I thought I would mention it.

Assuming that this should work and I am just making a simple error, can I use it twice in an expression such as this: Between [Enter Start Date] And [Enter Start Date] +7
Will this work so I only have to enter it once and it automatically calculates the end date based on the start date?

 

[Joe4]

I've tried that. Just to clarify the original date fields is in a Text format. I then use CDate function to convert it to date format so I can query it. I don't know if that is why I am having trouble or not but I thought I would mention it.

Are you using the CDATE function in the same query as where you are entering your expression? Sometimes Access is a little picky on running criteria on calculated fields, especially if you are converting data types. Try converting to a date using the CDATE function in one query, then create a new query based on this query where you apply the criteria and see if that works (sometimes it helps to have the intermediate "helper" query).

Assuming that this should work and I am just making a simple error, can I use it twice in an expression such as this: Between [Enter Start Date] And [Enter Start Date] +7
Will this work so I only have to enter it once and it automatically calculates the end date based on the start date?

No, it doesn't like that. Try it and you will see that you get an error. If you want to go this way, I would recommend creating a user selection Form where they enter the criteria, and build off of that.

 

[paulkc]

That is getting a little more involved than I would like to be at this point. What approach could I use to just start on Wednesday of the current week and end on the following Tuesday?

 

[Joe4]

That is getting a little more involved than I would like to be at this point. What approach could I use to just start on Wednesday of the current week and end on the following Tuesday?

I think it may be possible to write a formula to do that, but that may end up being more complex than creating a selection Form.

 

[alansidman]

I concur with Joe. Search forms do not have to be complicated. Look at Mike Alexander's tutorial on them and you will have yours working very quickly.

http://www.datapigtechnologies.com/flashfiles/searchform.html

Alan