# Solved: Access 2007 Query/Report to show expired dates



## dware2013 (Mar 1, 2013)

I am learning access and have build a database. in my tables we have dates to track employee expiration dates and some tables that show due dates for maintenance on equipment. Field names look like this.
LicExpires, MVR, AR, and so forth...
On one of my queries for equipment due dates, I used this formula in the query; InspDue: DateAdd("yyyy",1,[LastInsp]), which meant that I had to add another field "InspDue". 
On my employees query I have like 10 fields that have due dates in them and I was wondering how can I just get the names of the employees to show up with any one of these fields with due say like today and/or 30 days in the furture?
I have tried everything that I have read to make this work but when I use the >Date() expression I get back blank query. 
I am to my witts end here.
Also in the same table, if the field is null how can I get it to show on the same query?
Any help is appreciated.

Thanks,

David


----------



## OBP (Mar 8, 2005)

dware2013, welcome to the Forum.
You seem to be making progress with the database, the question that you ask is one of the more common ones.

First of all where have you tried entering the ">Date()"?

Do you have 10 fields with "Due Dates" or 10 Records?


----------



## dware2013 (Mar 1, 2013)

thanks for your quick reply. 
You would not believe how much time I have spent on this thing to get it where I have it. Most of my queries work the way that I need them. The employee one is the hardest one yet because they have more than one due date for each employee.
I was trying to enter it on the query design page under the field in the criteria.

10 fields that have due dates.


----------



## OBP (Mar 8, 2005)

Have you managed to get just one of them to work yet?


----------



## dware2013 (Mar 1, 2013)

No I have not by using the >Date() + 30, I think that is right it might be the other way. It jsut comes up with a blank query and it doesn't matter under what field I put it.


----------



## OBP (Mar 8, 2005)

OK, you need to ensure that the date you are testing will actually fall in the date + 30 days period, then in the Crieria of that field you enter the following
Between Date() And Date() + 30


----------



## dware2013 (Mar 1, 2013)

Actually the one that I have been using to test it was 08/22/2012 which would be inside the criterai but still a blank. So between would not work would it, because we need it to show anything past due as well as anything 30 days in the future.
I will try this and see though... I am at witts end on this...
I have read more books in the last year than I think in my whole life









Thanks for your replies...


----------



## OBP (Mar 8, 2005)

If you want it overdue as well as within the 30 days your <Date() + 30 should work.
Can you post a copy of the database with a dummy version of the personal data?
You will need to Compact & repair it and then Zip it to add it on here as an attachment using the Go Advanced and Manage Attachment Buttons.


----------



## dware2013 (Mar 1, 2013)

Looks like it my DB is to large to upload even after compressing. it is 623 and the max is 500.


----------



## OBP (Mar 8, 2005)

Do you want to send it me?


----------



## dware2013 (Mar 1, 2013)

OBP said:


> Do you want to send it me?


 Sure I can do that. If you don't mind looking at it.
Thanks.


----------



## OBP (Mar 8, 2005)

I have private mailed you my email address.


----------



## OBP (Mar 8, 2005)

I have the database, which query do you want me to look at?

PS can I ask to to take a look at Tabbed Mainforms as they work much more efficiently for users than Menus.


----------



## dware2013 (Mar 1, 2013)

OBP said:


> I have the database, which query do you want me to look at?
> 
> PS can I ask to to take a look at Tabbed Mainforms as they work much more efficiently for users than Menus.


I_DQF Qry for report


----------



## dware2013 (Mar 1, 2013)

Not sure what Tabbed Mainforms are. But if it will make it easier, sure.
Thanks!


----------



## OBP (Mar 8, 2005)

Which Date field do you want to filter?


----------



## dware2013 (Mar 1, 2013)

OBP said:


> Which Date field do you want to filter?


MVR, AR, MEDCARD, LicExpires and HMCert


----------



## OBP (Mar 8, 2005)

I owe you an apology, if I had read your original post a bit better at the start I would have realised you were using Greater than instead of Less Than in your Criteria.
See the attached Worksheet with a printscreen of the working query.


