IIF Expression with Between Dates criteria

G

Guest

I have a user form in which you can pick different time frames. ie "In the
next 30 days"; "In the next 60 days"; "In the next 90 days", and then run the
query to return records that are scheduled in that time frame, etc.

This runs with no errors, however it doesn't return any results either. If
I change the criteria to just "Between Date() And Date()+30" it does show the
records that it's supposed to, but as soon as I add the
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30 days",Between Date()
And Date()+30), I get no results.

Also, if I change the field's criteria to this:
=============================
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30 days",Between
Date() And Date()+30)
=============================


Access automatically changes the criteria to this:
=============================
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+30)
=============================


Very confused as to why this is so. I just want to be able to choose a time
from from the dropdown in the user form, and have the query show results that
fit in that time frame. Any help is appreciated.




Here's what I have right now:
=========================================
SELECT Chart.[Status], Chart.[P#], Chart.TITLE, IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training Date])) AS [Next
Training Date], Format(IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training Date])),"yyyy mm
dd") AS SortOrder
FROM Chart
WHERE (((Chart.[Status])=4) AND ((IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training
Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+30) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 60
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+60) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 90
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+90) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 6
months",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+180)))
ORDER BY Format(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date])),"yyyy mm dd");
=========================================
 
K

Kevin Jameson

you cant do this with microsoft access 2003

you must upgrade to access 20004 by clicking here:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html

HTH

Kevin
Chris said:
I have a user form in which you can pick different time frames. ie "In the
next 30 days"; "In the next 60 days"; "In the next 90 days", and then run
the
query to return records that are scheduled in that time frame, etc.

This runs with no errors, however it doesn't return any results either.
If
I change the criteria to just "Between Date() And Date()+30" it does show
the
records that it's supposed to, but as soon as I add the
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30 days",Between
Date()
And Date()+30), I get no results.

Also, if I change the field's criteria to this:
=============================
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30 days",Between
Date() And Date()+30)
=============================


Access automatically changes the criteria to this:
=============================
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+30)
=============================


Very confused as to why this is so. I just want to be able to choose a
time
from from the dropdown in the user form, and have the query show results
that
fit in that time frame. Any help is appreciated.




Here's what I have right now:
=========================================
SELECT Chart.[Status], Chart.[P#], Chart.TITLE, IIf([Training Frequency]
Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training Date])) AS [Next
Training Date], Format(IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training Date])),"yyyy mm
dd") AS SortOrder
FROM Chart
WHERE (((Chart.[Status])=4) AND ((IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training
Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+30) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 60
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+60) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 90
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+90) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 6
months",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+180)))
ORDER BY Format(IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date])),"yyyy mm dd");
=========================================
Click to expand...
 
G

Guest

You can't??? Well drat... And actually I'm using '97.

Kevin Jameson said:
you cant do this with microsoft access 2003

you must upgrade to access 20004 by clicking here:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html

HTH

Kevin
Chris said:
I have a user form in which you can pick different time frames. ie "In the
next 30 days"; "In the next 60 days"; "In the next 90 days", and then run
the
query to return records that are scheduled in that time frame, etc.

This runs with no errors, however it doesn't return any results either.
If
I change the criteria to just "Between Date() And Date()+30" it does show
the
records that it's supposed to, but as soon as I add the
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30 days",Between
Date()
And Date()+30), I get no results.

Also, if I change the field's criteria to this:
=============================
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30 days",Between
Date() And Date()+30)
=============================


Access automatically changes the criteria to this:
=============================
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+30)
=============================


Very confused as to why this is so. I just want to be able to choose a
time
from from the dropdown in the user form, and have the query show results
that
fit in that time frame. Any help is appreciated.




Here's what I have right now:
=========================================
SELECT Chart.[Status], Chart.[P#], Chart.TITLE, IIf([Training Frequency]
Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training Date])) AS [Next
Training Date], Format(IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training Date])),"yyyy mm
dd") AS SortOrder
FROM Chart
WHERE (((Chart.[Status])=4) AND ((IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training
Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+30) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 60
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+60) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 90
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+90) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 6
months",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+180)))
ORDER BY Format(IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date])),"yyyy mm dd");
=========================================
Click to expand...
Click to expand...
 
G

Guest

Are you sure you can't do this? It doesn't seem like a difficult task or
criteria, I just can't get the Between Dates function it to work as the true
part of the IIf statement.


Kevin Jameson said:
you cant do this with microsoft access 2003

you must upgrade to access 20004 by clicking here:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html

HTH

Kevin
Chris said:
I have a user form in which you can pick different time frames. ie "In the
next 30 days"; "In the next 60 days"; "In the next 90 days", and then run
the
query to return records that are scheduled in that time frame, etc.

This runs with no errors, however it doesn't return any results either.
If
I change the criteria to just "Between Date() And Date()+30" it does show
the
records that it's supposed to, but as soon as I add the
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30 days",Between
Date()
And Date()+30), I get no results.

Also, if I change the field's criteria to this:
=============================
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30 days",Between
Date() And Date()+30)
=============================


