|
Subject:
|
IIF Statement is a Date field
|
|
Posted By:
|
Corey
|
Post Date:
|
11/7/2005 10:39:22 AM
|
I’m trying to calculate a field that shows a message that if the Invoice Due Date plus the number of days for the Terms
So if the Invoice Due date is “11/5/05" and the Terms are "30"
It would show the message “Past Due” starting on the day of 12/5/05 and on.
Below is what I thought would work.
Past Due: IIf([Invoice Due Date]="> ([Invoice Due Date]+[Terms]) "Past Due",")
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
11/7/2005 1:07:49 PM
|
Past Due: Iif(Date() > DateAdd("d",[Terms],[Invoice Due Date]), "Past Due", Null)
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
Corey
|
Reply Date:
|
11/7/2005 2:09:35 PM
|
It's only giving me an error message?
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
11/7/2005 2:22:18 PM
|
What is the error message?
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
Corey
|
Reply Date:
|
11/7/2005 2:38:27 PM
|
#Name?
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
11/7/2005 2:48:08 PM
|
Given the code:
Past Due: Iif(Date() > DateAdd("d",[Terms],[Invoice Due Date]), "Past Due", Null)
The #Name Error means that either [Terms] does not exist, or [Invoice Due Date] does not exist, or even the functions Date() or DateAdd() do not exist. Because you didn't get a pop-up box asking you to enter term or invoice due date, I'm guessing that one or both of the two functions, Date() or DateAdd() didn't work. That means you're missing a reference library.
Open any VBA code and click on TOOLS > REFERENCES in the menu. Look for any libraries that say "MISSING!" and try to find them (or something close) in the list under them and click them.
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
Corey
|
Reply Date:
|
11/7/2005 3:47:42 PM
|
I tried this formula
=IIf(Date()>([InvoiceDueDate]+[Terms]),"Past Due","")
But it doesn’t populate all the ones that should be.
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
11/7/2005 4:05:51 PM
|
Can you give us a short list of data? Include what you're getting and what you WANT to get as a comparison. Don't forget column headings that reflect the actual name of the field (including calculated ones like Past Due).
What happens when there IS no Invoice Due Date and/or Terms value?
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
Corey
|
Reply Date:
|
11/7/2005 4:22:32 PM
|
Buyer Name:Tom (What it should look like)
Vendor: SENTRY INDUSTRIES
PO#: 862
Cost: $13.00
Expected Date: 5/11/2005
Terms: 30
InvoiceDueDate: 6/10/2005
Notes: SAVED
System Notes: Past Due
With Formulas
Buyer Name: Tom
Vendor: SENTRY INDUSTRIES
PO#: 862
Cost: $13.00
Expected Date: 5/11/2005
Terms: 30
InvoiceDueDate: Expected Date+Terms
Notes: Text Message
System Notes =IIf(Date()>([InvoiceDueDate]+[Terms]),"Past Due","")
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
11/7/2005 4:34:13 PM
|
Make these two changes.
InvoiceDueDate: DateAdd("d", [Terms], [Expected Date])
System Notes: IIf(Date() > [InvoiceDueDate], "Past Due", Null)
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
Corey
|
Reply Date:
|
11/7/2005 4:57:45 PM
|
InvoiceDueDate: DateAdd("d", [Terms], [Expected Date])
but the System Notes isn't working?
|
|
Reply By:
|
Corey
|
Reply Date:
|
11/7/2005 5:01:47 PM
|
Sorry - InvoiceDueDate: DateAdd("d", [Terms], [Expected Date])WORKS
but:System Notes isn't working
|
|
Reply By:
|
crapanz
|
Reply Date:
|
11/7/2005 8:34:31 PM
|
The DateAdd should work, this is good advice.
His comments assume that [Terms] is a Variable for Each Record.
Therefore, this explains why you got #Name before, probably.
You can just use 30 in the [Terms]
If [Terms] is a field, then is it an integer.
Is this on a Form or A Report.
If on a Form, did you use =IIF( ) in the Control Source?
N.B. Its always good idea to not name your fields with spaces, and include the field type in the name of the table field.
Database Agreements
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
11/8/2005 7:41:13 AM
|
What exactly is System Notes giving you?
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
Corey
|
Reply Date:
|
11/8/2005 8:38:04 AM
|
It shows a error message “#Error” and then after I enter the Terms, the “#Error” disapers and just showed a blank field.
I changed the formula you gave me to: =IIf(Date()>[InvoiceDueDate],"Past Due","Not Past Due")
And now it works but still shows the “#Error” in System Notes & Due Date prior to entering the Terms. Is there a way it can just be blank?
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
11/8/2005 8:46:42 AM
|
What do you mean by "entering the terms"? Do you mean that there is no field in the table called "Terms" and that the query is prompting you to enter it on the fly?
If so, that is the problem. The function cannot work without a value for "Terms". You need a field for "Terms" in your table filled with values ahead of time.
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
Corey
|
Reply Date:
|
11/8/2005 9:01:06 AM
|
Can I e-mail you a sample of the data?
|
|
Reply By:
|
Figgis
|
Reply Date:
|
11/12/2005 5:50:19 PM
|
Corey why not try someting like this
=IIf(Me.Terms.Value=Null,"Terms not set",IIF(Date()>[InvoiceDueDate],"Past Due","Not Past Due"))
|
