ElseIF Statements Help

J

jbeling

Registered User.
Local time
Today, 07:02
Joined
Nov 28, 2008
Messages
28
Please help be build this condition in a query:

=IIF (Exp Date>Date()+30) Then StatusID=1
ElseIf (Exp Date<= Date()+30) Then StatusID=2
ElseIf (Exp Date<Date()) Then StatusID=3
Else StatusID=4
End If

If "Exp Date" is > Todays date + 30 days I want "StatusID" to = 1

If <= to todays date + 30 days then = 2
If < todays date then = 3
If not any of those than = 4

ANy Suggesstions?
 
does it tell you where the code breaks down or give you an error message?

also, you haven't stated whether this is in a query or in VBA (just noticed: i don't think you can use "End If" with "=IIF"... and i'm not sure your paretheses are correct either... try pressing F1 on the ElseIf, as i'm not sure how to use these excatly...)

just taking a stab - try this:

Code: 
'code if in VBA
[COLOR=Red]IF ([[/COLOR]Exp Date[COLOR=Red]][/COLOR]>Date()+30) Then StatusID=[COLOR=Red]"[/COLOR]1[COLOR=Red]"[/COLOR]
ElseIf ([COLOR=Red][[/COLOR]Exp Date[COLOR=Red]][/COLOR]<= Date()+30) Then StatusID=[COLOR=Red]"[/COLOR]2[COLOR=Red]"[/COLOR]
ElseIf ([COLOR=Red][[/COLOR]Exp Date[COLOR=Red]][/COLOR]<Date()) Then StatusID=[COLOR=Red]"[/COLOR]3[COLOR=Red]"[/COLOR]
Else StatusID=[COLOR=Red]"[/COLOR]4[COLOR=Red]"[/COLOR]
End If
Code: 
'expression if in query
=I[COLOR=Red]IF (([[/COLOR]Exp Date[COLOR=Red]][/COLOR]>Date()+30)[COLOR=Red],[/COLOR] StatusID=[COLOR=Red]"[/COLOR]1[COLOR=Red]", IIF[/COLOR] [COLOR=Red]([/COLOR]([COLOR=Red][[/COLOR]Exp Date[COLOR=Red]][/COLOR]<= Date()+30)[COLOR=Red],[/COLOR] StatusID=[COLOR=Red]"[/COLOR]2[COLOR=Red]"), IIF ([/COLOR]([COLOR=Red][[/COLOR]Exp Date[COLOR=Red]][/COLOR]<Date())[COLOR=Red],[/COLOR] StatusID=[COLOR=Red]"[/COLOR]3[COLOR=Red]"),[/COLOR] StatusID=[COLOR=Red]"[/COLOR]4[COLOR=Red]"[/COLOR])[COLOR=Red])[/COLOR]
i don't have your data set, so i can't check if this works (i'm basing the IIF logic on what i know in Excel's IF statement, so i'm not sure if Access follows the same logic). let us know how this goes.
 
Last edited:
Well, Im not sure if you can have multiple multiple "else" for one "if". Looks like you should have nested iif statements. Also, lines 2 and 3 say "elseif", I think you meant "else".
 
Code: 
IF ([Exp Date]>Date()+30) Then 
StatusID="1"
Else If ([Exp Date]<= Date()+30) Then 
StatusID="2"
Else If ([Exp Date]<Date()) Then 
StatusID="3"
Else StatusID="4"
End If


Oh, sorry, it is nested!

I believe your problem is that you have multiple "if", but only one "end if".

Try this
Code: 
IF ([Exp Date]>Date()+30) Then 
StatusID="1"
Else If ([Exp Date]<= Date()+30) Then 
StatusID="2"
Else If ([Exp Date]<Date()) Then 
StatusID="3"
Else StatusID="4"
End If
End If
End If

If you still have problems, try different variations of the lines that set the status id.

perhaps
me.statusid="x"
me.statusid.value="x"
set me.statusid.value="x"

If your new to this kind of thing, try different things that look like they should work. Thats how I do it!
 
I hate these ...

Code: 
IIf([Exp Date] > Date() + 30, 1, IIf([Exp Date] <= Date() + 30, 2, IIf([Exp Date] < Date(), 3, 4)))

And ... That doesn't work, the 2nd clause will override the third clause all the time.


I'd rather use a VBA function, much more readable.

Wayne
 
speakers_86 said:
I believe your problem is that you have multiple "if", but only one "end if".

Try this
Code: 
IF ([Exp Date]>Date()+30) Then 
StatusID="1"
Else If ([Exp Date]<= Date()+30) Then 
StatusID="2"
Else If ([Exp Date]<Date()) Then 
StatusID="3"
Else StatusID="4"
End If
End If
End If
Click to expand...

i just checked access help file. the "ElseIf" is an accepted keyword, which allows just one IF...END IF clause (and multiple "ELSEIF"'s in between without their own "END IF"'s)

Code: 
'from Access developer reference search:

You can have as many [B]ElseIf[/B] clauses as you want in a block [B]If[/B], but none can appear after an [B]Else[/B] clause.
Code: 
'from Access help:

Public Sub CheckCommandLine()      
' Check the value returned by Command function and display     
' the appropriate form.     

If [B]Command[/B] = "Orders" Then         
        DoCmd.OpenForm "Orders" 
    ElseIf [B]Command[/B] = "Employees" Then         
        DoCmd.OpenForm "Employees" 
    Else         
        Exit Sub     
End If  

End Sub
 
wik,

That's why I'd move the logic from a query to a VBA function.

In VBA you can use Case statements and the ElseIf construct.

In a Query, you're stuck with the IIf and I can't even attempt to make the
original logic work with Nested IIfs.

Wayne
 
WayneRyan said:
In a Query, you're stuck with the IIf and I can't even attempt to make the original logic work with Nested IIfs.

Wayne
Click to expand...

hey wayne,

yeah, the logic isn't logical to me either (<= date + 30) vs (< date).... i'm wondering if jbeling means:

Code: 
IF ([exp date] + 30 <= Date())...
.... but then, i'm not focussed right now, and it's not very well explained in the first instance (what jbeling wants from this expression), so we can't even make suggestions.
 
Hey guys, thanks for the feedback. Basically what I am looking for is a field (StatusID) in my query (query1) that populates one of 4 numbers (1, 2, 3, 4) based on the date in another field (Exp Date) in the same query. So, IF the date in the field "Exp Date" is more than 30 days from the current date at any time then I want "StatusID" to be 1. If it is equal to or within 30 days from the current date I want "StatusID" to be 2. If the date in "Exp Date" is before the current date i want "StatusID" to be 3, and finally if "Exp Date" is blank I want StatusID to be a 4.

I am not familar at all with doing this so putting it in laymens terms or a step by step helps me understand what to do. So what would be the best, and easiest (remember i am a newb) way to make this work?

Thank you again for the responses, I will attempt what I can from above :)
 
