Same Criteria For Multiple Fields

D

DumbWithData

I am trying to set-up a select query that will use the same criteria (i.e.
Today's Date) for multiple fields. I want the query to return any records
that have today's date in any of these three fields. I have tried this, but
it will not work for each field (only the first one with this criteria). Any
assistance would be appreciated.
 
G

Golfinray

If you look down at the criteria, there are several criteria rows. You should
also see where it says OR. You can put criteria information in several rows.
 
D

DumbWithData

I have tried this a number of ways. I have entered DATE() in the first
field's criteria, then DATE() in the other two fields' OR rows. This did not
work. I also tried putting it in the criteria for the first field, the first
OR row for the second field, and the second OR row for the third field, but
all to no avail. Any other suggestions?
 
D

DumbWithData

I should note that when I apply this criteria by itself to any single field,
it works fine. It is only when I try to apply it to multiple fields that I
have trouble.
 
K

KARL DEWEY

Put the criteria in a separate row for each different field - will look like
this --
Field1 Field2 Field3
Date()
Date()
Date()

One thing to note is that Date() contains only the date so if your fields
have the date and time there will not be a match.
 
R

raskew via AccessMonster.com

To further illustrate (using one of my own tables), if you place your
criteria in the same row, the underlying SQL is going to look like:

SELECT tblDevProcess.startTime, tblDevProcess.endTime, tblDevProcess.Remarks
FROM tblDevProcess
WHERE (((tblDevProcess.startTime)>=Date()-20)) AND (((tblDevProcess.endTime)
=Date()-20));

... with the AND meaning that all criteria must be met.

Conversely, placing the criteria in successive rows ( as Karl showed), the
underlying SQL will look like:

SELECT tblDevProcess.startTime, tblDevProcess.endTime, tblDevProcess.Remarks
FROM tblDevProcess
WHERE (((tblDevProcess.startTime)>=Date()-20)) OR (((tblDevProcess.endTime)
=Date()-20));

... with the OR meaning that a record will be returned if either of the
criteria are met.

Best Wishes - Bob

KARL said:
Put the criteria in a separate row for each different field - will look like
this --
Field1 Field2 Field3
Date()
Date()
Date()

One thing to note is that Date() contains only the date so if your fields
have the date and time there will not be a match.
I should note that when I apply this criteria by itself to any single field,
it works fine. It is only when I try to apply it to multiple fields that I
[quoted text clipped - 14 lines]
 
D

DumbWithData

Thank you all. It is clear now.

raskew via AccessMonster.com said:
To further illustrate (using one of my own tables), if you place your
criteria in the same row, the underlying SQL is going to look like:

SELECT tblDevProcess.startTime, tblDevProcess.endTime, tblDevProcess.Remarks
FROM tblDevProcess
WHERE (((tblDevProcess.startTime)>=Date()-20)) AND (((tblDevProcess.endTime)
=Date()-20));

... with the AND meaning that all criteria must be met.

Conversely, placing the criteria in successive rows ( as Karl showed), the
underlying SQL will look like:

SELECT tblDevProcess.startTime, tblDevProcess.endTime, tblDevProcess.Remarks
FROM tblDevProcess
WHERE (((tblDevProcess.startTime)>=Date()-20)) OR (((tblDevProcess.endTime)
=Date()-20));

... with the OR meaning that a record will be returned if either of the
criteria are met.

Best Wishes - Bob

KARL said:
Put the criteria in a separate row for each different field - will look like
this --
Field1 Field2 Field3
Date()
Date()
Date()

One thing to note is that Date() contains only the date so if your fields
have the date and time there will not be a match.
I should note that when I apply this criteria by itself to any single field,
it works fine. It is only when I try to apply it to multiple fields that I
[quoted text clipped - 14 lines]
it will not work for each field (only the first one with this criteria). Any
assistance would be appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top