change a text format to number format

  • Thread starter Thread starter ferde
  • Start date Start date
F

ferde

I need to link to someone elses table but they have used a different format
type for a field called ACCOUNT. I am a beginner and do not know how to
write SQL but I was wondering if it is possible to use an update query to
change a text field in this situation into a number format. I am trying to
run a mismatch query on this table with another table in my database that has
a field called ACCOUNT but my table has a number format. I am trying to come
up with a list of ACCOUNT numbers that are common in both tables.

Thank you in advance for any direction you can provide
 
Let's say your account table is tblAccount2 and the one with the text
accounts is tblAccount1.

Create one query that converts your account field to text ( just in the
query not in the table).
Something like:
SELECT CStr([Account]) AS AccountAsText
FROM tblAccount2;

Then create a second query that joins the first query and tblAccount1.
Run the query and you have your answer.

SELECT tblAccount1.Account, qryToString.AccountAsText
FROM qryToString INNER JOIN tblAccount1 ON qryToString.AccountAsText =
tblAccount1.Account;

Regards

Kevin
 
ferde,

No, you can't change a fields Data Type by running an Update query. Try
creating a query with the linked table and for the field ACCOUNT put
(copy/paste)...

AccountNumber: Val([ACCOUNT])

....in the *Field:* part of the query replacing the ACCOUNT field.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I need to link to someone elses table but they have used a different
format
type for a field called ACCOUNT. I am a beginner and do not know how to
write SQL but I was wondering if it is possible to use an update query to
change a text field in this situation into a number format. I am trying to
run a mismatch query on this table with another table in my database that
has
a field called ACCOUNT but my table has a number format. I am trying to
come
up with a list of ACCOUNT numbers that are common in both tables.

Thank you in advance for any direction you can provide
 
Gina Whipp said:
ferde,

No, you can't change a fields Data Type by running an Update query. Try
creating a query with the linked table and for the field ACCOUNT put
(copy/paste)...

AccountNumber: Val([ACCOUNT])

...in the *Field:* part of the query replacing the ACCOUNT field.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I need to link to someone elses table but they have used a different
format
type for a field called ACCOUNT. I am a beginner and do not know how to
write SQL but I was wondering if it is possible to use an update query to
change a text field in this situation into a number format. I am trying to
run a mismatch query on this table with another table in my database that
has
a field called ACCOUNT but my table has a number format. I am trying to
come
up with a list of ACCOUNT numbers that are common in both tables.

Thank you in advance for any direction you can provide
Click to expand...
 
Thank you Keven this was very helpful.

kc-mass said:
Let's say your account table is tblAccount2 and the one with the text
accounts is tblAccount1.

Create one query that converts your account field to text ( just in the
query not in the table).
Something like:
SELECT CStr([Account]) AS AccountAsText
FROM tblAccount2;

Then create a second query that joins the first query and tblAccount1.
Run the query and you have your answer.

SELECT tblAccount1.Account, qryToString.AccountAsText
FROM qryToString INNER JOIN tblAccount1 ON qryToString.AccountAsText =
tblAccount1.Account;

Regards

Kevin


ferde said:
I need to link to someone elses table but they have used a different
format
type for a field called ACCOUNT. I am a beginner and do not know how to
write SQL but I was wondering if it is possible to use an update query to
change a text field in this situation into a number format. I am trying to
run a mismatch query on this table with another table in my database that
has
a field called ACCOUNT but my table has a number format. I am trying to
come
up with a list of ACCOUNT numbers that are common in both tables.

Thank you in advance for any direction you can provide
Click to expand...


.
Click to expand...
 
Back
Top