By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,315 Members | 1,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,315 IT Pros & Developers. It's quick & easy.

iif Concatenation of set day/month with year from another field

P: 5
In an access query I am trying to concatenate the year from one field onto a set day/month.

Here is where I'm at:

ExpirationDate: IIf([expire-dt]>#08/31/2009#,("8/31/" & [xyear]),8/31/2009)

The ("8/31/" & [xyear]) part should result like 8/31/2014 as an example. [xyear] is a four digit year.

I'll apreciate any help.

Thanks - AnderJ86


Access 2002 SP3
Jul 6 '09 #1
Share this Question
Share on Google+
9 Replies


Expert 100+
P: 1,287
You don't mention what problem you are having, but the third argument, 8/31/2009, is going to be treated as a number and division applied unless you use quotes. Parentheses around the second argument are not necessary.
Jul 6 '09 #2

P: 5
Thanks ChipR I tried:

ExpirationDate:ExpirationDate: IIf([expire-dt]>#08/31/2009#,"8/31/" & [xyear],"8/31/2009")

It errored with " The expression you entered contains invalid syntax)
Jul 6 '09 #3

Expert 100+
P: 1,287
Is your actual code all on one line, or on two lines? I can't see any other problems (assuming the ExpirationDate twice was a copy & paste issue).
Jul 6 '09 #4

Expert Mod 2.5K+
P: 2,545
Ahh, easy for you to miss that you've repeated the name of the field twice, which Chip refers to:

ExpirationDate:ExpirationDate: IIf([expire-dt]>#08/31/2009#,"8/31/" & [xyear],"8/31/2009")

hence the error. Should be

ExpirationDate: IIf([expire-dt]>#08/31/2009#,"8/31/" & [xyear],"8/31/2009")

The syntax of the line is otherwise correct.

-S
Jul 6 '09 #5

P: 5
Thanks ChipR I tried:

ExpirationDate: IIf([expire-dt]>#08/31/2009#,"8/31/" & [xyear],"8/31/2009")

It errored with " The expression you entered contains invalid syntax)

It is all on one line. The two ExpirationDate's was a typo. If the syntax is correct I will just keep tweeking it until it works. - Thanks
Jul 6 '09 #6

Expert Mod 2.5K+
P: 2,545
There may be an issue with returning the date literal as a string in double quotes. It may look like a date, but it is not of the same type.

Date literals are referred to like this: #8/31/2009#

As the True clause in your IIF builds a date using the month and day as a literal followed by a year from another field, use the DateSerial function to build the date instead of using a date literal:

IIF([expire-dt]>#08/31/2009#, DateSerial([xyear], 8, 31), #08/31/2009#)

-S
Jul 6 '09 #7

P: 5
The error this time said "you tried to execute a query that does not include the specified expresion ExpirationDate: IIf([expire-dt]>#08/31/2009#,DateSerial([xyear], 8, 31),#08/31/2009#) as part of the aggreget function."

I also tried changing the way [xyear] was created first I used Year[expire-dt]-365 then I changed it to xyear: DatePart("yyyy",[expire-dt]-365) both returned the correct yyyy but I don't know what data type it is.
Jul 6 '09 #8

Expert Mod 2.5K+
P: 2,545
You'll need to repeat the IIF statement in the GROUP BY clause of your SQL statement. The simplest way to do so is to use the Access query editor, select the View, Totals option, and select Group By for the total of the computed IIF field.

It is not correct to hard-code 365 days into your year calculations - leap years will not be dealt with correctly. In any event there is no need to do so. The Year function can give you the year component of any date, but without knowing more about what you are trying to achieve I can't be certain about what you should be doing. Please advise.

-S
Jul 6 '09 #9

P: 5
I appreciate all the help but I am putting this aside for now.

Thanks - AnderJ86
Jul 9 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.