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

[AnderJ86]

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

[ChipR]

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.

[AnderJ86]

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)

[ChipR]

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

[Stewart Ross]

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

[AnderJ86]

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

[Stewart Ross]

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

[AnderJ86]

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.

[Stewart Ross]

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

[AnderJ86]

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

Thanks - AnderJ86