apply a filter on date-column in a query

  • Thread starter Thread starter sverre
  • Start date Start date
S

sverre

Hi

I have applied the condition below graphically (shown i SQL-statement)

HAVING (((Sesam.TRN_START_DATE)>#1/31/2008#)

It does not work. I understand that you need to do some kind of conversion,
but what and how? There are several different functions in the access built
in gallery that probably could be used, but there are no examples on how to
use these functions.

The table has date/time as format and blank in the format line below. I have
not used any link specs, only imported the table from a Excel 2002 Workbook
and let Access decide the format.

best regards
Sverker
 
What do you mean by "It does not work."? Error messages? Unexpected results?

What is the data type of the field where the data is stored? Text or
Date/Time?

Do you live where dates are DD/MM/YYYY or MM/DD/YYYY?

Please provide some sample data.
 
Hello Jerry,

Sorry for being unclear. "It does not work" means that when I apply the
condition >=#2008-02-01# on the TRN_START_DATE-column (I do it graphically
by writing in the condition row) Access includes in the query result also
rows that have the value 2008-01-31 and 2008-01-30. Rows where dates are
2008-02-01 (1st of february 2008) or greater should be included in query
results, nothing else.

The data type of the field in the table is: Date/time. This data type has
Access set by itself when I imported the Excel-file.

The full sql is shown below:

SELECT Sesam.TRN_TYPO_CODE, Sesam.TRN_UNSTARTED, Sesam.TRN_START_DATE

FROM Sesam

GROUP BY Sesam.TRN_TYPO_CODE, Sesam.TRN_UNSTARTED, Sesam.TRN_START_DATE
HAVING (((Sesam.TRN_TYPO_CODE)=20) AND ((Sesam.TRN_UNSTARTED)="N") AND
((Sesam.TRN_START_DATE)>=#2/1/2008#)) OR (((Sesam.TRN_TYPO_CODE)=21) AND
((Sesam.TRN_UNSTARTED)="N") AND ((Sesam.TRN_START_DATE)>=#2/1/2008#)) OR
(((Sesam.TRN_TYPO_CODE)=22) AND ((Sesam.TRN_UNSTARTED)="N") AND
((Sesam.TRN_START_DATE)>=#2/1/2008#));

Converting the date-column to text would one solution but when I next month
import
the excel-file then Access would have set the data type to Date/time again.
Better
would be to find I way to rewrite my filter condition instead.

Many thanks in advance!
Sverre
 
I rewrote the query below. It makes it a little simpler to read plus may
better deal with any regional date/time settings. See what it does.

SELECT DISTINCT Sesam.TRN_TYPO_CODE,
Sesam.TRN_UNSTARTED,
Sesam.TRN_START_DATE
FROM Sesam
WHERE Sesam.TRN_TYPO_CODE In (20, 21, 22)
AND Sesam.TRN_UNSTARTED = "N"
AND Sesam.TRN_START_DATE >= #1 February 2008#;
 
Thank you, works perfect!

I have never understood the meaning of select.distinct instead of select only.
What kind of error is avoided with the add of distinct?

regards
Sverre
 
The keyword DISTINCT combines records returned when all the values in the
fields being returned are equal.

For example, given a table with three fields (FirstName, LastName, Counter)
John Spencer 21
John Spencer 22
John Spencer 33
and the first two fields were returned by the query without distinct, you
would see
John Spencer
John Spencer
John Spencer

With Select DISTINCT, you would see
John Spencer

A disadvantage is that the use of DISTINCT returns records that cannot be
updated. This makes sense - if I tried to change John there is no way for
the database to know if I want to change all three records or only one (and
if only one - which one).

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
To add to John's excellent answer, your original SQL statement was doing much
the same with the GROUP BY of all the returned fields. The DISTINCT made
things much easier to read. Also there is a DISTINCTROW predicate. DISTINCT
looks for duplicates in returned fields whereas DISTINCTROW also checks the
fields in the table that are not returned in the query.

Speaking of easier to read, notice how I got rid of the multiple AND and OR
clauses and used an IN statement. Sometimes just wadding through all the ANDs
and ORs can cause problems.

If you ever have trouble with a query, simplify it until it works correctly
then build it back up to return the expected records. Also never forget that
your data might not be exactly what you expect.
 
Back
Top