change a text format to number format

[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

 

[kc-mass]

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

 

[Gina Whipp]

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

 

[ferde]

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

 

[ferde]

Thank you so much...works great

 

[Gina Whipp]

You're welcome!

--
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

Thank you so much...works great

 

[ferde]

Thank you Keven this was very helpful.

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

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

.