IIF update query with Between Criteria

  • Thread starter Thread starter Tom Glatt
  • Start date Start date
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"))))
 
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
 
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.
 
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...
 
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...

--
HTH

Dale Fye

Message posted via AccessMonster.com
Click to expand...
 
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...

--
HTH

Dale Fye

Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
Back
Top