Re: Date not within range query

When Name Autocorrect works it is nice. The problem is that this feature
can cause problems and often fails to work. I always turn it off.

see Failures caused by Name AutoCorrect
http://allenbrowne.com/bug-03.html

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Cory" <Cory@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AAE627F8-753E-4B9C-AFEC-3DFBE5536DCA@xxxxxxxxxxxxxxxx
I have noticed before that updating a field name in one place can update it
in another. Does this work fairly univerallsy? Would it change the
reference to that Field name in every place that it appears?

Cory

"John Spencer" wrote:

Two query solution

Save following as QSeen
SELECT EntityKey
FROM LastSeen
WHERE LastSeen.Date > Forms!FindNoCoverage!ToDate
AND LastSeen.Date < Forms!FindNoCoverage!FromDate)

Now use that and your entities table to do an unmatched query.
SELECT Entities.*
FROM Entities LEFT JOIN QSeen
On Entities.EntityKey = QSeen.EntityKey
WHERE QSeen.EntityKey is Null

Or all in one query IF field names or tablenames don't require brackets
SELECT Entities.*
FROM Entities LEFT JOIN
(SELECT EntityKey
FROM LastSeen
WHERE LastSeen.Date > Forms!FindNoCoverage!ToDate
AND LastSeen.Date < Forms!FindNoCoverage!FromDate) as L
On Entities.EntityKey = L.EntityKey
WHERE L.EntityKey is Null

This may fail due to the use of Date as a field name. Date is a reserved
word in Access - it is a function that returns the current system date.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Cory" <Cory@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:63075309-A560-42B2-AF51-FE96261A177D@xxxxxxxxxxxxxxxx
I am working with a database that has a number of related tables. The
query
I am building only concerns two however. Entities and Last Seen. I
have
created a form that asks for the date range with txtDateTo and
txtDateFrom
text boxes. I wish to execute a query that retrieves all of the
entities
that do not have any Last Seen linked records with a date that falls in
the
range. So I want only the Entities that where not seen during a date
range.
The Entities table has a one to many relationship with the Last Seen
table.
I have tried a number of combinations including

SELECT Entities.* FROM Entities INNER JOIN LastSeen ON
Entities.[EntitiyKey]=LastSeen[EntityKey] WHERE LastSeen.Date >
Forms!FindNoCoverage!ToDate AND LastSeen.Date <
Forms!FindNoCoverage!FromDate

It does not return an error, but by doing some research in the tables,
there
are date gaps that I used to test the query and it did not return the
records. This is my first query messing with JOINS and finding records
that
have no linked records that fall inside a date range.

Any help will be appreciated.

Cory





.

Relevant Pages

  • Re: Query criterion by form control unsteady
    ... First thing to do would be to turn off Name AutoCorrect. ... Then compact the database: ... switch the query to SQL View ... Copy the whole SQL statement out to clipboard. ...
    (microsoft.public.access.queries)
  • Re: Prompted for info on a query that was deleted/renamed long ago
    ... The "Track Name AutoCorrect" feature is yet another great fix to a problem ... I develop a query called query1. ... new query (call it reallynewquery) that uses the query newquery. ... Options and turn OFF "Track Name Autocorrect". ...
    (microsoft.public.access.queries)
  • Re: PHP + MySQL + Queries that depend on each other?
    ... if echo mysql_error; ... If it fails, it will display the succeeded error. ... Should this procedure be done every time a query is done, or, ... or take down the server between queries. ...
    (comp.lang.php)
  • Re: Access query is renaming output fields - dreadfully inconvenient
    ... First thing to try would be disabling the Name AutoCorrect check boxes ... Create a new database. ... > I am running a select query with a lot of different output fields. ...
    (microsoft.public.access.queries)
  • Re: query from form
    ... The following forces the type of test to be a string. ... I have a question about applying to this to a query within a report. ... The query that fails refers to the text box on the form. ...
    (microsoft.public.access.queries)