change a text format to number format

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" wrote in message
...
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,

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

"ferde" wrote in message
...
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" wrote:

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

"ferde" wrote in message
...
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

Thank you so much...works great

"Gina Whipp" wrote:

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

"ferde" wrote in message
...
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

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

"ferde" wrote in message
...
Thank you so much...works great

"Gina Whipp" wrote:

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

"ferde" wrote in message
...
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

Thank you Keven this was very helpful.

"kc-mass" wrote:

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" wrote in message
...
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


.