Prompt for Date, default time of day

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mary - 28 Jun 2005 19:04 GMT

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

Reply to this Message

Ofer - 28 Jun 2005 22:27 GMT

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")))

> Hello, I apologize if this is a duplicate, can't find my post.
>
[quoted text clipped - 4 lines]
>
> Thanks, Mary

Reply to this Message

Van T. Dinh - 29 Jun 2005 00:09 GMT

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:]) )

Signature

HTH
Van T. Dinh
MVP (Access)

> Hello, I apologize if this is a duplicate, can't find my post.
>
[quoted text clipped - 4 lines]
>
> Thanks, Mary

Reply to this Message

Mary - 29 Jun 2005 19:01 GMT

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:])
[quoted text clipped - 14 lines]
> >
> > Thanks, Mary

Reply to this Message

Ofer - 29 Jun 2005 20:47 GMT

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!
>
[quoted text clipped - 20 lines]
> > >
> > > Thanks, Mary

Reply to this Message

Mary - 29 Jun 2005 23:26 GMT

Awesome - Thanks!

> You can use the dateadd function, when you ask for one parameter
>
[quoted text clipped - 30 lines]
> > > >
> > > > Thanks, Mary

Reply to this Message

Van T. Dinh - 29 Jun 2005 23:32 GMT

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:]) )

Signature

HTH
Van T. Dinh
MVP (Access)

> 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?

Reply to this Message