Related Topics

How To Query Past Due Dates In Access 2007

How to subtract when you have a null field?
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?


Expression Typed Incorrectly or Too Complex - Query Critieria IIF
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


Limiting Due dates to the current month (when the data is queried)
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.


Combining results from several queries
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?


Date Past Function
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


Check for the Month only in a Date
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
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.
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
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
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”.


2 Queries on one Date Field
I want my report to have a Carry Forward field and I need my query to add up the values related to the creation dates column and if the date is before say today, then add up those values that correspond with the creation dates and put in a field called carry forward and if those dates are past today, then leave them itemized on the report and summarized and totaledonly those values with a creation date past today.

So the report will show all of the dates past a certain date and totaled. And in the header section a box called Carry Forward and the summarized amount will represent all the values fromprior dates in the Creation Dates field.

Can a parameter query be used so the user can key in the date they want the data to be since that date and therefore, anything before that be summarized in the Carry Forward field on the report. I think I have to use the criteria portion of my query?


Query input dates for range of dates in two fields
I seem to be stuck on an issue with taking dates as input and querying a range of dates between two fields.

I am currently making a database of contract data. Each contract has an effective date (field StarDate) and an ending date (field EndDate). What I would like to do is eventually create aform-based query to input a range of two dates, and the query would return all contracts that were effective between those two dates.

For instance, if I would like search all contracts valid in 2007, I would input 1/1/2007 and 12/31/2007 into the form fields, and the query would return contracts signed between 1/1/2007-12/31/2007 and contracts ending between 1/1/2007-12/31/2007.

I've seen a lot of solutions for taking input of two dates and searching a single date field with BETWEEN queries, and I've also seen solutions for taking a single date input and searchinga range of dates with IIF statements, but nothing to query two input dates vs two date fields.


Filtering records by date
Yea I'm trying to filter a table that has specific due dates, but I want to run a query that will show the results 7 days before the due date and not show all the records just the records that have the due date 7 days prior to the due date. Also wondering if anyone knows how to make it where the query could send an email notifier to people when a record hits the 7 day prior to the due date mark?


If else: automatically change the status based on due date
I have a calibration database at work that I use to keep track of the calibration for tools in the warehouse, and the fields consist of ID number, Status(Active, Inactive), Due Date, Last Date, etc.

I want to be able to set the Status field to be able to change it to inactive if the Due Date exceeds the current date. IE If the due date is 4/4/2012, and the current date goes past that (4/5/2012 -), I would like the status to change to inactive.

We have an active query, and an inactive query, but right now we are having to go back in and change the status manually to inactive when we know its past due, but we have over 100 items to keep track of, and it would just be easier if it did it automatically.

So is there a way I can do "IF Date < current date, make active. Else, inactive (or IF Date > current date, make inactive).


Query input dates for range of dates in two fields
this is my first post. I seem to be stuck on an issue with taking dates as input and querying a range of dates between two fields.

I am currently making a database of contract data. Each contract has an effective date (field StarDate) and an ending date (field EndDate). What I would like to do is eventually create a form-based query to input a range of two dates, and the query would return all contracts that were effective between those two dates.

For instance, if I would like search all contracts valid in 2007, I would input 1/1/2007 and 12/31/2007 into the form fields, and the query would return contracts signed between 1/1/2007-12/31/2007 and contracts ending between 1/1/2007-12/31/2007.

I've seen a lot of solutions for taking input of two dates and searching a single date field with BETWEEN queries, and I've also seen solutions for taking a single date input and searching a range of dates with IIF statements, but nothing to query two input dates vs two date fields


Filtered reports on base of due documents
Of the many columns in the main table, I have one which lists the dates when new documents were received from the client. Now our firm then classifies these documents on date priority and processes them by due dates which can range from 5 days to 15 days.

However there are certain documents which need to be processed on the same day when it was received. I have another columnin the table which lists all the due dates.

What I would like to get is a report with:

1. number of same day due documents (I.e. how many records in the due date column match with a particular date in the documents receipt date column)

2. sum of the number of above mentioned documents on a weekly basis

3. for a given date range

I have been trying everything I can think of but this seems a bit tough, at least for me.


Late fees based on due date
I am having trouble coming up with a query that will assess late fees based on a due date. Payment is due the 1st of every month, if after 10 days payment is not received then a 15.00 late fee is charged. CAn any one help me out, not really good with dates and it needs to be in a query so I can run the query in vb.net


How to calculate a leap year and using julian date. Access 2007
I have two date fields. One "Date Sent" the other "Next Due Date". I've been asked to just take out the "Next Due Date" field so user's don't have to type it and just basically add a text box that will display the date equal to "Date Sent" + 3years - 1day. However, I began to think. what about leap years? I've found some information on Julian dates, but I'm not sure really how use it to get my result. I'm currently using Access 2007.


Multiple Queries and Reports
I have a Form (access 2003) that can Input Dates into, From these dates I have a Cross tab Query that returns the Number of hours a employee works per day and it sums it.

I also have a table with employee, job, event date, plus a few other fields, Also a query that draws from the same dates in FORM1 for events.

I am unable to include employee Hours with a report for events. Included is the Database thou most fields wont work due a lot of the data base is drawn from a separate Program.


Can I change a FONT based on condition?
I have two different date fields. One for the date due, and one for the received date.

What I want to do is this.

If the Received Date is past the Due Date, I want the received date to be RED.

I don't need it to do this in the table, just in the report, which is based on a query. Can I put a criteria formula in the Query for that field?