Prompt for Date, default time of day

[Guest]

Hello, I apologize if this is a duplicate, can't find my post.

I need a query that will prompt for StartDate and EndDate, where the
StartDate time of day is always 22:00 and the EndDate time of day is always
08:00. I want to see all records that start *or* end between the StartDate
at 22:00 and the EndDate at 08:00.

Thanks, Mary

 

[Guest]

Try this

SELECT MyTable.MyDateField
FROM MyTable
WHERE (((MyTable.MyDateField) Between Format([StartDate] & "
08:00","dd/mm/yyyy hh:nn") And Format([EndDate] & " 08:00","dd/mm/yyyy
hh:nn")))

 

[Van T. Dinh]

SELECT ...
WHERE ( [RecordStartDateTime] BETWEEN DateAdd("h", 22, [Enter Start Date:])
AND DateAdd("h", 8, [Enter End
Date:]) )
OR ( [RecordEndDateTime] BETWEEN DateAdd("h", 22, [Enter Start Date:])
AND DateAdd("h", 8, [Enter End
Date:]) )

 

[Guest]

Thanks, that worked!

To save one prompt, is it possible to have users enter one date, and list
records that start on the date entered at 22:00 or later and end by 08:00 the
day after the date entered?

SELECT ...
WHERE ( [RecordStartDateTime] BETWEEN DateAdd("h", 22, [Enter Start Date:])
AND DateAdd("h", 8, [Enter End
Date:]) )
OR ( [RecordEndDateTime] BETWEEN DateAdd("h", 22, [Enter Start Date:])
AND DateAdd("h", 8, [Enter End
Date:]) )

--
HTH
Van T. Dinh
MVP (Access)

Hello, I apologize if this is a duplicate, can't find my post.

I need a query that will prompt for StartDate and EndDate, where the
StartDate time of day is always 22:00 and the EndDate time of day is always
08:00. I want to see all records that start *or* end between the StartDate
at 22:00 and the EndDate at 08:00.

Thanks, Mary

 

[Guest]

You can use the dateadd function, when you ask for one parameter

SELECT ...
WHERE ( [RecordStartDateTime] BETWEEN DateAdd("h", 22, [Enter Date:])
AND DateAdd("h", 8,
dateadd("d",1,[Enter Date:])) )
OR ( [RecordEndDateTime] BETWEEN DateAdd("h", 22, [Enter Date:])
AND DateAdd("h", 8,
dateadd("d",1,[Enter Date:])) )

Thanks, that worked!

To save one prompt, is it possible to have users enter one date, and list
records that start on the date entered at 22:00 or later and end by 08:00 the
day after the date entered?

SELECT ...
WHERE ( [RecordStartDateTime] BETWEEN DateAdd("h", 22, [Enter Start Date:])
AND DateAdd("h", 8, [Enter End
Date:]) )
OR ( [RecordEndDateTime] BETWEEN DateAdd("h", 22, [Enter Start Date:])
AND DateAdd("h", 8, [Enter End
Date:]) )

--
HTH
Van T. Dinh
MVP (Access)

Hello, I apologize if this is a duplicate, can't find my post.

I need a query that will prompt for StartDate and EndDate, where the
StartDate time of day is always 22:00 and the EndDate time of day is always
08:00. I want to see all records that start *or* end between the StartDate
at 22:00 and the EndDate at 08:00.

Thanks, Mary

 

[Guest]

Awesome - Thanks!

You can use the dateadd function, when you ask for one parameter

SELECT ...
WHERE ( [RecordStartDateTime] BETWEEN DateAdd("h", 22, [Enter Date:])
AND DateAdd("h", 8,
dateadd("d",1,[Enter Date:])) )
OR ( [RecordEndDateTime] BETWEEN DateAdd("h", 22, [Enter Date:])
AND DateAdd("h", 8,
dateadd("d",1,[Enter Date:])) )

Thanks, that worked!

To save one prompt, is it possible to have users enter one date, and list
records that start on the date entered at 22:00 or later and end by 08:00 the
day after the date entered?

SELECT ...
WHERE ( [RecordStartDateTime] BETWEEN DateAdd("h", 22, [Enter Start Date:])
AND DateAdd("h", 8, [Enter End
Date:]) )
OR ( [RecordEndDateTime] BETWEEN DateAdd("h", 22, [Enter Start Date:])
AND DateAdd("h", 8, [Enter End
Date:]) )

--
HTH
Van T. Dinh
MVP (Access)




Hello, I apologize if this is a duplicate, can't find my post.

I need a query that will prompt for StartDate and EndDate, where the
StartDate time of day is always 22:00 and the EndDate time of day is
always
08:00. I want to see all records that start *or* end between the
StartDate
at 22:00 and the EndDate at 08:00.

Thanks, Mary

 

[Van T. Dinh]

Try:

SELECT ...
WHERE ( [RecordStartDateTime] BETWEEN DateAdd("h", 22, [Enter Date:])
AND DateAdd("h", 32, [Enter Date:]) )
OR ( [RecordEndDateTime] BETWEEN DateAdd("h", 22, [Enter Date:])
AND DateAdd("h", 32, [Enter Date:]) )