----------



## dware2013 (Mar 1, 2013)

OBP said:


> I owe you an apology, if I had read your original post a bit better at the start I would have realised you were using Greater than instead of Less Than in your Criteria.
> See the attached Worksheet with a printscreen of the working query.


Thanks.
So do I need to put it in just like it is on the spreadsheet that you attached? Step it down each time? and one last thing, do I need to put the Like statement in as on the spreadsheet? just want to be sure...
If this works, My boss is going to give me a big raise....
Thanks so much.
Let me try this and I will get back to you...

David


----------



## OBP (Mar 8, 2005)

David, I just what was in your current queries, the first one you pointed me to did not have all the required date fields, so I used the one that did.
The reason for stepping the Criteria is to use an "Or" in the criteria otherwise if they were on the same criteria line all the criteria would have to be met at the same time.
Of course that may be what you want.

I can send you back the database if you want.


----------



## dware2013 (Mar 1, 2013)

I have changed the qry as the spreadsheet and I am still getting date that is past the 30 days with the query.
See the attachment.


----------



## OBP (Mar 8, 2005)

It will display any record that if any one of the columns meets the criteria.
If you want only one query to test all the columns at the same time that will be very difficult unless you can arrange some form of hierarchy.
If you put the criteria all on just one line then all the fields have to meet all the criteria. Is that what you want?


----------



## dware2013 (Mar 1, 2013)

OBP said:


> It will display any record that if any one of the columns meets the criteria.
> If you want only one query to test all the columns at the same time that will be very difficult unless you can arrange some form of hierarchy.
> If you put the criteria all on just one line then all the fields have to meet all the criteria. Is that what you want?


Actually, What she wanted was to just show up the data that is past due plus the 30 days for each of those.
I am not sure if Access is capable of doing just that. 
We have another program that we use that is a Third party that supplies and we can't control what it does and we were trying to build a database based on that. That program has fields like these and it only pulls out the ones that are due. We just have to put in the date like between dates.
Would between dates work instead of <Date() + 30 in the criteria?


----------



## OBP (Mar 8, 2005)

I need to ask some questions about the date fields first.
Should they be ascending order, ie certain dates like the MVR met before the AR etc?
As your data shows some records when they are not.

Now back to your problem, if you want a list of overdue items then that can be arranged by running a query for each item which has it's own report, all the reports are added to one overall main report.
Can you show or send me the output from the old database report so that I can see the data it outputs.


----------



## dware2013 (Mar 1, 2013)

OBP said:


> I need to ask some questions about the date fields first.
> Should they be ascending order, ie certain dates like the MVR met before the AR etc?
> As your data shows some records when they are not.
> 
> ...


 So what you are saying, I would have to have a query for like MVR, AR, LicExpires and any others. Then make a report to pull all these into one report, right?
I have attached the output report. It doesn't show much. I am going to try and show you a screen shout of the data that we put in.

Thanks!


----------



## OBP (Mar 8, 2005)

OK, does your data structure reflect the same structure as the original database? As the output is either from separate query/subreports as I described or the date data is in individual records (a subtable) and not in individual fields.


----------



## dware2013 (Mar 1, 2013)

OBP said:


> OK, does your data structure reflect the same structure as the original database? As the output is either from separate query/subreports as I described or the date data is in individual records (a subtable) and not in individual fields.


 Oh, that I don't know. I don't really know the structure of that database. It is a third pary program and I would not even know how to see how it is set up... sorry


----------



## OBP (Mar 8, 2005)

OK, so do you want to just produce a list like the one in the attachment, but maybe not laid out exactly the same?

Do you have time to re-arrange any of the Data?

I can get your boss what she wants, but it depends on how quickly she wants it and how flexible your design can be.

For instance the data could be put in a temporary table prior to printing.


----------



## OBP (Mar 8, 2005)

There is also another way to split out just the overdue using IIF() statements, so that only the overdue name & date would be displayed.


----------



## dware2013 (Mar 1, 2013)

