Appending leading zeros by if statement with formulas for true and false, in SQL

[alcorp]

My first question is how do I append 00001 so I can have a value for the false side of 9123400001 instead of 912341



My second question is why does it prompt me to enter parameters? It also produces all false values from the if statement

What I am trying to do is use a column that has either a Y or a N and using the if statement to correspond with different formulas depending on the Y or N.

If anyone can help, thanks!

This what I have so far.
SELECT

IIF (ISRAILROAD=Y,9 & UCN & TXRTAREA, 9 & UCN & 00001 )

FROM CombinedUtility;

[pbaldy]

Try

IIF (ISRAILROAD=-1,9 & UCN & TXRTAREA, 9 & UCN & "00001" )

If you still get a parameter prompt, what is it for? That is Access telling you it can't find something.

[thebigthing313]

In CombinedUtility, you have a field called IsRailroad. Is this a Boolean field, as in Yes/No (True or False)? Or is it a text field where you actually typed in Y or N?

If it's a boolean field, then your Iif function's first argument should be ISRAILROAD = TRUE.
If it's a text field containing either Y or N, then it should be ISRAILROAD = "Y".

As for the parameters, what is it asking for? Most likely reason is you mistyped a field name in your SQL code.

[alcorp]

When I run it it prompts me the parameters just for ISRAILROAD, which is a text filed column too. I also found the column name is IS RAILROAD. I tried eliminating the space in between in between and when I do I just it produces an error for the query.

[alcorp]

Okay I got it, Thanks!

[thebigthing313]

Glad you got it sorted out. Good luck with the rest of your project!