 |
| Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
November 7th, 2005, 11:39 AM
|
|
Friend of Wrox
|
|
Join Date: Sep 2005
Location: , , USA.
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
IIF Statement is a Date field
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",")
|
November 7th, 2005, 02:07 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
|
|
Past Due: Iif(Date() > DateAdd("d",[Terms],[Invoice Due Date]), "Past Due", Null)
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
November 7th, 2005, 03:09 PM
|
|
Friend of Wrox
|
|
Join Date: Sep 2005
Location: , , USA.
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
It's only giving me an error message?
|
November 7th, 2005, 03:22 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
|
|
What is the error message?
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
November 7th, 2005, 03:38 PM
|
|
Friend of Wrox
|
|
Join Date: Sep 2005
Location: , , USA.
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
#Name?
|
November 7th, 2005, 03:48 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
|
|
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
|
November 7th, 2005, 04:47 PM
|
|
Friend of Wrox
|
|
Join Date: Sep 2005
Location: , , USA.
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I tried this formula
=IIf(Date()>([InvoiceDueDate]+[Terms]),"Past Due","")
But it doesn’t populate all the ones that should be.
|
November 7th, 2005, 05:05 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
|
|
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
|
November 7th, 2005, 05:22 PM
|
|
Friend of Wrox
|
|
Join Date: Sep 2005
Location: , , USA.
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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","")
|
November 7th, 2005, 05:34 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
|
|
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
|
|
Similar Threads
