queries and criteria

[Guest]

I have a table that has 5 fields labeled "Contact Date 1, Contact Date 2,
Contact Date 3, etc." I need to be able to pull either one date, or a range
of dates from each of the Contact Date fields. For example, I currently need
to pull all records that have yesterday's date and I know that 2 of the
contact date fields have that date in them, how do I format the criteria so I
can pull all the records for that date?

 

[PC Datasheet]

You need to redesign your tables; they are not normalized. You need
something like:
TblCustomer
CustomerID
etc

TblContact
ContactID
CustomerID
ContactDate
etc

 

[John Spencer (MVP)]

If you can't redesign your table structure (you should), then you would need to
put the criteria on separate lines of the query grid criteria. Something like:

Field: Contact Date 1
Criteria (line 1): #1/1/2005#

Field: Contact Date 2
Criteria (line 1): <Leave blank>
Criteria (line 2): #1/1/2005#

Field: Contact Date 3
Criteria (line 1): <Leave blank>
Criteria (line 2): <Leave blank>
Criteria (line 3): #1/1/2005#

If you are doing this in the SQL statement then use the OR conjunction between
the criteria vice AND. Something like:

WHERE [Contact Date 1] = #1/1/2005# OR [Contact Date 2] = #1/1/2005# OR [Contact
Date 3] = #1/1/2005#