expression

[mhtmarsha]

I have two tables I pull together to make labels in a query. In the query i made an expression to put the city, state and zip together in one field.
exp1=Rtrim(city)&" "&Rtrim(state)&" "&Rtrim(zip)

The zip code is formatted in the table it resides in 00000\-9999;;_

The problem is when the labels print some of the zip codes are not in a format such as: 999999999 when it should be 99999-9999

I have over 600 lables in this query but only about 50 of them is effected. I have tried to track it down and I end up at the expression. When I pull the query up it shows that same error in the view of the query.

Can anyone help me with what is going on in this expression? Could it be a fluke? I check the orginal table and it seems fine. The zip is fine by itself in the query but when put in the expression it loses it's format but only on about 50 records. Not all are effected. I would appreciate any help. Thanks.

 

[The_Doc_Man]

The "format" of the field in the table in which something resides is a display format, not a storage format.

You need to use the Format$(...) function, repeating your format string, rather than the raw field itself, when you concatenate your strings. If you are not familiar with Format$, look it up in help. But you DID look up user format strings, so you have most of the battle already won.

 

[mhtmarsha]

expression/converting

I couldn't find much on Format$ but did find something on manipuating text values and that is when i realized:

I think I have figured out what is happening. In my orginal table the field of zip is a text field. It needs to be a number field. When I went to change the field it advised me that the fields would be erased. I have over 7000 records in that table. Is there a way to convert the field from a text to a number field without losing everything?

 

[mark curtis]

=Rtrim([city]) & " " & Rtrim([state]) & " " & Rtrim(cstr([zip]))

 

[mhtmarsha]

expression/converting

Tried it and it didn't work. It turned out to be only maybe 30 out of 600 records that this happened to.