IIF Statement 2

[Jhtexas]

Can anyone look at the IIF statment and tell me whats wrong with the syntax?


SELECT Employees.EmpID, Employees.LastName, Employees.FirstName, Employees.State,
IIf (Employees.State = "YES",[Lastname] & " " & [Firstname] AS [Full Name], IIF (Employees.State = "NO" [lastname]))
FROM Employees;

Thanks in advance!
Jim

 

[SkipVought]

hi,

IIf (Employees.State = "YES",[Lastname] & "  " & [Firstname], [lastname]))

don't know about the As...

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[jbpez]

Try:

SELECT Employees.EmpID, Employees.LastName, Employees.FirstName, Employees.State,
IIf ([Employees].[State] = 'YES',[Lastname] & ' ' & [Firstname],IIF ([Employees].[State] = 'NO', [lastname])) AS [Full Name]
FROM Employees;

John Borges

 

[PHV]

IIf([State]="YES",[Lastname] & " " & [Firstname],IIf(State="NO",[lastname],"?")) AS [Full Name]


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jbpez]

Ooops sorry should be,

SELECT Employees.EmpID, Employees.LastName, Employees.FirstName, Employees.State,
IIf ([Employees].[State] = 'YES',[Lastname] & ' ' & [Firstname],[Lastname]) AS [Full Name]
FROM Employees;

John Borges

 

[Jhtexas]

When I applied both examples above to my test database it works just fine. When I applied the syntax to the real DB I get an #error when running the query for the "Full Title" Field. Can you help me understand what changes in the example below? It does format correctly without the new IIF statements.
Thanks again,
Jim

SELECT IIF ([Ride Title & Descriptions].[Paid Ride] ="Yes", Format([Ride Listings].[Ride Date],"mmmm dd") & Chr(9) & "--$$ " & [Ride Listings].[Ride Title] & " $$--" & Chr(9) & Format([Ride Listings].[Start Time],"h:nn am/pm"), IIF ([Ride Title & Descriptions].[Paid Ride] ="No", Format([Ride Listings].[Ride Date],"mmmm dd") & Chr(9) & "-- " & [Ride Listings].[Ride Title] & " --" & Chr(9) & Format([Ride Listings].[Start Time],"h:nn am/pm"))) AS [Full Title], [Ride Title & Descriptions.Ride Description] & Chr(9) & [Ride Starters & Contacts.First Name] & " " & Right([Ride Starters & Contacts.Phone 1],8) & Chr(13) AS [Descrip-Name]
FROM [Ride Title & Descriptions] INNER JOIN ([Ride Starters & Contacts] INNER JOIN [Ride Listings] ON [Ride Starters & Contacts].[Starter ID]=[Ride Listings].[Ride Starter ID]) ON [Ride Title & Descriptions].[Ride Title]=[Ride Listings].[Ride Title]
WHERE ((([Ride Listings.Ride Date])>=[Enter From Date ie "2/1/2005"]))
ORDER BY [Ride Listings].[Ride Date], [Ride Listings].[Start Time];

 

[PHV]

SELECT IIf([Ride Title & Descriptions].[Paid Ride]="Yes",
Format([Ride Listings].[Ride Date],"mmmm dd") & Chr(9) & "--$$ " & [Ride Listings].[Ride Title] & " $$--" & Chr(9) & Format([Ride Listings].[Start Time],"h:nn am/pm"),
Format([Ride Listings].[Ride Date],"mmmm dd") & Chr(9) & "-- " & [Ride Listings].[Ride Title] & " --" & Chr(9) & Format([Ride Listings].[Start Time],"h:nn am/pm")
) AS [Full Title],

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jhtexas]

PH,
I tried that format also before I reposted, but it also returns and error. The field is setup for Yes/No. Any other ideas?
I really appreciate your help,
Jim

 

[PHV]

SELECT IIf([Ride Title & Descriptions].[Paid Ride]=Yes,
Format([Ride Listings].[Ride Date],"mmmm dd") & Chr(9) & "--$$ " & [Ride Listings].[Ride Title] & " $$--" & Chr(9) & Format([Ride Listings].[Start Time],"h:nn am/pm"),
Format([Ride Listings].[Ride Date],"mmmm dd") & Chr(9) & "-- " & [Ride Listings].[Ride Title] & " --" & Chr(9) & Format([Ride Listings].[Start Time],"h:nn am/pm")
) AS [Full Title],

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jhtexas]

PH,
So the quotes around the "Yes" are not required when it's a Yes/No field. Good eye!

Thanks for your help,
Jim