Report that lists date fields that have expired

[gpnhmiller]

I have a table that has multiple date fields and a name field.
Name, Date1, Date2, Date3, Date4.....

I need a report by name that tells me which dates are going to expire within 45 days. I would like the report to print the date filed that is expiring followed by text that says "New Date_____/_____/_____" so they can print the report, go get the new dates write them on the report, then come back to the databse and enter all of the new dates at one.

Example:
Name1: Date1 is expiring, (Enter new date_____/____/_____, Date 4 is expiring, (Enter new date_____/_____/_____)

Name2: Date1 is expiring, (Enter new date_____/____/_____, Date 2 is expiring, (Enter new Date_____/_____/_____)

Etc...

I am not sure if I do this in a query or in the report...

Help is greatly appreciated.

Greg

 

[vbaInet]

What you need is a query that will list those dates that expire within the next 45 days. You need two functions for this, i.e. Date() and DateDiff(). You will use them as criteria under your date field. See here for more:

http://www.techonthenet.com/access/functions/index_alpha.php

Then you need a form that's based on the query above which users can use to enter the new dates.

 

[gpnhmiller]

Thanks for the reply.
I already have a query set up based on the criteria of "between Now() and now() +45". I get the following data:

UserDate1Date2Date3Date4Date5Name11/2/20139/1/20121/2/20139/1/20129/1/2012Name21/2/20131/2/20139/1/20121/2/20131/2/2013Name31/2/20131/2/20139/1/20121/2/20131/2/2013Name49/1/20121/2/20139/1/20121/2/20139/1/2012

But I need a report by user name that only lists the dates that are due to expire not all 6 dates. I also want to add the following text after each date so they can print the report and write in the new dates on the paper: (New Date___/___/___)

Example:

User1, Date 2 is expiring (New date___/__/___), Date 4 is expiring (New Date___/___/___), Date 5 is expiring (New Date___/___/___)
User 2, Date 3 is expiring (New Date___/___/___)
User 3, Date 3 is expiring (New Date___/___/___)
User 4, Date 1 is expiring (New Date___/___/___), Date 3 is expiring (New Date___/___/___), Date 5 is expiring (New Date___/___/___)

Thanks, Greg

 

[vbaInet]

You should change Now() to Date(). Now() includes a Time element which you don't need in your code.

Can you draw up what you expect to see in an Excel spreadsheet? And also show me some raw data (in Excel or as a screenshot).

 

[gpnhmiller]

Sorry, the spreadsheet did not import, this is what the query result looks like:

 

[vbaInet]

Ok, but I need to see the original query result, and then also what you would like to see. I can't visualise any of that from what you wrote in your other post.

Welcome to the forum by the way.

 

[gpnhmiller]

thanks, I am still trying to ficure this site out... Did you see the picture I uploaded?

 

[gpnhmiller]

If you can see the picture I uploaded, all I want is a report by user name that only shows me the date fields in yellow.

Thanks

 

[vbaInet]

I've seen the image but that didn't help. I need to see the two reports mentioned in my previous posts.

Here's the site's FAQs. It will explain how to upload files to the site:

http://www.access-programmers.co.uk/forums/forumdisplay.php?f=23

 

[gpnhmiller]

Sorry for not replying earlier, I have been away.

I think I am making this sound more complicated than it is.
1. I have a table that tracks 5 date fields that expire on that date.
2. I have a query that shows me dates that are going to expire in 45 days for all users.
3. I want to create report by user name that has two columns.
Column one is the field name of the date that is going to expire and column 2 is the date the field expires.
I uploaded a spreadsheet for a visual if you need it.

Thanks for your ptients..

Greg

 

[gpnhmiller]

forgot to upload

 

[vbaInet]

Greg,

The spreadsheet didn't attach. Remember to zip it and when you upload it wait for it until it shows it has uploaded.

3. I want to create report by user name that has two columns.

You have the query so create the report based on the query and in your report right-click, select Sorting & Grouping and add a group for your name field.

Column one is the field name of the date that is going to expire and column 2 is the date the field expires.

Have you created a query that shows the field name for expired dates?

 

[gpnhmiller]

When I group it, the reports shows me all of the dates, not just the dates that expired, because in the query date2 may only be expiring for User1 and not user5 but is shows up for all users. Not sure if I am making since....

 

[gpnhmiller]

My attackment show up just above your last post.

 

[vbaInet]

Let me see the SQL statement of your query.

 

[gpnhmiller]

SELECT Clients.Client, Clients.Date1, Clients.Date2, Clients.Date3, Clients.Date4, Clients.Date5
FROM Clients
WHERE (((Clients.Date1) Between Now() And Now()+45)) OR (((Clients.Date2) Between Now() And Now()+45)) OR (((Clients.Date3) Between Now() And Now()+45)) OR (((Clients.Date4) Between Now() And Now()+45)) OR (((Clients.Date5) Between Now() And Now()+45));

 

[gpnhmiller]

And I want the report to look like this:
User Name: BOB
Date Expiring Date
Date3 10/1/12
Date4 9/11/12

User Name: Sam
Date Expiring Date
Date2 9/11/12
Date5 9/12/12

 

[gpnhmiller]

I was able to have the query pull in data for fileds that are between date() and Date =45 with this query.

SELECT Clients.Client, IIf(([date1]between Date() and Date()+45),[date1],"") AS [Date 1], IIf(([date2]between Date() and Date()+45),[date2],"") AS [Date 2], IIf(([date3]between Date() and Date()+45),[date3],"") AS [Date 3], IIf(([date4]between Date() and Date()+45),[date4],"") AS [Date 4], IIf(([date5]between Date() and Date()+45),[date5],"") AS [Date 5]
FROM Clients;

But I still dont know how to build a report to only show me the dates that are expiring for each client in column format.

 

[gpnhmiller]

here is a skinned down version of the DB with 2 queries.
1. Expiring
2. Expired

The clinet report is what I would like to see.

The yellow area is just additional text I would like on the report so they can print the report and make notes.

Thanks!

Greg

 

[vbaInet]

I've seen them all. There's not much I can do to help you at this point unfortunately, because your table is not normalised. There's no such "Transpose" function in Access because it's a database and as such requires that tables are designed properly.

The report you're trying to create somehow represents how one of your tables should actually look like (with the exception of the ExpiringDate calculation).

I would advise that you look into normalisation. Here are some good links:

http://support.microsoft.com/kb/283878
http://r937.com/relational.html