Need your suggestions

[jmriddic]

I built this db for our HR department. When I met with them about the reports one of the ones they wanted was based off the 8 Licensure/Certification fields and the date fields associated with them. They need one report that is going to list the Licensure/Certification and the expiration date for that License so they can notify the employee that has or will be expiring. I am not sure I would build a query that is going to reference 8 date fields. Never have tried anything this complex before.

 

[vbaInet]

If you're unsure how to write criteria for 8 fields, are you comfortable writing it for 2 date fields?

 

[jmriddic]

I have defintely written queries where I have done Between[Beginning Date] And [Ending Date] but in this case never for multiple date fields. I do want to get prompted sixteen times when I need to enter it only once.

 

[jmriddic]

Excuse me don't want to prompted sixteen times

 

[vbaInet]

You will get prompted once as long as the parameter name is the same across the 16 fields. So if I wrote [Enter a Date:] as a parameter under 16 fields in a query, it will pop up just once.

 

[jmriddic]

So if I do this Between[Beginning Date:] And [Ending Date:] I should get prompted once?

 

[vbaInet]

That's right. Just make sure to copy and paste it across instead of typing it.

 

[jmriddic]

I tried your suggestion in the query LicExpDate for date 08/31/12 since all three test records have that have that date for some of the License Date field in question. I pull no records when I run it though.

 

[JLCantara]

Hi JMRiddic,

Simple:

Your between functions are all on the same line meaning and between them (check to the left of this line). However, some dates are missing...

On 3 different lines, they wil be ored and it will do the job you're expecting.

JLCantara.

 

[jmriddic]

I actually have 8 date fields I need to check because I have 8 text boxes for License/Certification information and each of those date fields are to be associated with each of those. So will access allow me to do that many lines?

 

[jmriddic]

I tried what you suggested but now it will not run the query saying its too complex. I will restate the original problem. I need to check 8 license expiration date fields in order to see if any of them fall in the date range I put. I do not need the parameter box to show more than once. Its being suggested to put them on the same line or put them on seperate lines starting on the OR line. Which one is correct?

 

[JLCantara]

Hi JMRiddic,

Tested your last version, and here it works fine. Try this: copy the query text, open a new query and paste the previous text...

JLCantara.

 

[jmriddic]

Ok I see what you mean. I ran it as well. Only problem its repeating the employee more than once if it finds matches on any of those fields. If you try 08/01/12 to 08/31/12 you will see what I mean. I just need them to show once. Suggestions?

 

[JLCantara]

Holly pistol JM what the f... is the duplate table doing there???? That's where your problem is.

JLCantara.

 

[jmriddic]

Not sure what you are meaning. There's only three test records in the database. Duplicate file?

 

[JLCantara]

Check LicenseExpDate: table 'Contract Employees' occurs twice so you end up with the cartesian product of the query result!!!

JLCantara.

 

[jmriddic]

How did I do that? Oh well. Its fixed now. Thanks for the help.

 

[JLCantara]

You'r right Pat, but will you be listened???

JLCantara.

 

[vbaInet]

You mean if jmriddic will listen to Pat's advice?

 

[jmriddic]

I am all for making this easier on myself. Any suggestions on a linking field for this seperate table. I am assuming this would mean a subform where the license fields are?