So where exactly am I suppose to enter an expression for a query? I thought it was in the criteria part of the design view for that field, but that does not seem to be working.
 
wiklendt said:
does it tell you where the code breaks down or give you an error message?

also, you haven't stated whether this is in a query or in VBA (just noticed: i don't think you can use "End If" with "=IIF"... and i'm not sure your paretheses are correct either... try pressing F1 on the ElseIf, as i'm not sure how to use these excatly...)

just taking a stab - try this:

Code: 
'code if in VBA
[COLOR=red]IF ([[/COLOR]Exp Date[COLOR=red]][/COLOR]>Date()+30) Then StatusID=[COLOR=red]"[/COLOR]1[COLOR=red]"[/COLOR]
ElseIf ([COLOR=red][[/COLOR]Exp Date[COLOR=red]][/COLOR]<= Date()+30) Then StatusID=[COLOR=red]"[/COLOR]2[COLOR=red]"[/COLOR]
ElseIf ([COLOR=red][[/COLOR]Exp Date[COLOR=red]][/COLOR]<Date()) Then StatusID=[COLOR=red]"[/COLOR]3[COLOR=red]"[/COLOR]
Else StatusID=[COLOR=red]"[/COLOR]4[COLOR=red]"[/COLOR]
End If
Code: 
'expression if in query
=I[COLOR=red]IF (([[/COLOR]Exp Date[COLOR=red]][/COLOR]>Date()+30)[COLOR=red],[/COLOR] StatusID=[COLOR=red]"[/COLOR]1[COLOR=red]", IIF[/COLOR] [COLOR=red]([/COLOR]([COLOR=red][[/COLOR]Exp Date[COLOR=red]][/COLOR]<= Date()+30)[COLOR=red],[/COLOR] StatusID=[COLOR=red]"[/COLOR]2[COLOR=red]"), IIF ([/COLOR]([COLOR=red][[/COLOR]Exp Date[COLOR=red]][/COLOR]<Date())[COLOR=red],[/COLOR] StatusID=[COLOR=red]"[/COLOR]3[COLOR=red]"),[/COLOR] StatusID=[COLOR=red]"[/COLOR]4[COLOR=red]"[/COLOR])[COLOR=red])[/COLOR]
i don't have your data set, so i can't check if this works (i'm basing the IIF logic on what i know in Excel's IF statement, so i'm not sure if Access follows the same logic). let us know how this goes.
Click to expand...

The code for an espression if in a query is not working. It says the function i have entered has the wrong number of arguments.
 
jbeling said:
Hey guys, thanks for the feedback. Basically what I am looking for is a field (StatusID) in my query (query1) that populates one of 4 numbers (1, 2, 3, 4) based on the date in another field (Exp Date) in the same query. So, IF the date in the field "Exp Date" is more than 30 days from the current date at any time then I want "StatusID" to be 1. If it is equal to or within 30 days from the current date I want "StatusID" to be 2. If the date in "Exp Date" is before the current date i want "StatusID" to be 3, and finally if "Exp Date" is blank I want StatusID to be a 4.

I am not familar at all with doing this so putting it in laymens terms or a step by step helps me understand what to do. So what would be the best, and easiest (remember i am a newb) way to make this work?

Thank you again for the responses, I will attempt what I can from above :)
Click to expand...

