IIF update query with Between Criteria

[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"))))

 

[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

 

[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.

 

[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.

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

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"))))

 

[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!

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

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"))))

--
HTH

Dale Fye

Message posted via AccessMonster.com

 

[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!

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!

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

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"))))

--
HTH

Dale Fye

Message posted via AccessMonster.com