Query data for Monday through Friday

[randolphoralph]

I have a Form named Orders that has the fields OrderNumber, DateofOrder, and Price.

I am trying to create a query that will give me the data for Monday through Friday if the user enters Friday's date when they run the query.


I am not sure what I need to enter in the Query Criteria for DateofOrder.

 

[randolphoralph]

I have tried this and had no luck

Between [Orders]![DateofOrder] And DateAdd("d",5,[Orders]![DateofOrder])

 

[vbaInet]

What should it return if a non Friday date was entered?

 

[randolphoralph]

If the user entered a non-Friday date then they would get the last five days of orders. So the user will need to make sure they enter the correct date for Friday.

 

[vbaInet]

You would be better off performing the checks in vba and building the sql string.

1. Use the IsDate() function to check that the string entered is a valid date string.
2. If it is use the Weekday() function to check if it's a Friday. Friday is 6. If it is, then use:

Between "#" & Format(MyDate.Value - 5, "mm/dd/yyyy") & "#"  And "#" & format(MyDate.value, "mm/dd/yyyy") & "#"

Where mydate.value is the value from the textbox control.

 

[randolphoralph]

vbaInet,

I think that I am in over my head. I am not sure what you mean. I am trying to use the query to pull back 5 days worth of information in a report using the DateofOrder field. So lets say the user enters last Friday 6/11/2010 I need the query to pull all the records from Monday 6/7/2010 to Friday 6/11/2010. I guess I could use the following in the Criteria of the query.

Between [Enter Monday's Date:] And [Enter Friday's Date:]

I was just trying to make it easier on the user and build the query so they only have to enter Friday's date.

 

[vbaInet]

Simplifying things for your users would require more work from the developer in most cases. So if you want to effectively perform this operation, you need to perform some validation checks on the date you're using for the criteria before sending it to the query.

Here's a link on pbaldy's website. Have a look at section 2 for the Search Form:

http://baldyweb.com/BuildSQL.htm

You will still need the code line I provided in my last post.