change data type in query through a link table

H

hoachen

I don't know how to use the function to change the data type in my query. I
have two link tables and i want to link them but, the column of both link
table have different data type. I can;t change the data type in table due to
it is a link table.

Is there a way i can change the data type from number to text OR text to
number?
 
M

MGFoster

hoachen said:
I don't know how to use the function to change the data type in my query. I
have two link tables and i want to link them but, the column of both link
table have different data type. I can;t change the data type in table due to
it is a link table.

Is there a way i can change the data type from number to text OR text to
number?
Click to expand...


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If the ID in Table2 has numerics but is stored as TEXT and the ID in
Table1 is an integer then like this:

SELECT ...
FROM table1 As T1 INNER JOIN table2 As T2 ON T1.ID = CInt(T2.ID)

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqlU4oechKqOuFEgEQKCpACgjsqZWDORGqJrxB0854ndgr/3LIMAmwTq
NBoo9arXCSqe9bpwSvkJWZTN
=5yvq
-----END PGP SIGNATURE-----
 
H

hoachen

Actually, the column is have bunch of text then follow by the number (AMDKFD:
124556 which is text DataType). I replaced it used Replace function in the
query and leave only the student number. Can i change it here to number
DataType?

Student#: Replace([OutputStr],"ABC:STUDENT#","")
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

To convert the number into a Long integer you'd do this:

Student#: CLng(Replace([OutputStr],"ABC:STUDENT#",""))

See the Type Conversion Functions in the VBA Help - just type "Clng"
(without the quotes) in the Debug window (Ctrl-G) and keeping the cursor
on the word hit the F1 key. The Help article will appear.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqqY9YechKqOuFEgEQJZlwCgr1npdwg5hxg1ATVrCX0n+dvHMNYAoLpY
vWy6hj2zWji7WSg41WCx7wfa
=Jpfd
-----END PGP SIGNATURE-----
Actually, the column is have bunch of text then follow by the number (AMDKFD:
124556 which is text DataType). I replaced it used Replace function in the
query and leave only the student number. Can i change it here to number
DataType?

Student#: Replace([OutputStr],"ABC:STUDENT#","")

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If the ID in Table2 has numerics but is stored as TEXT and the ID in
Table1 is an integer then like this:

SELECT ...
FROM table1 As T1 INNER JOIN table2 As T2 ON T1.ID = CInt(T2.ID)

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqlU4oechKqOuFEgEQKCpACgjsqZWDORGqJrxB0854ndgr/3LIMAmwTq
NBoo9arXCSqe9bpwSvkJWZTN
=5yvq
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
 
Top