IIF update query with Between Criteria

T

Tom Glatt

I created an update query (posted below) that I thought was constructed
properly. However, it seems to only update according to the first criteria
and the last criteria. In other words, it either updates my "Demographic"
field with Silent or Next and ignores the remaining criteria. I am sure if
has something to do with the between criteria, but I am at a loss. I am not
much of a query designer. Any assistance would be appreciated.

Query:

UPDATE Results SET Results.Demographic = IIf(Results.[Custom Data] Between
"1/1/1926" And "12/31/1945", "Silent",IIf(Results.[Custom Data] Between
"1/1/1946" And "12/31/1964", "Boomer",IIf(Results.[Custom Data] Between
"1/31/1965" And "12/31/1984", "GenX",IIf(Results.[Custom Data] Between
"1/31/1985" And "12/31/2002", "GenX","Next"))))
 
V

vanderghast

Custom Data field is hopefully a DATE_TIME value, not a string. If it is a
string, then each character in the data is read textually, NOT AS A DATE.


Furthermore, your DATE constants should be delimited by a #, not by a double
quote. Try:

UPDATE Results SET Results.Demographic = IIf(Results.[Custom Data] Between
#1/1/1926# And #12/31/1945#, "Silent",IIf(Results.[Custom Data] Between
#/1/1/1946# And #12/31/1964#, "Boomer",IIf(Results.[Custom Data] Between
#1/31/1965# And #12/31/1984#, "GenX",IIf(Results.[Custom Data] Between
#1/31/1985# And #12/31/2002#, "GenX","Next"))))




Vanderghast, Access MVP
 
K

KARL DEWEY

Is Results.[Custom Data] a text field or a DateTime field?

Your IIF statement has quotes around the dates as if it is a text field.

If it is a Datetime field drop the quotes. If a text field it ain't gonna
work until you convert the text to dates.
 
T

Tom Glatt

I really appreciate the quick reply. The Custom Data field is indeed a
Date_Time field so we are good there. However, when I made the change as
suggested - using the # delimiter - Access presents a syntax error message.
Not sure why.

vanderghast said:
Custom Data field is hopefully a DATE_TIME value, not a string. If it is a
string, then each character in the data is read textually, NOT AS A DATE.


Furthermore, your DATE constants should be delimited by a #, not by a double
quote. Try:

UPDATE Results SET Results.Demographic = IIf(Results.[Custom Data] Between
#1/1/1926# And #12/31/1945#, "Silent",IIf(Results.[Custom Data] Between
#/1/1/1946# And #12/31/1964#, "Boomer",IIf(Results.[Custom Data] Between
#1/31/1965# And #12/31/1984#, "GenX",IIf(Results.[Custom Data] Between
#1/31/1985# And #12/31/2002#, "GenX","Next"))))




Vanderghast, Access MVP


Tom Glatt said:
I created an update query (posted below) that I thought was constructed
properly. However, it seems to only update according to the first criteria
and the last criteria. In other words, it either updates my "Demographic"
field with Silent or Next and ignores the remaining criteria. I am sure if
has something to do with the between criteria, but I am at a loss. I am
not
much of a query designer. Any assistance would be appreciated.

Query:

UPDATE Results SET Results.Demographic = IIf(Results.[Custom Data] Between
"1/1/1926" And "12/31/1945", "Silent",IIf(Results.[Custom Data] Between
"1/1/1946" And "12/31/1964", "Boomer",IIf(Results.[Custom Data] Between
"1/31/1965" And "12/31/1984", "GenX",IIf(Results.[Custom Data] Between
"1/31/1985" And "12/31/2002", "GenX","Next"))))
Click to expand...
Click to expand...
 
D

Dale_Fye via AccessMonster.com

Also, you have two GenX values, I think you mean GenY in the second instance.

Personally, I prefer to use the Switch() function over multiple nested iif( )
functions. It evaluates the expresstions until it finds an expression that
evaluates to TRUE, so it works a lot like an If/ElseIf/EndIf construct.
Since it operates in this way you don't have to check for between, as
indicated below

UPDATE Results
SET Results.Demographic = SWITCH(Results.[Custom Data] < #1/1/1926#,
"Ancient",
Results.[Custom Data] < #1/1/1946#, "Silent",
Results.[Custom Data] < #1/1/1965#, "Boomer",
Results.[Custom Data] < #1/1/1985#, "GenX",
Results.[Custom Data] < #1/1/2003#, "GenY",
TRUE, "Next")

HTH
Dale
Tom said:
I created an update query (posted below) that I thought was constructed
properly. However, it seems to only update according to the first criteria
and the last criteria. In other words, it either updates my "Demographic"
field with Silent or Next and ignores the remaining criteria. I am sure if
has something to do with the between criteria, but I am at a loss. I am not
much of a query designer. Any assistance would be appreciated.

Query:

UPDATE Results SET Results.Demographic = IIf(Results.[Custom Data] Between
"1/1/1926" And "12/31/1945", "Silent",IIf(Results.[Custom Data] Between
"1/1/1946" And "12/31/1964", "Boomer",IIf(Results.[Custom Data] Between
"1/31/1965" And "12/31/1984", "GenX",IIf(Results.[Custom Data] Between
"1/31/1985" And "12/31/2002", "GenX","Next"))))
Click to expand...
 
T

Tom Glatt

Thanks for the replies, everyone. Much appreciated. I reviewed it all again
and corrected a few problems of my own making. This worked perfectly:

UPDATE Results SET Results.Demographic =
IIf(Results.[Custom Data] Between #1/1/1926# And #12/31/1945#, "Silent",
IIf(Results.[Custom Data] Between #1/1/1946# And #12/31/1964#, "Boomer",
IIf(Results.[Custom Data] Between #1/1/1965# And #12/31/1984#, "GenX",
IIf(Results.[Custom Data] Between #1/1/1985# And #12/31/2002#,
"GenY","Next"))))

Thanks again!

Dale_Fye via AccessMonster.com said:
Also, you have two GenX values, I think you mean GenY in the second instance.

Personally, I prefer to use the Switch() function over multiple nested iif( )
functions. It evaluates the expresstions until it finds an expression that
evaluates to TRUE, so it works a lot like an If/ElseIf/EndIf construct.
Since it operates in this way you don't have to check for between, as
indicated below

UPDATE Results
SET Results.Demographic = SWITCH(Results.[Custom Data] < #1/1/1926#,
"Ancient",
Results.[Custom Data] < #1/1/1946#, "Silent",
Results.[Custom Data] < #1/1/1965#, "Boomer",
Results.[Custom Data] < #1/1/1985#, "GenX",
Results.[Custom Data] < #1/1/2003#, "GenY",
TRUE, "Next")

HTH
Dale
Tom said:
I created an update query (posted below) that I thought was constructed
properly. However, it seems to only update according to the first criteria
and the last criteria. In other words, it either updates my "Demographic"
field with Silent or Next and ignores the remaining criteria. I am sure if
has something to do with the between criteria, but I am at a loss. I am not
much of a query designer. Any assistance would be appreciated.

Query:

UPDATE Results SET Results.Demographic = IIf(Results.[Custom Data] Between
"1/1/1926" And "12/31/1945", "Silent",IIf(Results.[Custom Data] Between
"1/1/1946" And "12/31/1964", "Boomer",IIf(Results.[Custom Data] Between
"1/31/1965" And "12/31/1984", "GenX",IIf(Results.[Custom Data] Between
"1/31/1985" And "12/31/2002", "GenX","Next"))))
Click to expand...
Click to expand...
 
T

Tom Glatt

Dale,

I like your approach also. I tried it with another version of the table and
it worked perfectly. It seems to be easier to manage, as you suggested.

Thanks!

Tom Glatt said:
Thanks for the replies, everyone. Much appreciated. I reviewed it all again
and corrected a few problems of my own making. This worked perfectly:

UPDATE Results SET Results.Demographic =
IIf(Results.[Custom Data] Between #1/1/1926# And #12/31/1945#, "Silent",
IIf(Results.[Custom Data] Between #1/1/1946# And #12/31/1964#, "Boomer",
IIf(Results.[Custom Data] Between #1/1/1965# And #12/31/1984#, "GenX",
IIf(Results.[Custom Data] Between #1/1/1985# And #12/31/2002#,
"GenY","Next"))))

Thanks again!

Dale_Fye via AccessMonster.com said:
Also, you have two GenX values, I think you mean GenY in the second instance.

Personally, I prefer to use the Switch() function over multiple nested iif( )
functions. It evaluates the expresstions until it finds an expression that
evaluates to TRUE, so it works a lot like an If/ElseIf/EndIf construct.
Since it operates in this way you don't have to check for between, as
indicated below

UPDATE Results
SET Results.Demographic = SWITCH(Results.[Custom Data] < #1/1/1926#,
"Ancient",
Results.[Custom Data] < #1/1/1946#, "Silent",
Results.[Custom Data] < #1/1/1965#, "Boomer",
Results.[Custom Data] < #1/1/1985#, "GenX",
Results.[Custom Data] < #1/1/2003#, "GenY",
TRUE, "Next")

HTH
Dale
Tom said:
I created an update query (posted below) that I thought was constructed
properly. However, it seems to only update according to the first criteria
and the last criteria. In other words, it either updates my "Demographic"
field with Silent or Next and ignores the remaining criteria. I am sure if
has something to do with the between criteria, but I am at a loss. I am not
much of a query designer. Any assistance would be appreciated.

Query:

UPDATE Results SET Results.Demographic = IIf(Results.[Custom Data] Between
"1/1/1926" And "12/31/1945", "Silent",IIf(Results.[Custom Data] Between
"1/1/1946" And "12/31/1964", "Boomer",IIf(Results.[Custom Data] Between
"1/31/1965" And "12/31/1984", "GenX",IIf(Results.[Custom Data] Between
"1/31/1985" And "12/31/2002", "GenX","Next"))))
Click to expand...
Click to expand...
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

iif and between 2
IIF statement in query criteria, help! 3
Formatting date gives bad results in query 2
Perameter Query Criteria Box opens twice 1
Update query with date criteria 3
DateDiff with variable criteria? 5
IIf Statement in Query Criteria 1
Help with small result type 2

Top