Query Date Prompt

[Mcrawford]

I have a parameter query that prompts user for date. I'm not sure how to
explain this, but I've created a field in the query that pulls only the date
information from the date/time field in the table and not the time. I did
this by using DateValue([Call_Date]).

When the query prompts for date, I must enter 8/9/2007 for it to return
results. How can I set it to return results no matter what format user types?

8907
080907
08092007
etc.

I am new to Access 2003 and this is my first project, so I'll need detailed
responses. Thanks for any help you can provide.

Also, am I defining the fields properly in my table and query? Is there a
better way to get just date information and NOT time?

 

[Jerry Whittle]

You can't. At least you can't "no matter what what format user types". There
are just too many variables. For example is 6/7/1954 either June 7th, 1954 or
July 6th, 1954? In most of the world it's Day/Month/Year.

If you don't need the time stored in the table, remove it. For example you
might have the default value as Now(). Change that to Date() instead. You'll
need to go back and modify all your existing records.

Careful now! Don't mess with the dates if you ever think that you might need
them.

Personally I'd rather not have DateValue([Call_Date]) as a field. It's
possible that an index on Call_Date would not work. Instead I'd have
Call_Date as the field and make the criteria like so:

Between [Enter Call Date] and [Enter Call Date] +.99999

The .99999 takes the date entered up to 11:59:59 PM on that date.

Now to the Parameters statement. When prompted for a parameter, Access
guesses if you are putting in text, numbers, or dates. Sometimes it gets it
wrong. In fact it will cause errors with crosstab queries unless you
precisely define the data type of the parameter.

To define the parameter, go up to Query on the menu and pick Parameters in
the drop down. In the first box you need to put in the parameter just like
it's in the criteria of the query. Then you need to pick Date/Time in the
Data Type. The result should look like so:

Parameter Data Type
[Enter Call Date] Date/Time

 

[Mcrawford]

Thanks Jerry. That worked!

You can't. At least you can't "no matter what what format user types". There
are just too many variables. For example is 6/7/1954 either June 7th, 1954 or
July 6th, 1954? In most of the world it's Day/Month/Year.

If you don't need the time stored in the table, remove it. For example you
might have the default value as Now(). Change that to Date() instead. You'll
need to go back and modify all your existing records.

Careful now! Don't mess with the dates if you ever think that you might need
them.

Personally I'd rather not have DateValue([Call_Date]) as a field. It's
possible that an index on Call_Date would not work. Instead I'd have
Call_Date as the field and make the criteria like so:

Between [Enter Call Date] and [Enter Call Date] +.99999

The .99999 takes the date entered up to 11:59:59 PM on that date.

Now to the Parameters statement. When prompted for a parameter, Access
guesses if you are putting in text, numbers, or dates. Sometimes it gets it
wrong. In fact it will cause errors with crosstab queries unless you
precisely define the data type of the parameter.

To define the parameter, go up to Query on the menu and pick Parameters in
the drop down. In the first box you need to put in the parameter just like
it's in the criteria of the query. Then you need to pick Date/Time in the
Data Type. The result should look like so:

Parameter Data Type
[Enter Call Date] Date/Time

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a parameter query that prompts user for date. I'm not sure how to
explain this, but I've created a field in the query that pulls only the date
information from the date/time field in the table and not the time. I did
this by using DateValue([Call_Date]).

When the query prompts for date, I must enter 8/9/2007 for it to return
results. How can I set it to return results no matter what format user types?

8907
080907
08092007
etc.

I am new to Access 2003 and this is my first project, so I'll need detailed
responses. Thanks for any help you can provide.

Also, am I defining the fields properly in my table and query? Is there a
better way to get just date information and NOT time?

 

[Jamie Collins]

I'd have
Call_Date as the field and make the criteria like so:

Between [Enter CallDate] and [Enter CallDate] +.99999

The .99999 takes thedateentered up to 11:59:59 PM on thatdate.

