Hi All,

I have an access database (2000) and having trouble building a query 

using "&" operator.



This is what I am trying to do..

I am trying to build a query which would add information from three 

columns in a table.  The first two columns are text so everything works 

out fine.  But the third column is integer and has masked format for 3 

digits ex. if I type 12 it shows in the table as 012 and 1 shows as 001 

etc.  Now, when I build the query using the "&" operator to add the three 

columns, say A, BC and 001 the resulting query column shows as ABC1 and 

not as ABC001.  The masked format is not getting carried over to the query 

column and our client need them.  

Has anyone come across this problem before?  is there any way to keep the 

format even after using the operator? or is it something that I am 

missing? please let me know.



Any kind of help is appreciated.



Thanks,

Anand

Strictly speaking, you can't concatenate an integer with a string--Access is

coercing your integer field to a string for the purposes of your expression.

Because the "000" format is for displaying the integer field only (e.g.,

Access actually stores the value 12 or 1, not 012 or 001) it's not giving

you the leading zeroes you want.



The fix is to explicitly do the conversion from integer to string yourself,

and in the process get the format you want by using the Format$() function.

If you change your expression from:



TextField1 & TextField2 & IntegerField



to:



TextField1 & TextField2 & Format$(IntegerField, "000")



you'll get what you want.



HTH,



-Roy



-----Original Message-----

From: Anand [mailto:anand.rathinasamy@r...]

Sent: Tuesday, August 14, 2001 11:39 AM

To: Access

Subject: [access] Help with "&" operator





Hi All,

I have an access database (2000) and having trouble building a query 

using "&" operator.



This is what I am trying to do..

I am trying to build a query which would add information from three 

columns in a table.  The first two columns are text so everything works 

out fine.  But the third column is integer and has masked format for 3 

digits ex. if I type 12 it shows in the table as 012 and 1 shows as 001 

etc.  Now, when I build the query using the "&" operator to add the three 

columns, say A, BC and 001 the resulting query column shows as ABC1 and 

not as ABC001.  The masked format is not getting carried over to the query 

column and our client need them.  

Has anyone come across this problem before?  is there any way to keep the 

format even after using the operator? or is it something that I am 

missing? please let me know.



Any kind of help is appreciated.



Thanks,

Anand

The third field should be contatenated with the Format function"

[Field1] & [Field2] & Format([Field], "000")

The three zeros keeps three digits there and will give you the result

you want.



Richard Lobel

Accessible Data

richard@a... <mailto:richard@a...> 

Cell:  (xxx) xxx-xxxx

Fax:  (xxx) xxx-xxxx