By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,448 Members | 934 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,448 IT Pros & Developers. It's quick & easy.

Report from multiple queries

P: 2
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
Apr 1 '10 #1
Share this Question
Share on Google+
3 Replies


patjones
Expert 100+
P: 931
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
Apr 2 '10 #2

P: 2
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)
Apr 5 '10 #3

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1. 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
Apr 5 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.