Access automatically changes the criteria to this:
=============================
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+30)
=============================


Very confused as to why this is so. I just want to be able to choose a
time
from from the dropdown in the user form, and have the query show results
that
fit in that time frame. Any help is appreciated.




Here's what I have right now:
=========================================
SELECT Chart.[Status], Chart.[P#], Chart.TITLE, IIf([Training Frequency]
Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training Date])) AS [Next
Training Date], Format(IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training Date])),"yyyy mm
dd") AS SortOrder
FROM Chart
WHERE (((Chart.[Status])=4) AND ((IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training
Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+30) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 60
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+60) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 90
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+90) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 6
months",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+180)))
ORDER BY Format(IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date])),"yyyy mm dd");
=========================================
Click to expand...
Click to expand...
 
N

Neil Sunderland

Are you sure you can't do this? It doesn't seem like a difficult task or
criteria, I just can't get the Between Dates function it to work as the true
part of the IIf statement.
Click to expand...

Ignore it - it's just a rather tedious troll and/or spammer.
 
J

John Spencer

Try

Between Date() and DateAdd("d",Switch(Forms!frmTimeScheduled!CboTime = "In
the next 30 days",30, Forms!frmTimeScheduled!CboTime="In the next
60days",60 ),Date())

Better yet, change your combo box to have two columns, so that column one
contains the number of days and column two contains the text. Make sure the
bound column is the first column and then hide column 1 by making its width
zero. Then all you would need would be

Between Date() and DateAdd("d", Forms!frmTimeScheduled,CboTime,Date())


Chris said:
I have a user form in which you can pick different time frames. ie "In the
next 30 days"; "In the next 60 days"; "In the next 90 days", and then run
the
query to return records that are scheduled in that time frame, etc.

This runs with no errors, however it doesn't return any results either.
If
I change the criteria to just "Between Date() And Date()+30" it does show
the
records that it's supposed to, but as soon as I add the
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30 days",Between
Date()
And Date()+30), I get no results.

Also, if I change the field's criteria to this:
=============================
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30 days",Between
Date() And Date()+30)
=============================


Access automatically changes the criteria to this:
=============================
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+30)
=============================


Very confused as to why this is so. I just want to be able to choose a
time
from from the dropdown in the user form, and have the query show results
that
fit in that time frame. Any help is appreciated.




Here's what I have right now:
=========================================
SELECT Chart.[Status], Chart.[P#], Chart.TITLE, IIf([Training Frequency]
Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training Date])) AS [Next
Training Date], Format(IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training Date])),"yyyy mm
dd") AS SortOrder
FROM Chart
WHERE (((Chart.[Status])=4) AND ((IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training
Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+30) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 60
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+60) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 90
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+90) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 6
months",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+180)))
ORDER BY Format(IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date])),"yyyy mm dd");
=========================================
Click to expand...
 
G

Guest

I figured this out myself, and if anyone else has the problem, this is what I
changed

IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30 days",Between
Date() And Date()+30)

CHANGED TO

Between Date() and Date()+(IIf([Forms]![frmTimeScheduled]![cboTime]="In the
next 30 days",30)


Chris said:
I have a user form in which you can pick different time frames. ie "In the
next 30 days"; "In the next 60 days"; "In the next 90 days", and then run the
query to return records that are scheduled in that time frame, etc.

This runs with no errors, however it doesn't return any results either. If
I change the criteria to just "Between Date() And Date()+30" it does show the
records that it's supposed to, but as soon as I add the
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30 days",Between Date()
And Date()+30), I get no results.

Also, if I change the field's criteria to this:
=============================
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30 days",Between
Date() And Date()+30)
=============================


Access automatically changes the criteria to this:
=============================
IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+30)
=============================


Very confused as to why this is so. I just want to be able to choose a time
from from the dropdown in the user form, and have the query show results that
fit in that time frame. Any help is appreciated.




Here's what I have right now:
=========================================
SELECT Chart.[Status], Chart.[P#], Chart.TITLE, IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training Date])) AS [Next
Training Date], Format(IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training Date])),"yyyy mm
dd") AS SortOrder
FROM Chart
WHERE (((Chart.[Status])=4) AND ((IIf([Training Frequency] Is
Null,Null,DateAdd("m",[Training Frequency],[Last Training
Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 30
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+30) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 60
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+60) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 90
days",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+90) Or
(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training Frequency],[Last
Training Date])))=IIf([Forms]![frmTimeScheduled]![cboTime]="In the next 6
months",(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date]))) Between Date() And Date()+180)))
ORDER BY Format(IIf([Training Frequency] Is Null,Null,DateAdd("m",[Training
Frequency],[Last Training Date])),"yyyy mm dd");
=========================================
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query doesnt work after importing from 97 to 2000 4
IIF problem 8
"Query too complex" workaround 4
DateDiff returning incorrect years 11
Help with small result type 2
Another most-recent date query 0
Remove zero totals in query 4
Expression using Iif with dates 5

Top