Report from multiple queries

[patrickmcdiver]

I have a table which includes employee information as well as the dates of about 5 differnent certifications. I am trying to create a query that will eventually be the basis for a report that will list the employee and any (and only) certifications that are due based on today's date. I have tried various methods but my limited understanding of Access and VBA has been hampering me.

Can anyone give some tips on how I might accomplish this. Thanks

[patjones]

Can you point out what you've tried so far and post any code that you've written up to now? We'll use that as a starting point. Thanks.

Pat

[patrickmcdiver]

Well, I've kinda went in a different direction. I am now trying to write code for the report that will be generated from the qry where only cert dates that are passed the current date (Date()) will appear. Same idea, just handling it from the report end instead of the query. I am code illiterate but have been trying to look through examples. I am trying to write code on the load filter that will make visible the fields that are past due and not the ones that aren't:

If "First Aid Due Date" >= Date Then Visible = True

Something like that for each of the certs (ie... CPR, Rigging, Dive Physical, AED)

[patjones]

Hi -

Usually the Visible property is connected to a particular form object. Also, in the comparison you need to write out the actual field name that you're comparing to the current date; so you would say something like:

If [DateOfFirstAid] >= Date Then Me.TextBoxName.Visible = True

Here you would replace "DateOfFirstAid" with whatever the relevant field name from your recordsource is, and "TextBoxName" with the name of the box you're filling in on the report.

I think however that the query approach is still the best way for you to go, and in the query criteria you would simply put [DateOfFirstAid] <= Now(). If you're building this in Query Design view, it's actually even simpler...on the criteria line of whatever the applicable date field is you'd put <=Now(). This will cause the query to pull only records where the certification in question is expired.

Let me know how it goes.

Pat