CONVERT A NUMBER TO TEXT

[Guest]

I'm reading ales data from a Customer database in SQL 2000 where the customer
number(Primary Key) is a numeric field to update participation data to an
access database.

Unfortunately the Customer number field in access,he primary key, is text.

Using the original BASIC programming I could do:

A$=STR(A:"0000")

in order to convert the numeric data to alphanum. How can I accomplish this
in an access query?

 

[Vincent Johns]

I'm reading ales data from a Customer database in SQL 2000 where the customer
number(Primary Key) is a numeric field to update participation data to an
access database.

Ah, so you're a brewmeister.

Unfortunately the Customer number field in access,he primary key, is text.

Using the original BASIC programming I could do:

A$=STR(A:"0000")

in order to convert the numeric data to alphanum. How can I accomplish this
in an access query?

In Access, you can use

Format$(A,"0000")

to produce a 4-character string (such as "0077" if A = 77).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

I'm still confused. Perhaps including the query will help:

UPDATE Client1 INNER JOIN PARHSTY1TEMP ON
Client1.CLIENTNO=PARHSTY1TEMP.Clientno SET
Client1.Partici=Client1.Partici+PARHSTYTEMP.AMOUNT

My problem is that Client1.CLIENTNO is TEXT while PARHSTY1TEMP.CLIENTNO is
NUMBER.

What needs to be added to this Query so that it will work?

 

[Suzette]

I think you are going to have to go through a middle step of having a query
that creates has a new field converting that to text. You can then use the
update command.

 

[John Spencer]

Try using Clng or Val to convert the text string to a number

UPDATE Client1 INNER JOIN PARHSTY1TEMP ON
CLng(Client1.CLIENTNO)=PARHSTY1TEMP.Clientno
SET Client1.Partici=Client1.Partici+PARHSTYTEMP.AMOUNT