very weird query problem

  • Thread starter Thread starter Julian Cropley
  • Start date Start date
J

Julian Cropley

I have encountered I very weird problem whilst creating a query in
Access 2002. The query is based on another query which is comprised
of 2 linked access tables(backend).
The problem I have encountered is that when I try and match the
values of 2 fields dateTo and SiteType I get no records returned an
example might be.
SELECT RevQRes.*
FROM RevQRes
WHERE (((RevQRes.To)=#4/1/2002#) AND ((RevQRes.SiteT)="P"));

The above query returns no records yet when I search only on the To
field
SELECT RevQRes.*
FROM RevQRes
WHERE (((RevQRes.To)=#4/1/2002#));

I get many matching records with a SiteT of "P"
Date ReservationID Site_No From To Occupant SiteT
02/04/2002 10 28/03/2002 01/04/2002 N Drayton P
30/03/2002 11 26/03/2002 01/04/2002 r drayton P
23/03/2002 73 23/03/2002 01/04/2002 r bryant P
31/03/2002 100 27/03/2002 01/04/2002 john cherry P

I have checked and there is no space in the siteT field ie the value
is exactly "P"

Why is my query not functioning correctly, it doesn't return any
error messages yet clearly something is not right.

Here is the sql for the query this query is based on RevQRes

SELECT Rev.Date, Rev.ReservationID, Rev.Site_No, Rev.From, Rev.To,
Rev.Occupant, Sites.SiteT
FROM Rev INNER JOIN Sites ON Rev.Site_No = Sites.Site_No;
 
Julian,

It may not be so weird as it seems... I can think of two possible problems:

1. Some of the data entry is done through forms using the Now() function;
the problem with this is that although you may have formatted your fields,
sontrols etc. to only display the date part, it does actually store a time
part as well, whereas your criterion is a purely date part one, so no
matches! Solution: change the WHERE clause to:
WHERE ((Int(RevQRes.To))=#4/1/2002#)
to match the date part only.

2. If you're doing this in code rather than in query design, bear in mind
that to VBA all dates are US-formatted (mm/dd/yyyy), no matter what your
fields / controls / overall Windows settings. In that case your criterion
should be #04/29/2002#, not #29/04/2002#.

HTH,
Nikos
 
Back
Top