Problems with IIF and dates as query criteria (1 Viewer)

RoyD

New member
Local time
Today, 08:34
Joined
Aug 14, 2015
Messages
6
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.
 

AlexN

Registered User.
Local time
Today, 17:34
Joined
Nov 10, 2014
Messages
223
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
 

JHB

Have been here a while
Local time
Today, 16:34
Joined
Jun 17, 2012
Messages
7,720
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?
 

arnelgp

error reading drive A:
Local time
Today, 23:34
Joined
May 7, 2009
Messages
8,716
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") & "#
 

RoyD

New member
Local time
Today, 08:34
Joined
Aug 14, 2015
Messages
6
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
 

arnelgp

error reading drive A:
Local time
Today, 23:34
Joined
May 7, 2009
Messages
8,716
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".
 

JHB

Have been here a while
Local time
Today, 16:34
Joined
Jun 17, 2012
Messages
7,720
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).
 

RoyD

New member
Local time
Today, 08:34
Joined
Aug 14, 2015
Messages
6
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

New member
Local time
Today, 08:34
Joined
Aug 14, 2015
Messages
6
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
 

Attachments

RoyD

New member
Local time
Today, 08:34
Joined
Aug 14, 2015
Messages
6
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
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom