Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-14-2015, 02:05 AM   #1
RoyD
Newly Registered User
 
Join Date: Aug 2015
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
RoyD is on a distinguished road
Problems with IIF and dates as query criteria

Problems using IIF with Dates as criteria in a query
I am trying to use a IIF command in a query with date functions but can’t get it to work.
I am trying to limit a list of Suppliers to a choice of all or the last 3 months.
I have the following 2 parts working when used alone as criteria.
IIF ([Forms]![frmSuppliersTransMain]![status] = “full”,#1/7/2015#,#1/6/2015#)
Between date() and DateAdd(“m”,-3,date())
But if I try to combine them I don’t get any records or any errors.
IIF ([Forms]![frmSuppliersTransMain]![status] = “full”, Between date() and DateAdd(“m”,-3,date())
, Between date() and DateAdd(“m”,-90,date())
In fact I don’t get any records when using and any date function such as <date()
I am sure there is not much wrong

If anyone can help I would appreciate it.

RoyD is offline   Reply With Quote
Old 08-14-2015, 06:15 AM   #2
AlexN
Newly Registered User
 
AlexN's Avatar
 
Join Date: Nov 2014
Location: Athens, Greece
Posts: 223
Thanks: 121
Thanked 14 Times in 14 Posts
AlexN is on a distinguished road
Re: Problems with IIF and dates as query criteria

I've been struggling with an issue like this , all day long. This is what I've to finally come to a solution.
  • Completely Deleted Query.
  • Compact and Repair.
  • Re-created query with the least possible fields.
  • Compact and Repair.
It finally worked.

Make sure fields on which you apply the criteria are really Dates.


HTH
AlexN is offline   Reply With Quote
Old 08-14-2015, 09:38 PM   #3
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Problems with IIF and dates as query criteria

Quote:
Originally Posted by RoyD View Post
Problems using IIF with Dates as criteria in a query
I am trying to use a IIF command in a query with date functions but can’t get it to work.
I am trying to limit a list of Suppliers to a choice of all or the last 3 months.
I have the following 2 parts working when used alone as criteria.
IIF ([Forms]![frmSuppliersTransMain]![status] = “full”,#1/7/2015#,#1/6/2015#)
Between date() and DateAdd(“m”,-3,date())
But if I try to combine them I don’t get any records or any errors.
IIF ([Forms]![frmSuppliersTransMain]![status] = “full”, Between date() and DateAdd(“m”,-3,date())
, Between date() and DateAdd(“m”,-90,date())
In fact I don’t get any records when using and any date function such as <date()
I am sure there is not much wrong

If anyone can help I would appreciate it.
It is a mess, could you show exactly what you've in the Iif statement?

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 08-14-2015, 10:15 PM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,581
Thanks: 68
Thanked 2,754 Times in 2,638 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Problems with IIF and dates as query criteria

you need to supply your date field on your query, ie:
IIF ([Forms]![frmSuppliersTransMain]![status] = 'full' AND [Forms]![frmSuppliersTransMain]![yourDateControl] Between #" & Format(DateAdd("m",-3,Date) ,"mm\/dd\/yyyy") & "# AND #" & Format(Date, "mm\/dd\/yyyy") & "#
arnelgp is offline   Reply With Quote
Old 08-15-2015, 06:08 AM   #5
RoyD
Newly Registered User
 
Join Date: Aug 2015
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
RoyD is on a distinguished road
Re: Problems with IIF and dates as query criteria

Hi
Thanks for every ones support and help.
AlexN Thanks I did a compact and repair and had some success as I now have errors reported which was not the case.
I have extracted the table form and query to a new database to allow more experimentation and to remove any interference form other areas of the database.

JHB
I hope a clearer description.
I have a Suppliers table "tblSuppliers"
a sub table for details and deliveries "tblSupplierSub"
I have a query that extracts a suppliers deliveries but want to limit the list to the last 3 months.
I am trying to get a working solution by using a query and criteria in the "DateF" field

I have tied the following but with no working output.

Attempt 1 IIF ([Forms]![frmSuppliersTransMain]![status] = “full”, Between date() and DateAdd(“m”,-3,date())
, Between date() and DateAdd(“m”,-90,date())

Attempt 2 IIf([forms]![frmSuppliersTransMain]![Status]="Full",([tblSupplierSub].[dateF]) Between Date() And DateAdd("m",-3,Date()),([tblSupplierSub].[dateF])<Date())

Attempt 3
IIF ([Forms]![frmSuppliersTransMain]![status] = 'high' AND [tblSuppliersub]![dateF] Between #" & Format(DateAdd("m",-3,Date,"ddmmyyyy") & "# AND #" & Format(Date, "ddmmyyyy") & "#
Which gives a date format error for the section in red.

I hope the above is understandable.
Any other solution would be appreciated
RoyD is offline   Reply With Quote
Old 08-15-2015, 06:18 AM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,581
Thanks: 68
Thanked 2,754 Times in 2,638 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Problems with IIF and dates as query criteria

even if your "[tblSuppliersub]![dateF] " field is in the format "mmddyyyy",
ms access (on all version) understand only english(american) date format in the form:
"mm/dd/yyyy".
arnelgp is offline   Reply With Quote
Old 08-15-2015, 08:37 PM   #7
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Problems with IIF and dates as query criteria

If you still not have got it working post a stripped down version of your database with some sample data + the name of the query, (zip it).

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 08-16-2015, 01:56 AM   #8
RoyD
Newly Registered User
 
Join Date: Aug 2015
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
RoyD is on a distinguished road
Re: Problems with IIF and dates as query criteria

Quote:
Originally Posted by JHB View Post
If you still not have got it working post a stripped down version of your database with some sample data + the name of the query, (zip it).
Thanks for your help.
As I can't get it to work I will take you up on your kind offer.
I will extract the relevant parts and send them.
Thanks
RoyD is offline   Reply With Quote
Old 08-16-2015, 08:59 AM   #9
RoyD
Newly Registered User
 
Join Date: Aug 2015
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
RoyD is on a distinguished road
Thumbs up Re: Problems with IIF and dates as query criteria

Quote:
Originally Posted by JHB View Post
If you still not have got it working post a stripped down version of your database with some sample data + the name of the query, (zip it).

Hi JHB
I have the scaled down Db attached.
Open form frmSuppliersTransSub
Click button on top left "Full details"
The sub form uses query qrySuppliersSub
Which is the one I am having problems with.

Thanks for your help
Attached Files
File Type: accdb testdb2015.accdb (1.00 MB, 40 views)
RoyD is offline   Reply With Quote
Old 08-16-2015, 09:27 PM   #10
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Problems with IIF and dates as query criteria

Try it now.
Attached Files
File Type: zip testdb2015.zip (222.0 KB, 84 views)
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 08-17-2015, 01:52 AM   #11
RoyD
Newly Registered User
 
Join Date: Aug 2015
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
RoyD is on a distinguished road
Thumbs up Re: Problems with IIF and dates as query criteria

Quote:
Originally Posted by JHB View Post
Try it now.
Thanks for all your help JHB that works great, this problem is now solved.
I can now get back to the real world after going around in circles.

Just to summarise what I had wrong for anyone else looking.
The code needed is IIf([Forms]![frmSuppliersTransMain]![Status]="full",[tblSupplierSub].[dateF] Between Date() And DateAdd("m",-3,Date()),[tblSupplierSub].[dateF] Between Date() And DateAdd("m",-90,Date()))

But I had it in the criteria section.
I notice you have it in the "Field" section, I think that was what others were trying to tell me.
In the criteria you have <> False

RoyD is offline   Reply With Quote
Reply

Tags
date , iif

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
between And Dates in Query Criteria rkrause Queries 16 05-17-2011 10:49 AM
Problems with query criteria benkingery Queries 1 11-02-2010 05:50 AM
Query criteria using dates Toolman Queries 1 10-11-2009 08:01 AM
Criteria problems in query HorstSnap Queries 7 01-22-2009 05:41 AM
Problems with query grouping dates Triona* Queries 1 01-18-2005 06:43 AM




All times are GMT -8. The time now is 06:01 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World