Question: Multiple Date Criteria Search

[grifter6]

The query I am trying to make needs to search each employee's records and return all records for the particular month, in this case it would be May. Though the search criteria for the month of May is typed in each criteria field, the query fails to return the info unless each field being searched has a date in the month of May. I am trying to remedy this, and allow the query to return all fields with a May date even if some the fields in the employee's record don't contain May Dates. A sample table with a sample query are attached, the query is set up as fore-mentioned yet returns no results, even though multiple fields contain Dates from May. Any Input would be much appreciated.

Thanks

 

[Zigzag]

Hi grifter6
Do a google for 'between'
You will have create a query that will give you results from the 1st of the month to the last of the month and the best way to achieve this is to use 'between'

G

 

[dkinley]

Your criteria needs to look like:

Like "5/*"

Because of your date format this interprets to anything with the month of 5. Another example:

Like "*10/1996"

will return records with any month, day 10, year 1996.

-dK

 

[georgedwilkinson]

Try:

SELECT Test.[Employee ID], Test.[Employee Name], Test.Title, Test.Department, Test.[LO/TO], Test.[Office Safety], Test.[Log Vehicles], Test.[Monitor Sprinkler]
FROM Test
WHERE Test.[Employee ID] in 
(SELECT Test.[Employee ID] FROM Test
WHERE (((Test.[LO/TO]) Like "5/**/****") AND ((Test.[Office Safety]) Like "5/**/****") AND ((Test.[Log Vehicles]) Like "5/**/****") AND ((Test.[Monitor Sprinkler]) Like "5/**/****"))
);

Not tested, air code, your code reformatted to display a concept, all the other usual disclaimers.

You know this is not normalized correctly, right? And you can use datepart or some other date function instead, right? Anyhoo, that's (^) the concept.

 

[Zigzag]

hi again,

Read your post aging and I think I might have missunderstood....
Try changing the query so that the criteria are not on the same row so it is an 'or' query instead of and 'and'.

G

 

[dkinley]

I should also mention that if you go across the criteria rows pasting that in ... you still won't return any records unless there is a record with the month of 5 in every field.

You will need to stairstep them down the criteria rows on each field (column) for it to work properly.

Example, suppose I had four columns:

Like "5/*"||||
||Like "5/*"|||
|||Like "5/*"||
||||Like "5/*"|

Remember: If you past criteria in the same row on different columns it is like ANDing. If you stagger them on rows on different columns it is ORing. Note: This is between field logic, not in-field logic as if you just went down the criteria column.

-dK

 

[dkinley]

LOL ... all posts are the same, take your pick!

-dK

~I think we got him covered 6 ways to Sunday.

 

[Zigzag]

.

 

[georgedwilkinson]

all posts are the same

Look again. And re-read the OP.

the query fails to return the info unless each field being searched has a date in the month of May

OP said nothing about the way he was testing for the date. Check post #4.

 

[dkinley]

Sigh ...

I stand corrected.

OP said nothing about the way he was testing for the date.

This is a true statement; I was remarking on the way the query was set up because the OP was ANDing and assumed this was the test.

But then again .. there is that assume word.

-dK

 

[grifter6]

Could someone just re-post the file with the changes made, and Everyone gets what I'm talking about right? When I enter the month, for it to return any results over all fields they have to have that month in each field, the query as is returns nothing.