Need help with this IIF expression

[Scott]

Greetings, I am trying to get the following expression to work and it
currently does not. Here is my logic: I have a report called invoice that
I want to say ie: "1 Night Stay in Cousin Amy at $149 per night beginning
7/20/04" if a 1 night stay and "5 Nights Stay in Cousin Amy at $149 per
night beginning 7/20/04" if more than 1 night. With fields: [StayLength] &
"Night Stay in " & [Rooms] & " at $" & [Rate] & " per Night Beginning" & "
" & [StayStart] (this currently works. I want to try and get it to have a
plural nights for 2 or more nights and singular night for 1 night. The
following is what I have so far, but it does not work. I get a syntax
error.

IIF([StayLength] = 1 , ([StayLength] & "Night Stay in " & [Rooms] & " at $"
& [Rate] & " per Night Beginning" & " " & [StayStart]), & ([StayLength] & "
Nights Stay in " & [Rooms] & " at $" & [Rate] & " per Night Beginning" & "
" & [StayStart]))

Thanks in advance,
Scott B

 

[Scott]

You've got an unnecessary ampersand after the comma for the False part of
your IIf statement.

Try:

IIF([StayLength] = 1 , "1 Night Stay in " & [Rooms] & " at $"
& [Rate] & " per Night Beginning" & " " & [StayStart], [StayLength] & "
Nights Stay in " & [Rooms] & " at $" & [Rate] & " per Night Beginning" & "
" & [StayStart])

Doug,

Here is the exact error message I just got when i made the chjages to the
expression (ain't Access fustrating?) word for word with all the error
message punctuation. I did not add or substract anything. Any thoughts?

Syntax error (comma) in query expression '[IIF([StayLength = 1 ,
[StayLength]& " Night Stay in " &[Rooms]& " at $" &[Rate] & " per Night
Beginning" & " " &[StayStart], [StayLength]& " Nights Stay in " &[Rooms]& "
at $" &[Rate]& " per Night Beginning" & " " &[StayStart])]'.

Best regards,
Scott

 

[Douglas J. Steele]

How exactly are you using the IIf statement? Is it supposed to be the source
for a control? If so, you need an equal sign in front of it.

Another possibility is what country have you selected in Windows Regional
Settings? Sometimes you need to use semicolons rather than commas.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You've got an unnecessary ampersand after the comma for the False part of
your IIf statement.

Try:

IIF([StayLength] = 1 , "1 Night Stay in " & [Rooms] & " at $"
& [Rate] & " per Night Beginning" & " " & [StayStart], [StayLength] & "
Nights Stay in " & [Rooms] & " at $" & [Rate] & " per Night Beginning" & "
" & [StayStart])

Doug,

Here is the exact error message I just got when i made the chjages to the
expression (ain't Access fustrating?) word for word with all the error
message punctuation. I did not add or substract anything. Any thoughts?

Syntax error (comma) in query expression '[IIF([StayLength = 1 ,
[StayLength]& " Night Stay in " &[Rooms]& " at $" &[Rate] & " per Night
Beginning" & " " &[StayStart], [StayLength]& " Nights Stay in " &[Rooms]& "
at $" &[Rate]& " per Night Beginning" & " " &[StayStart])]'.

Best regards,
Scott

 

[Scott]

How exactly are you using the IIf statement? Is it supposed to be the source
for a control? If so, you need an equal sign in front of it.

Another possibility is what country have you selected in Windows Regional
Settings? Sometimes you need to use semicolons rather than commas.

Douglas,

The = did it. I had taken it out as part of the process of trying to get
it to work. I had negelcted to put it back. That and your change of the
ampersand did it.

Many thanks,
Scott