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

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

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

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.

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

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)

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

Thanks - AnderJ86