Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
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
  #1 (permalink)  
Old 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
Send a message via Yahoo to Corey
Default 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",")


  #2 (permalink)  
Old November 7th, 2005, 02:07 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Past Due: Iif(Date() > DateAdd("d",[Terms],[Invoice Due Date]), "Past Due", Null)


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
  #3 (permalink)  
Old 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
Send a message via Yahoo to Corey
Default

It's only giving me an error message?

  #4 (permalink)  
Old November 7th, 2005, 03:22 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

What is the error message?

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
  #5 (permalink)  
Old 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
Send a message via Yahoo to Corey
Default

#Name?

  #6 (permalink)  
Old November 7th, 2005, 03:48 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
  #7 (permalink)  
Old 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
Send a message via Yahoo to Corey
Default

I tried this formula

=IIf(Date()>([InvoiceDueDate]+[Terms]),"Past Due","")
But it doesn’t populate all the ones that should be.


  #8 (permalink)  
Old November 7th, 2005, 05:05 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
  #9 (permalink)  
Old 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
Send a message via Yahoo to Corey
Default

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","")




  #10 (permalink)  
Old November 7th, 2005, 05:34 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
Thread Thread Starter Forum Replies Last Post
Can I make this a IIF Statement Corey Access VBA 1 November 3rd, 2006 08:40 AM
Iif statement in Access rangeview Access 7 March 28th, 2006 01:14 PM
IIF Statement golden Access 2 August 3rd, 2004 01:32 AM
IIF Statement golden Access 3 July 12th, 2004 07:49 PM
IIF Statement Question fastcorvette Access 8 October 31st, 2003 11:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.