OBP said:


> OK, so do you want to just produce a list like the one in the attachment, but maybe not laid out exactly the same?
> 
> Do you have time to re-arrange any of the Data?
> 
> ...


 That what I sent you was just the beginning where I was doing some testing. Let me send you an attachment of the datebase that I have without anything in it. you would have to make up some dates for the fields but I think you will get a better view over all. I will send you an email when I get it done.


----------



## dware2013 (Mar 1, 2013)

OBP said:


> There is also another way to split out just the overdue using IIF() statements, so that only the overdue name & date would be displayed.


 IIF() statements?

Oh, we have not started using this yet so the time frame is not really crucial. We still have the other program going but we are trying to wean ourself off that one once we get this one working better. We have a client that has about 180 emplyees that we track this info for and they don't like to see 80 page expiration list... but most of the time they do because they are over due...lol
Thanks!


----------



## OBP (Mar 8, 2005)

If you take a copy of the query with the date criteria in it and remove the date criteria and just leave the other 2.
then add a new column Heading with the following as a demonstartion of an IIF().

MVROD: IIf([MVR]<Date()+30,"MVR - " & [MVR],"")

This will put a new value in the new column only when the MVR is overdue.
You can create one of these for each of the Date Fields to be tested and then Concatenate them at the end for printing purposes.

Try it out and see what you think about using it.


----------



## dware2013 (Mar 1, 2013)

OBP said:


> If you take a copy of the query with the date criteria in it and remove the date criteria and just leave the other 2.
> then add a new column Heading with the following as a demonstartion of an IIF().
> 
> MVROD: IIf([MVR]<Date()+30,"MVR - " & [MVR],"")
> ...


Would I have to add this in the Table first? When I put this in, I get an error message, "The expression you entered contains invalid synttax. You may have entered an operand without an operator."


----------



## dware2013 (Mar 1, 2013)

dware2013 said:


> IIF() statements?
> 
> Oh, we have not started using this yet so the time frame is not really crucial. We still have the other program going but we are trying to wean ourself off that one once we get this one working better. We have a client that has about 180 emplyees that we track this info for and they don't like to see 80 page expiration list... but most of the time they do because they are over due...lol
> Thanks!


Oh, how did your poker game go?
Ok, I got the if statement to work but it still pulls every employee's name even if the MVR OD date is not due. It just leaves a blank field beside the name. Is there something that I am doing wrong?


----------



## OBP (Mar 8, 2005)

No luck at Poker this week.

Have you now added the criteria to the new column

not isnull

or 
<>""

That should only find those that have MVR and the date in it.
The iif can be added for each date and then add another column to Concatenate them in to one column for printing


----------



## dware2013 (Mar 1, 2013)

OBP said:


> No luck at Poker this week.
> 
> Have you now added the criteria to the new column
> 
> ...


Maybe I am not doing it right. I added a column and in the field i put MVROD: IIf([MVR]<Date()+30,"MVR - " & [MVR],"")
So I would need to add "not isnull" in the criteria and then drop down one line and add <>""
Is that correct.
Sorry about your poker, i don't play that well.


----------



## dware2013 (Mar 1, 2013)

dware2013 said:


> Maybe I am not doing it right. I added a column and in the field i put MVROD: IIf([MVR]<Date()+30,"MVR - " & [MVR],"")
> So I would need to add "not isnull" in the criteria and then drop down one line and add <>""
> Is that correct.
> Sorry about your poker, i don't play that well.


Ok an update! I think that I have it now. Man you have been so much help... If I was close, I would buy to a staek dinner and a beer...
I ran that and it is just pulling in what I need. I think that I can build what I need off of this...
I have another query simular with equipment but they only had one date that expired not multiple...


----------



## OBP (Mar 8, 2005)

Well done.


----------



## dware2013 (Mar 1, 2013)

OBP said:


> Well done.


 I think that I will close this one as solved.
If I have anything else, now I know where to come. This has been a long year and a half.
Again, thanks...:up:

David


----------

