Topics Search

How To Query Past Due Dates In Access 2007

How To Query Past Due Dates In Access 2007
Views: 3
I have a db that we have data stored for things like expiration dates on certain things. What we like to do is make a query so that anything that has expired prior to today's date will show up in the query.

example of the fields:
MVRDate
CDLDate
HMDate
ARDue
Sponsored Links:

More topics

 

How to subtract when you have a null field?

Preview
I have this expression built: date()-date past due. that way I can get the number of days past due. It works but date past due is null if the customer is current so that field comes back empty.

Is there a way I can make the results be zero if the date past due is null?
 

Limiting Due dates to the current month (when the data is queried)

Preview
In a calibration database I have a query that calculates due dates. I'd like to limit the query to just the calibrations due in the current month. How do I modify this formula to make only the current month's due dates show up?

Expr1: Format$(([MaxOfDateDone] [FreqDays]),"m y")

MaxofDateDone is a query that only looks at the last calibration done. Frequency Days is the number of days that a piece of equipment goes before it needs another calibration.
 

Expression Typed Incorrectly or Too Complex - Query Critieria IIF

Preview
Just wanted to say that this forum has been incredibly useful for multiple access projects I've been involved in so thanks for you help and I'm confident that you'll be able to solve my issue.

I'm having a problem with the following IIF function as query criteria. I'm referencing an option group on a form to determine what date of records to return. There are 4 options, Past Due, Past Due plus 30 days into the future, Past Due plust 60 days into the future, and All Dates. The criteria is as follows:

IIf([Forms]![Punch List Report Filter]![Past_Due]=1,"<Date()",IIf([Forms]![Punch List Report Filter]![Past_Due_Plus_30]=1,"<=Date()+30",IIf([Forms]![Punch List Report Filter]![Past_Due_Plus_60]=1,"<=Date()+60","*")))

Each time I run the query I get the error message "This expression is typed incorrectly, or it is too complex to be evaluated
 

Date Past Function

Preview
Is there some type of function I can use in Access to do the following?

One field has dates in it.
When these days are past due I need to know so I can check on the data.

I tried to make another field in my query to calculate this [FIELDNAMEHERE] > Date().

Do I need to create another field that has the current date in it and then make another field to say one is greater than the other or less than the the other or something? I really just need to know when the date entered into this field has passed so I can escalate on the issue
 

Combining results from several queries

Preview
I am using Access 2007. I have 4 queries I would like to combine. They are pulled from same table. They have the same fields: Bank, Days past due and Balance. There are 4 different values for Bank 30,68,69, and 98.For each Bank I need the total number of accounts for 15-29 days, 30 to 59 days, 60 to 89 days, and 90 plus past due.

I also need the total of the balances for each period.

I am doing this with 4 queries right now. I would like to do it with one. How would I do that?
 

Check for the Month only in a Date

Preview
table 1 has a column with request dates

table 2 has a column with due dates

a request date must be before the due date

im writing a query for the two tables, but how do I just check the month?

ex: if request date's month = 01/11/2012 in table 1, I want it to scroll through all the dates in table 2 where it finds a due date also in january. (there will not be multiple due dates in the same month)

can someone help with putting this in the Design View of the query screen
 

Update date in three different fields based on date in another field

Preview
I've got three fields with three different dates, all based upon one or the other for when reports should be turned in where.

What I want to do, is have it set up so that whenever the report is due, the dates will be automatically entered, and updated if the due date changes.

I know that in Access 2003, you could use the SetValue in a Macro, but I don't believe (but haven't seen anything that works the same) that option exists in 2007, and I believe that creating the code in VBA would probably be "easier."

If there's another topic dealing with this, I must've not been searching using the appropriate keywords, and for that I apologize.

Again, I'm using Access 2007.
 

Automatically show previous dates.

Preview
I am creating an Access 2007 datadase. I have a field labeled "Current date" where I would like to manually input a date. I have also created 10 Past date fields labeled "Past 1" through "Past 10". I would like these dates to automatically show the 10 most recent dates that I have entered in the "Current Date" field respectively. How do I make this happen? All fields are a Date/Time with a short date format
 

Dates/Due Dates

Preview
I'm not even sure how to ask this.I am trying to track what is due that has not been done and if it is not done, there is probably no data for it.

Clients have a fiscal year end, for example 12/31 (could be 6/30, 4/30, etc.)
During that year they have 4 quarterly reports that are due 45 days after quarter end (with the exception of one client which is due 40 days after quarter end). I can set up a field to indicate that if necessary.

How can I design a table/query/report that will track when something is due for each client that hasn't been completed yet.

Do I need to manually create a record for each quarter's due date for each client or can I calculate this somehow?

Essentially I need a record for each due date. Currently am filling it in when it is done and manually looking to see when the next one is due. Can I have it do some sort of automatic creation of record for next due date when one is completed
 

Past Due Invoices by payment terms

Preview
The query I have below. Is returning records with the Payment Terms for NET 30 are showing as “15-30 Days Past Due”. The NET 30 payment terms should show as “31-60 Days Past Due” and the NET 15 payment terms should show as “15-30 Days Past Due”.