Someone Help Please!!!???

[CarrieW]

I have a very simple database to track training and I am having trouble getting it to pull up the information I want. What I have is 3 types of training that need to be done yearly. I want to be able to go in once a month and pull up a report "training due this month" and have it pull up all records that are 1 year or more old or are blank. Right now, in the criteria block I am using <Date()-356 under each date in the query but what this is doing is pulling up only the records that are late on all 3 dates. I want something that says "if any" of these dates are old or blank, show the record. I am by far not a genious when it comes to all this so any help anyone could provide would be greatly appreciated or I'm going to be bald soon from pulling out all my hair! LOL ;-)



~Carrie

http://stores.ebay.com/Charming-Creations

 

[DBAMJA]

If you notice in the design view of a query, there are multiple lines for the criteria. Under each of the types of training, have the criteria on different lines thus making it an OR query instead of an AND query.



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[CarrieW]

Thank you so much! That did get me a step closer, however now I have a new issue. LOL I put <Date()-356 Or Is Null under each date area on different lines to make it an "or" query which pulled up more names however now it's showing names that are NOT more than a year old. Is there something else wrong with that expression? I use it when I'm only trying to pull info from one date field and it works fine. Thank you so much again! I've been going crazy with this!!

~Carrie

http://stores.ebay.com/Charming-Creations

 

[GingerR]

why are you using Date()-356? There are 365 days in a year, not 356.But sometimes there are not 365 days in a year (leap year). Instead you may want to use the DateAdd function to correctly subtract a year from the current date.

does at least one of the three fields always have a date in it? How does a field being null satisfy your query? What I mean is, if all three date fields are NULL, is that a record you want to show up, or not? Maybe just having the formula as the criteria is satisfactory, not the "is null" part?



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DBAMJA]

I'm going to have to think about this one for a bit. Are you sure that some of the records that are being returned have all of the dates between now and a year ago?

I'll think about it some more and get back to you.



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[CarrieW]

Oops, LOL I meant -365. Yes, the null is necessary because if I don't have a date on file for them completing the training I need to know that. That's why I put that in there. And no, right now I have 2 individuals who I have no dates for on any of the 3 training requirements and a couple of people have one missing, etc.

~Carrie

http://stores.ebay.com/Charming-Creations

 

[CarrieW]

What it appears to be doing is pulling up all the records that have a null field in them therefore showing the dates that are both overdue and not overdue. I took out the "is null" in the query and now no records show up. I just tried updating my own record (where all my dates are current) to reflect an overdue date and it also shows all my dates now. I'm thinking I'm going to have to just build separate queries for each because I don't want ALL the dates to show up all the time. I really appreciate your help.

~Carrie

http://stores.ebay.com/Charming-Creations

 

[PHV]

Can you please post the SQL code ?
The query builder is known to generate wrong sql when you play with the OR operator.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CarrieW]

Here it is....

SELECT [Main Table].[LAST NAME:], [Main Table].[SATE DATE:], [Main Table].[LOAC DATE:], [Main Table].[ANTI-TERRORISM DATE:]
FROM [Main Table]
WHERE ((([Main Table].[SATE DATE:])<Date()-365 Or ([Main Table].[SATE DATE:]) Is Null)) OR ((([Main Table].[LOAC DATE:])<Date()-365 Or ([Main Table].[LOAC DATE:]) Is Null)) OR ((([Main Table].[ANTI-TERRORISM DATE:])<Date()-365 Or ([Main Table].[ANTI-TERRORISM DATE:]) Is Null));


~Carrie

http://stores.ebay.com/Charming-Creations

 

[PHV]

"if any" of these dates are old or blank
Your WHERE clause seems OK for the above purpose.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GingerR]

If this is still not working for you, you might want to post some sample data of records that you both want to be returned by your query, and which you don't, and which ones are not being returned that you want returned, and visa-versa.

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244