Query Expresssion Not Working

[JohnLee]

Good day,

I have written the expression below in my query and for some reason it isn't working, can anyone help please.

ThirdLineAdd: IIf([strThirdLineAdd]="","x",[strThirdLineAdd])

What I am trying to do is if the third line address field has nothing in it, I then want to populate it with a lower case "x"

If it does have something in it, then keep that value.

Any assistance would be most appreciated.

Regards

John Lee

 

[Trevor G]

Have you considered that someone might have pressed the space bar so there may be a character in there. Try using LEN in another column to see if there are extra characters.

 

[JohnLee]

Hi Thanks for your response,

I don't know how to do that, can you give me a pointer please

Regards

John lee

 

[Trevor G]

Create a query and add your table and then double click the 3rd Line address field, then save the query something like qryTestAddress3 then in the next column to the field name click and then use the Expression Builder and type in LEN(strThirdLineAdd) Click OK and then preview the query

 

[JohnLee]

Hi Thanks for that, done what you suggested and where the records for that field have no data it doesn't return any field length, of the 8 records 4 records return a fleld length which is correct because only those for records have data in that field and the other four return no information as to field length which is also correct because there is no data in that field for those records.

So my expression is still not working because there is no spaces in that field for those records, any suggestions as to how I can get it to work as I need.

Your assistance is most appreciated.

Regards

John lee

 

[JohnLee]

Hi Again,

Thanks for your help, I have resolved the problem, I re-wrote the expression as following and got the desired result:

ThirdLineAdd: IIf([strThirdLineAdd] Is Null,"x",[strThirdLineAdd])

Regards

John Lee

 

[pr2-eugin]

I would suggest not using Is Null, as ZLS are not really Null, so they will fail the condition. Use the following instead.

ThirdLineAdd: IIf(Len(Trim([strThirdLineAdd] & "") & "") = 0, "x", [strThirdLineAdd])

 

[JohnLee]

Hi thanks for your response,

I will take on board your observation and use your suggested method.

Regards

John lee