Actually, the addition pushes the value beyond the last valid DATETIME
value by approx 136 milliseconds; also, it coerces the DATETIME value
to DECIMAL:

SELECT TYPENAME(DATE() + 0.99999)

returns 'Decimal' and you will have equality issues in when comparing
with DATETIME values:

SELECT 0.99999 = TIMESERIAL(23, 59, 59)

returns FALSE.

A further consideration is whether you can assume the user will supply
a DATETIME value with a 'time' element of midnight; probably not. The
best approach IMO with DATETIME parameters is in such circumstances is
to make no assumptions and use date time functionality (including
sticking to one second granularity) to 'round' the parameter value to
the first and last time granule of the day respectively e.g.

WHERE field_name
BETWEEN DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#,
param_value), #1990-01-01 00:00:00#)
AND DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, param_value),
#1990-01-01 23:59:59#)

Jamie.

--

 

[Jerry Whittle]

My little .99999 shortcut isn't bulletproof? Dang! 0.999983 is a little
closer but harder to remember.

Obviously your method is technically better.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'd have
Call_Date as the field and make the criteria like so:

Between [Enter CallDate] and [Enter CallDate] +.99999

The .99999 takes thedateentered up to 11:59:59 PM on thatdate.

Actually, the addition pushes the value beyond the last valid DATETIME
value by approx 136 milliseconds; also, it coerces the DATETIME value
to DECIMAL:

SELECT TYPENAME(DATE() + 0.99999)

returns 'Decimal' and you will have equality issues in when comparing
with DATETIME values:

SELECT 0.99999 = TIMESERIAL(23, 59, 59)

returns FALSE.

A further consideration is whether you can assume the user will supply
a DATETIME value with a 'time' element of midnight; probably not. The
best approach IMO with DATETIME parameters is in such circumstances is
to make no assumptions and use date time functionality (including
sticking to one second granularity) to 'round' the parameter value to
the first and last time granule of the day respectively e.g.

WHERE field_name
BETWEEN DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#,
param_value), #1990-01-01 00:00:00#)
AND DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, param_value),
#1990-01-01 23:59:59#)

Jamie.

 

[Jamie Collins]

My little .99999 shortcut isn't bulletproof? Dang! 0.999983 is a little
closer but harder to remember.

You got me: I had to refer to my notes for mine

FWIW:

? Round(CDec(TimeSerial(23, 59, 59)), 6)
0.999988

but why add an approximate DECIMAL to a DATETIME (thus coercing the
result to DECIMAL) when the time constructor expression TIMESERIAL is
easy to remember, more human readable and spot on? e.g.

Between [Enter CallDate] And ([Enter CallDate] + TimeSerial(23, 59,
59))

I use the more long winded (hence less human readable) DATEADD/
DATEPART version because it easily ports to other SQL DMBSs, notably
SQL Server.

Jamie.

--

 

[John W. Vinson]

but why add an approximate DECIMAL to a DATETIME (thus coercing the
result to DECIMAL) when the time constructor expression TIMESERIAL is
easy to remember, more human readable and spot on? e.g.

Between [Enter CallDate] And ([Enter CallDate] + TimeSerial(23, 59,
59))

And how is this simpler than

= [Enter CallDate] AND < DateAdd("d", 1, [Enter CallDate])

?

John W. Vinson [MVP]

 

[Jamie Collins]

TIMESERIAL is
easy to remember, more human readable and spot on

Between [Enter CallDate] And ([Enter CallDate] + TimeSerial(23, 59,
59))

And how is this simpler than

= [Enter CallDate] AND < DateAdd("d", 1, [Enter CallDate])

?

Mine isn't simpler; in fact, yours may be a better suited to Access/
Jet's floating point nature of DATETIME values (with caveat, see
below). But it's a case of 'apples and oranges' because yours is
fundamentally different approach.

You, John, are using closed-open representation of a period, where the
end date (instant) does not fall within the period itself. On the
other hand, Jerry is using the closed-closed representation, where the
end date is the last time granule (one second accuracy for Access/Jet)
within the period.

