iif question

[kidrobot]

I have an IIF field 'Inbound : IIf([Dwell Start Event]="RMFC",Yes,No)' that outputs -1 or 0 depending if the value is Yes or No. How can I make is so the result will say Y if it's -1 and N if it's 0? I tried changing the Yes and No to Y and N, but it didn't work.

 

[namliam]

Just make it return "Y" or "N"
IIf([Dwell Start Event]="RMFC","Y","N")

On another note... You shouldnt use spaces in column/tablenames ... or actually anywhere.

 

[kidrobot]

Namliam, Changing it to Y or N did not work. When I run the query it brings up a prompt with Y then N, tell me to type something in. Here is my query...

SELECT [Q01 Generate Data with Duplicates].[Report Dt], [Q01 Generate Data with Duplicates].Controller, [Q01 Generate Data with Duplicates].[Eq Init], [Q01 Generate Data with Duplicates].[Equip Nr], [Q01 Generate Data with Duplicates].[Dwell Start Dt], [Q01 Generate Data with Duplicates].[Dwell Start Tm], [Q01 Generate Data with Duplicates].[Dwell Start Event], [Q01 Generate Data with Duplicates].[Dwell Days], [Q01 Generate Data with Duplicates].Terminal, [Q01 Generate Data with Duplicates].[Wb Origin], [Q01 Generate Data with Duplicates].[Wb Destination], [Q01 Generate Data with Duplicates].[Wb Route], 
IIf([outbound billing?] Or [inbound within freedays?],False,True) AS [CY Unit?], 
IIf((Not IsNull([Wb Origin])) And [Wb Origin]<>[T OLDDATA]!Terminal And [Wb Destination]<>[T OLDDATA]!Terminal,True,False) AS [Outbound Billing?], 
IIf([Dwell Days]<[Free Days] And [Dwell Start Event]="RMFC",True,False) AS [Inbound Within Freedays?]

FROM [T ALLOTMENT] INNER JOIN [Q01 Generate Data with Duplicates] ON [T ALLOTMENT].Terminal = [Q01 Generate Data with Duplicates].Terminal

WHERE ((([Q01 Generate Data with Duplicates].[Report Dt]) Between CDate([Start Date]) And CDate([End Date])))

ORDER BY [Q01 Generate Data with Duplicates].[Report Dt], [Q01 Generate Data with Duplicates].Terminal;

Also, the query field names are out of my control as they are from an automated program that I get in an e-mail. Plus I didn't create this database. Thanks though, I always try to keep my naming conventions in control in my own databases.

 

[Brianwarnock]

IIf([Dwell Days]<[Free Days] And [Dwell Start Event]="RMFC","Y","N") AS [Inbound Within Freedays?]

should display a Y or N as the mailman said.

Brian

 

[kidrobot]

Brian, I changed my query to this

SELECT [Q01 Generate Data with Duplicates].[Report Dt], [Q01 Generate Data with Duplicates].Controller, [Q01 Generate Data with Duplicates].[Eq Init], [Q01 Generate Data with Duplicates].[Equip Nr], [Q01 Generate Data with Duplicates].[Dwell Start Dt], [Q01 Generate Data with Duplicates].[Dwell Start Tm], [Q01 Generate Data with Duplicates].[Dwell Start Event], [Q01 Generate Data with Duplicates].[Dwell Days], [Q01 Generate Data with Duplicates].Terminal, [Q01 Generate Data with Duplicates].[Wb Origin], [Q01 Generate Data with Duplicates].[Wb Destination], [Q01 Generate Data with Duplicates].[Wb Route], 
IIf([outbound billing?] Or [inbound within freedays?],N,Y) AS [CY Unit?], 
IIf((Not IsNull([Wb Origin])) And [Wb Origin]<>[T OLDDATA]!Terminal And [Wb Destination]<>[T OLDDATA]!Terminal,Y,N) AS [Outbound Billing?], 
IIf([Dwell Days]<[Free Days] And [Dwell Start Event]="RMFC",Y,N) AS [Inbound Within Freedays?]

FROM [T ALLOTMENT] INNER JOIN [Q01 Generate Data with Duplicates] ON [T ALLOTMENT].Terminal = [Q01 Generate Data with Duplicates].Terminal

WHERE ((([Q01 Generate Data with Duplicates].[Report Dt]) Between CDate([Start Date]) And CDate([End Date])))

ORDER BY [Q01 Generate Data with Duplicates].[Report Dt], [Q01 Generate Data with Duplicates].Terminal;

Still doesnt work! It prompts Y or N, just like if you put [Start date]...

 

[Brianwarnock]

You will notice if you reread our posts that Mailman and I put "Y" not Y.

Y and N are not numeric.

Brian

 

[kidrobot]

My bad, overlooked those quotes

 

[namliam]

My bad, overlooked those quotes

Can happen to the best of us...

Thanks for helping out Brian

I would hate to have to look into that DB... Just looking at those column names makes my head hurt.

(Could be the late hour tho)

 

[kidrobot]

Can happen to the best of us...

Thanks for helping out Brian

I would hate to have to look into that DB... Just looking at those column names makes my head hurt.

(Could be the late hour tho)

lol tell me about it.. most the databases i work with are made by interns, which explains the issues. I myself am an intern.