weird query issue

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

I have a single table that contains customer information
with 5 queries that I'm trying to run. These queries are
of a range type where I'm filtering on a particular date
range. Four of the queries work fine. However, one query
refuses to return correct information. When i put in my
date ranges, the query returns only a portion of the
accounts and in addition reports other accounts that are
beyond the date range. The command that I'm using is
"between[Type Beginning Date:]And[Type Ending Date:]".
I've looked at the data field in an attempt to determine
the cause but without success. Any suggestions would be
greatly appreciated.

Thanx in advance
 
Ray,

Have you defined the input types as dates? If not, Access may be
interpreting them as strings, and the conditions you describe makes it
sound like it is interpreting the dates as strings.

Go to your query in design view, place the cursor in the grey area
next to where the tables are displayed. Right click and select
Parameters, then in the boxes provided enter your parameter names
(include the braces - [Type Beginning Date:], and make sure the text
is exactly as it is in your query.

--
HTH

Dale Fye


I have a single table that contains customer information
with 5 queries that I'm trying to run. These queries are
of a range type where I'm filtering on a particular date
range. Four of the queries work fine. However, one query
refuses to return correct information. When i put in my
date ranges, the query returns only a portion of the
accounts and in addition reports other accounts that are
beyond the date range. The command that I'm using is
"between[Type Beginning Date:]And[Type Ending Date:]".
I've looked at the data field in an attempt to determine
the cause but without success. Any suggestions would be
greatly appreciated.

Thanx in advance
 
Also be aware that dates when entered as a criterion for a SQL query
(as here) will be interpreted, if possible, as if in US format
(mm/dd/yyyy), whatever your local format is. I say "if possible"
because Access makes things even more confusing by silently
interpretting dates that are "impossible" in US format as being in a
format in which they are possible - quite conceivably your local
format!

An example:

If your locale is the United Kingdom, where the normal date format is
dd/mm/yyyy, SQL will still insist on interpreting "1/12/2003", when
entered as a criterion, as January 12, but "13/12/2003" will (since
there isn't a 13th month) be interpreted as December 13, which is
probably what you intended. An error is not thrown unless an entered
date cannot be interpreted in _any_ conceivable format (e.g.
"40/25/2014").

I think that this is a serious deficiency in Access SQL - others
disagree. If the date format for criteria cannot be localised, then an
error should be thrown if a "badly" formatted date is passed - it
shouldn't be silently converted, IMHO.


I have a single table that contains customer information
with 5 queries that I'm trying to run. These queries are
of a range type where I'm filtering on a particular date
range. Four of the queries work fine. However, one query
refuses to return correct information. When i put in my
date ranges, the query returns only a portion of the
accounts and in addition reports other accounts that are
beyond the date range. The command that I'm using is
"between[Type Beginning Date:]And[Type Ending Date:]".
I've looked at the data field in an attempt to determine
the cause but without success. Any suggestions would be
greatly appreciated.

Thanx in advance
Click to expand...
 
Back
Top