Listing All Records Between Two Dates

[lewando_bria]

I am supposed to make a report based on a query that lists all records found between two dates...here is the problem though...one value is supposed to be inputted by the user (this date is the week ending date) the second date is supposed to be the first day of the week....

I need to know if there is a way to perform an operation with the dates so that my query will find all the records that were created within that week while the user only puts in the week ending date...any help would be great!!!

Thanks!

BoRiS

 

[bradcccs]

The user enters the week ending date.

You define the first day of the week being? ? ?

Sunday / Monday / 7 days ago?
Do you ensure that the end date by the user is correct? ie: is actually the end of the week?


There are many date experts that peruse this forum. If you feed them the details, I am sure they will point you in the right direction.

Brad.

 

[Pat Hartman]

Where YourDate Between [Enter Week Ending Date] And [Enter Week Ending Date] - 7;

Notice that the same paramter is used twice. make sure that the two are identical or you'll receive two prompts.

 

[raskew]

This can be accomplished by just entering one date that falls
within the desired week.

Function MyMonday, given a date (Sunday - Saturday) will
return the date of Monday of that week.

Public Function MyMonday(myDate As Date)
'*******************************************
'Name:      MyMonday (Function)
'Purpose:   Return the date of Monday of the
'           current week
'*******************************************

MyMonday = myDate - (WeekDay(myDate) + IIf(WeekDay(myDate) <= 2, 7, 0) - 2) _
            + IIf(WeekDay(myDate) <= 2, 7, 0)
End Function

Copy/paste the code to a module in Northwind, then copy/paste the following query-SQL
to a new query (in Northwind)

PARAMETERS [enter date] DateTime;
SELECT Orders.OrderID, Orders.OrderDate, Format([OrderDate],"dddd") AS DOW
FROM Orders
WHERE (((Orders.OrderDate) Between MyMonday([enter date]) And MyMonday([enter date])+4));

Run the query and, when prompted, enter a date between 8/4/94 and 9/6/96. The query will
return all records dated Monday - Friday of that week.

 

[lewando_bria]

Thanks for the help everyone!

i did what pat posted and that worked great!!! Thanks again!