specifing dates in a report

[Guest]

I have a report that specifies two dates. [DOCKTIME] and [DEPARTTIME]. I am
needing to show Boats that have docked and departed by date. The critiria
for Departtime is "Between [Enter Date mm/dd/yy 00:01] And [Enter Second Date
mm/dd/yy 23:59] Or Is Null" but I can not get the Docktime to set the
critiria I want.

All Docking Records for a specified date and prior to that date if the
Departtime is equal or null.

[Enter Date mm/dd/yy 00:01] And [Enter Second Date mm/dd/yy 23:59] Or ???

Yet is there a way to search each field by entering the date to represent 24
hours other than the way it is set now. (my field is entered by general
date/time).

Thanks in advance
Jen

 

[Allen Browne]

In place of Between ... And, you could use:
- greater than or equal to the starting date, and
- less than the day after the end date.

Something like this:
(>= [StartDate] And < DateAdd("d", 1, [EndDate])) Or Is Null

Since this is for a report, you could omit the parameters from the query,
and instead build a WhereCondition for OpenReport. That is more efficient,
and particularly so where you are offering lots of criteria options for the
user. There's an example in Method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
but you still need to adapt that for less than the day after.

 

[Wayne Morgan]

Some possible helps.

1) If you only enter a date (no time entered) the time portion is zero. This
equates to midnight on that date. So, while this will work for the front end
(where you have the time 00:01), it won't work for the back end. However,
what does work is:

= [Enter Date] AND < [Enter Second Date] + 1

Note, no = in the second part of the expression. The +1 will add one day to
what the user enters and with the time assumed to be midnight, the less than
will return any time before midnight the day after the date the user
entered.

You may also need some parentheses to include your Or statement:

(>= [Enter Date] AND < [Enter Second Date] + 1) Or Is Null

2) Date parameters in a query usually need to be specified for Data Type.
Look at the SQL view of your query and you should see the parameters listed
in the first line with their data type defined. If not, you may need to add
it. The line should look like:

PARAMETERS [Enter Date mm/dd/yy 00:01] DateTime, [Enter Second Date mm/dd/yy
23:59] DateTime;
SELECT......etc.

The part between the brackets should be identical to the parameter as you've
typed it in the main body of the query. Define all of your Date/Time type
parameters this way. This can also be done in with the query in Design View
by going to the Query menu and choosing Parameters...

The dates will need to be in US format, which is the way you've told the
user to enter them.

3) Create a form that has a date picker and textbox for each parameter. Let
the user pick the date from the date picker then get the parameter value
from the form. This will help save typing errors and the user trying to "out
smart" your request. Just using the parameter and letting the query pop it
up doesn't allow you to limit what the user enters. With a form, you can
control what they enter. #2 still applies.

--
Wayne Morgan
MS Access MVP

I have a report that specifies two dates. [DOCKTIME] and [DEPARTTIME]. I am
needing to show Boats that have docked and departed by date. The critiria
for Departtime is "Between [Enter Date mm/dd/yy 00:01] And [Enter Second
Date
mm/dd/yy 23:59] Or Is Null" but I can not get the Docktime to set the
critiria I want.

All Docking Records for a specified date and prior to that date if the
Departtime is equal or null.

[Enter Date mm/dd/yy 00:01] And [Enter Second Date mm/dd/yy 23:59] Or ???

Yet is there a way to search each field by entering the date to represent
24
hours other than the way it is set now. (my field is entered by general
date/time).

Thanks in advance
Jen

 

[Guest]

I have attempted to enter theses critirias and none will generate the search
I need.

I need two critirias

1. = or > (for the begintime) of the endtime
2. a specific date for the endtime

example

I want all the records for all departures on the 1/19 and null values.

However I want the report to show all the dockings for the 1/19 and before
if the value for endtime is null.

In place of Between ... And, you could use:
- greater than or equal to the starting date, and
- less than the day after the end date.

Something like this:
(>= [StartDate] And < DateAdd("d", 1, [EndDate])) Or Is Null

Since this is for a report, you could omit the parameters from the query,
and instead build a WhereCondition for OpenReport. That is more efficient,
and particularly so where you are offering lots of criteria options for the
user. There's an example in Method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
but you still need to adapt that for less than the day after.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a report that specifies two dates. [DOCKTIME] and [DEPARTTIME]. I am
needing to show Boats that have docked and departed by date. The critiria
for Departtime is "Between [Enter Date mm/dd/yy 00:01] And [Enter Second
Date
mm/dd/yy 23:59] Or Is Null" but I can not get the Docktime to set the
critiria I want.

All Docking Records for a specified date and prior to that date if the
Departtime is equal or null.

[Enter Date mm/dd/yy 00:01] And [Enter Second Date mm/dd/yy 23:59] Or ???

Yet is there a way to search each field by entering the date to represent
24
hours other than the way it is set now. (my field is entered by general
date/time).

Thanks in advance
Jen

 

[Guest]

IT WORKED FINALLY...................THANKS............THANKS.............

I have attempted to enter theses critirias and none will generate the search
I need.

I need two critirias

1. = or > (for the begintime) of the endtime
2. a specific date for the endtime

example

I want all the records for all departures on the 1/19 and null values.

However I want the report to show all the dockings for the 1/19 and before
if the value for endtime is null.

In place of Between ... And, you could use:
- greater than or equal to the starting date, and
- less than the day after the end date.

Something like this:
(>= [StartDate] And < DateAdd("d", 1, [EndDate])) Or Is Null

Since this is for a report, you could omit the parameters from the query,
and instead build a WhereCondition for OpenReport. That is more efficient,
and particularly so where you are offering lots of criteria options for the
user. There's an example in Method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
but you still need to adapt that for less than the day after.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a report that specifies two dates. [DOCKTIME] and [DEPARTTIME]. I am
needing to show Boats that have docked and departed by date. The critiria
for Departtime is "Between [Enter Date mm/dd/yy 00:01] And [Enter Second
Date
mm/dd/yy 23:59] Or Is Null" but I can not get the Docktime to set the
critiria I want.

All Docking Records for a specified date and prior to that date if the
Departtime is equal or null.

[Enter Date mm/dd/yy 00:01] And [Enter Second Date mm/dd/yy 23:59] Or ???

Yet is there a way to search each field by entering the date to represent
24
hours other than the way it is set now. (my field is entered by general
date/time).

Thanks in advance
Jen