Expression builder in query. (1 Viewer)

[TobyMace]

Hi All,

I am trying to combine two fields into one [RER_Number] from a table in a query using a calulated field and using the expression builder to do so.

I want to combine [RER] and [RER Suffix] but only if [RER Suffix] is not blank and does not equal 0. If [RER Suffix] is blank or does equal 0 then [RER_Number] should just equal [RER].

I have entered in the field row in the table of the query design view:

RER_Number: IIf([RER Suffix]<>"",IIf([RER Suffix]="0",[RER],[RER] & "/" & [RER Suffix]),[RER])

I have now been presented with #Error in the [RER_Number] field only when RER_Suffix is not blank.

Is someone able to suggest a better method?

Thanks in advance.

 

[jdraw]

Can you show us some sample data. You mention 0 and show "0" in the query??
Sample input data, and what you need as output would be helpful for context.

 

[TobyMace]

[RER]=1234 and [RER Suffix]=1 Then [RER_Number]=12341
[RER]=5678 and [RER Suffix]="" Then [RER_Number]=5678
[RER]=9999 and [RER Suffix]=0 Then [RER_Number]=9999

I don't know if you can enter tables on here so sorry this is the best I can do.

 

[Minty]

If I have followed you logic correctly then this should work.

IIF (Len([RER Suffix]  & "") < 1 OR  [RER Suffix] = 0, [RER], [RER] & "/" & [RER Suffix])

This assumes [RER Suffix] is a number.

Edit: Your example data isn't adding the "/"

 

[TobyMace]

Minty. You have made me and therefore a lot of other people very happy indeed! Thank you very much!

jdraw thank you for your help also! I'm sorry I didn't explain it well! I'm rubbish at that sort of thing!

 

[jdraw]

So [RER] and [RER Suffix] are both text datatypes. They are not numbers. They are text strings of "digits".

OOops: I see Minty has made the right guess and solved the issue.

 

[Minty]

Makes a change to guess in the right direction