Now I like Jerry-closed-closed representation myself because I prefer
to state one 'fact' using one predicate:

target_date BETWEEN start_date AND end_date

The above is simple and easily understood by the person who inherits
my code. To do the same using the John-closed-open representation
requires two predicates:

start_date <= target_date
AND target_date < end_date

I find the <= then < to be less intuitive than BETWEEN (you can't use
BETWEEN with closed-open representation and expect correct results).
Also I find the fact that the end date is not within the period to non-
intuitive e.g. contrast the respective representations for the current
year (square bracket = closed, parenthesis = open):

[#2007-01-01 00:00:00#, #2008-01-01 23:59:59#]
[#2007-01-01 00:00:00#, #2008-01-01 00:00:00#)

I find that using a date in 2008 to represent the period 2007 to be,
well, odd.

I do have problems with the Jerry-closed-closed representation as
well. Primarily, it assumes DATETIME values of being of the same
granularity, in this case one second. My response to this is to always
use temporal functionality and put validation rules on every DATETIME
column to ensure they are, which works but DATETIME values of sub-
second granularity (as Jerry will get with by adding an approximate
DECIMAL to a DATETIME) will be rounded, fine by me but may cause some
people problems e.g.

SELECT
#2007-01-01 00:00:00# AS start_date_closed,
#2007-12-31 23:59:59# AS end_date_closed,
#2008-01-31 00:00:00# AS end_date_open,
#2007-12-31 00:00:00# + 0.99999 as target_date,
target_date BETWEEN start_date_closed AND end_date_closed
AS result_jerry_closed_closed,
(end_date_closed <= target_date AND target_date < end_date_open)
AS result_john_closed_open

The 'Jerry' result is FALSE and the 'John' result is TRUE. Getting a
TRUE result is arguably better.

Now for that caveat I mentioned. The most common representation I see
in the Access groups is a closed-closed representation BUT with one
day granularity and without taking any steps to ensure DATETIME values
are rounded to one day granularity e.g. the current year period:

[#2007-01-01 00:00:00#, #2007-12-31 00:00:00#]

Such users are going to have problems much more frequently because
they are omitting the 86398 DATETIME values between #2007-12-31
00:00:01# and #2007-12-31 23:59:59# inclusive from the timeline. Hence
we get almost daily posts about 'missing dates' and the ubiquitous
'dates are floating point' replies.

But what about the parameter value? Let's use yours as an example:

= [Enter CallDate] AND < DateAdd("d", 1, [Enter CallDate])

Let's assume this supposed to get all values that falls on the same
calendar day as the parameter value (because it's usually the case
here).

The above assumes that the parameter value will be a DATETIME value
with midnight as it's time value but let's say the person entered
39310.99999 (the only reason I can think of for a parameter name
'Enter CallDate', with an annoying space in it, is that a user will
see it); even with strongly typed parameter values (not always the
case in the groups, even for DATETIME values) the start- and end dates
aren't going to come out as intended e.g.

SELECT
CDATE(39310.99999) AS [Enter CallDate],
[Enter CallDate] AS start_date_closed,
DateAdd("d", 1, [Enter CallDate]) AS end_date_open

retuns

[#2007-08-16 23:59:59#, #2007-08-17 23:59:59#]

i.e. mostly the next day!

My approach for temporal data is to use strongly typed DATETIME values
and ensure their values are of known granularity, and that granularity
is usually (always?) one second for me. This is why -- finally gets to
the point -- I posted my recommendation that when using the closed-
closed representation the parameter value should get 'rounded' e.g.

SELECT
39310.99999 AS [Enter CallDate],
DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, [Enter CallDate]),
#1990-01-01 00:00:00#) AS start_date_closed,
DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, [Enter CallDate]),
#1990-01-01 23:59:59#) AS end_date_closed

returns

[#2007-08-16 00:00:00#, #2007-08-16 23:59:59#]

Jamie.

--