Just to clarify... For Status ID #'s 1 & 2 the date is in the future. For 3 it is in the past. So if today is 12/15/08 and the date in the Exp Date field is 12/25/08 it would be a 2 in StatusID b/c it is within 30 days of todays date, but is also in the future. If the date was 12/14/08 in the Exp Date field it would be a 3 b/c its in the past.
 
Hi -

You might want to consider the Switch() function.

Code: 
StatusID = Switch([Exp Date]>date()+30, "1", [Exp Date] between date() and date()+ 30, "2", [Exp Date]< date(), "3", True, "4")

Bob
 
raskew said:
Hi -

You might want to consider the Switch() function.

Code: 
StatusID = Switch([Exp Date]>date()+30, "1", [Exp Date] between date() and date()+ 30, "2", [Exp Date]< date(), "3", True, "4")

Bob
Click to expand...
I did this and my query comes up blank when I open it. Also, when I open the query back up in design more there is a new column called "StatusID" with the quotes around it. It is in this column that the expression was moved to. The orginal column for StatusID stays unchanged. :(
 
Try creating an update query. In design view place the Switch statement in the Update To cell of the StatusID column.

If that doesn't work for you, please post your query SQL.

Bob
 
raskew said:
Try creating an update query.
Click to expand...
:eek:

before you do anything, i hope you're keeping backups!! i always make a copy of my db file before i do any sort of major work on it and after i have successfully completed something complex/difficult/hair-pulling.

(and i rename the copy something like: "EQdb 2008-12-14.01 - changed contacts to MM.mdb", where my 'real' database file is called "EQdb.mdb", the ".01" in the file name ensures that my backups display in the same order as i made them, and i know which change was done when (i may have several backups per day))

having said that, if you post your db here on the forum, we may be able to help a bit more. (when you're posting, scroll down and click "manage attachments", then browse and attach a zip of your db - be sure it's less than 785.9 KB).

there is always a fixable reason that queries come up blank when you expect them to have data. once we see what your db, perhaps the solution has been staring us in the face the whole time!
 
Hi -

After backing-up your database, copy/paste this to a new query:

Code: 
UPDATE tbl_Expire SET tbl_Expire.StatusID = Switch([Exp Date]>Date()+30,"1",[Exp Date] Between Date() 
AND Date()+30,"2",[Exp Date]<Date(),"3",True,"4");

HTH - Bob
 
raskew said:
Hi -

After backing-up your database, copy/paste this to a new query:

Code: 
UPDATE tbl_Expire SET tbl_Expire.StatusID = Switch([Exp Date]>Date()+30,"1",[Exp Date] Between Date() 
AND Date()+30,"2",[Exp Date]<Date(),"3",True,"4");

HTH - Bob
Click to expand...

Where do i paste that into? If a make a new query, where do i paste it in the query? Do I have to call the query anything special? When in design view do I delect a field, table, or enter in any criteria? Is this the criteria?
 
Where do i paste that into?
Click to expand...

Open a new query, go to SQL View and copy/paste it there.

If a make a new query, where do i paste it in the query?
Click to expand...

See above.

Do I have to call the query anything special?
Click to expand...

No, you can call it whatever you desire.

When in design view do I delect a field, table, or enter in any criteria? Is this the criteria?
Click to expand...

Once you've copied/pasted into SQL view, switch to Design view. You'll see the fields and the criteria as though you'd entered them in Design view.

After running the query, open tbl_Expire and you'll see that StatusID has been updated.

Bob
 
You must log in or register to reply here.

Similar threads

klsblues
VBA Conditional Formatting error because I have more than 3 formatting requirements.
2 3
Replies
53
Views
2,204
AHeyne
A
C
limit date formula as variable
Replies
16
Views
742
MajP
MajP
H
Solved Case Statement to get value in Access Query
Replies
15
Views
1,546
Pat Hartman
P
V
Form formula help sought
Replies
3
Views
392
Vincew
V
D
Double Click cell inserts Today's Date
Replies
8
Views
512
Isaac
Isaac

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom