Convert datatype using query

I have create a query which combines two tables; ASSETS and UNITINFO. The
UNITINFO table is linked from another database and the ASSETS table has a
field that looks up a field called UNIT # from the UNITINFO table. Both
fields are called UNIT # but the datatype on the UNITINFO table is set to
Text and the UNIT # field on the ASSETS table is set to Number since it is a
lookup field. I cannot change the datatype on the UNITINFO table to Number
as the field contains both numbers and text. So when I ran my query that I
created to pull data from both tables it gave me an error because the two
fields datatypes don't match.

I am thinking I need to use a query to change the datatype on the UNIT #
field in the ASSETS table to Text in order to get this query to work.

Can anyone show me how to go about this if its possible?

Thank you

Use a non-equijoin to join the two tables. This type of join allows you to
use expressions/functions in the join's ON clause. Note that this type of
query will not be updatable.

You'll need to build this type of query in the SQL view (cannot do it in
datagrid/design view).

SELECT ASSETS.*, UNITINFO.*
FROM ASSETS INNER JOIN UNITINFO
ON CStr(ASSETS.[UNIT #]) = UNITINFO.[UNIT #];

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"ReneeD" wrote in message
...
I have create a query which combines two tables; ASSETS and UNITINFO. The
UNITINFO table is linked from another database and the ASSETS table has a
field that looks up a field called UNIT # from the UNITINFO table. Both
fields are called UNIT # but the datatype on the UNITINFO table is set to
Text and the UNIT # field on the ASSETS table is set to Number since it is
a
lookup field. I cannot change the datatype on the UNITINFO table to
Number
as the field contains both numbers and text. So when I ran my query that
I
created to pull data from both tables it gave me an error because the two
fields datatypes don't match.

I am thinking I need to use a query to change the datatype on the UNIT #
field in the ASSETS table to Text in order to get this query to work.

Can anyone show me how to go about this if its possible?

Thank you

Try this --
qryASSETS --
SELECT [ASSETS].*
FROM [ASSETS];

SELECT [qryASSETS].*, [UNITINFO].*
FROM qryASSETS LEFT JOIN UNITINFO ON [qryASSETS].[UNIT #] = [UNITINFO].[UNIT
#];


--
Build a little, test a little.


"ReneeD" wrote:

I have create a query which combines two tables; ASSETS and UNITINFO. The
UNITINFO table is linked from another database and the ASSETS table has a
field that looks up a field called UNIT # from the UNITINFO table. Both
fields are called UNIT # but the datatype on the UNITINFO table is set to
Text and the UNIT # field on the ASSETS table is set to Number since it is a
lookup field. I cannot change the datatype on the UNITINFO table to Number
as the field contains both numbers and text. So when I ran my query that I
created to pull data from both tables it gave me an error because the two
fields datatypes don't match.

I am thinking I need to use a query to change the datatype on the UNIT #
field in the ASSETS table to Text in order to get this query to work.

Can anyone show me how to go about this if its possible?

Thank you

I have done this assuming I had to create two queries; one called qryASSETS
and one having selected this new query and my UNITINFO table and putting in
the code in the sql view. Should I be able to run the second query? Because
I tried to and it gives me the message 'type mismatch in expression'.

Renee

"KARL DEWEY" wrote:

Try this --
qryASSETS --
SELECT [ASSETS].*
FROM [ASSETS];

SELECT [qryASSETS].*, [UNITINFO].*
FROM qryASSETS LEFT JOIN UNITINFO ON [qryASSETS].[UNIT #] = [UNITINFO].[UNIT
#];


--
Build a little, test a little.


"ReneeD" wrote:

I have create a query which combines two tables; ASSETS and UNITINFO. The
UNITINFO table is linked from another database and the ASSETS table has a
field that looks up a field called UNIT # from the UNITINFO table. Both
fields are called UNIT # but the datatype on the UNITINFO table is set to
Text and the UNIT # field on the ASSETS table is set to Number since it is a
lookup field. I cannot change the datatype on the UNITINFO table to Number
as the field contains both numbers and text. So when I ran my query that I
created to pull data from both tables it gave me an error because the two
fields datatypes don't match.

I am thinking I need to use a query to change the datatype on the UNIT #
field in the ASSETS table to Text in order to get this query to work.

Can anyone show me how to go about this if its possible?

Thank you

I have tried the query you told me to create and it runs the query but it
only shows 17 records and it should a lot more than that. Why wouldn't the
other entries show up when I know that the unit #'s match in both tables?

Renee

"Ken Snell" wrote:

Use a non-equijoin to join the two tables. This type of join allows you to
use expressions/functions in the join's ON clause. Note that this type of
query will not be updatable.

You'll need to build this type of query in the SQL view (cannot do it in
datagrid/design view).

SELECT ASSETS.*, UNITINFO.*
FROM ASSETS INNER JOIN UNITINFO
ON CStr(ASSETS.[UNIT #]) = UNITINFO.[UNIT #];

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"ReneeD" wrote in message
...
I have create a query which combines two tables; ASSETS and UNITINFO. The
UNITINFO table is linked from another database and the ASSETS table has a
field that looks up a field called UNIT # from the UNITINFO table. Both
fields are called UNIT # but the datatype on the UNITINFO table is set to
Text and the UNIT # field on the ASSETS table is set to Number since it is
a
lookup field. I cannot change the datatype on the UNITINFO table to
Number
as the field contains both numbers and text. So when I ran my query that
I
created to pull data from both tables it gave me an error because the two
fields datatypes don't match.

I am thinking I need to use a query to change the datatype on the UNIT #
field in the ASSETS table to Text in order to get this query to work.

Can anyone show me how to go about this if its possible?

Thank you


.

If those records don't show up, they likely don't have the value in UNIT #
that you think they do. Look at the values for the "missing" records, and be
sure that there are no leading spaces, no trailing spaces, and no trailing
carriage return or line feed character.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"ReneeD" wrote in message
...
I have tried the query you told me to create and it runs the query but it
only shows 17 records and it should a lot more than that. Why wouldn't the
other entries show up when I know that the unit #'s match in both tables?

Renee

"Ken Snell" wrote:

Use a non-equijoin to join the two tables. This type of join allows you
to
use expressions/functions in the join's ON clause. Note that this type of
query will not be updatable.

You'll need to build this type of query in the SQL view (cannot do it in
datagrid/design view).

SELECT ASSETS.*, UNITINFO.*
FROM ASSETS INNER JOIN UNITINFO
ON CStr(ASSETS.[UNIT #]) = UNITINFO.[UNIT #];

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"ReneeD" wrote in message
...
I have create a query which combines two tables; ASSETS and UNITINFO.
The
UNITINFO table is linked from another database and the ASSETS table has
a
field that looks up a field called UNIT # from the UNITINFO table.
Both
fields are called UNIT # but the datatype on the UNITINFO table is set
to
Text and the UNIT # field on the ASSETS table is set to Number since it
is
a
lookup field. I cannot change the datatype on the UNITINFO table to
Number
as the field contains both numbers and text. So when I ran my query
that
I
created to pull data from both tables it gave me an error because the
two
fields datatypes don't match.

I am thinking I need to use a query to change the datatype on the UNIT
#
field in the ASSETS table to Text in order to get this query to work.

Can anyone show me how to go about this if its possible?

Thank you


.

All the unit # entries should match from the UNITINFO table to the ASSETS
table since the unit # field on the ASSETS table is a lookup field to the
unit # field on the UNITINFO table. That is why I don't understand why it
would only select certain records.

"Ken Snell" wrote:

If those records don't show up, they likely don't have the value in UNIT #
that you think they do. Look at the values for the "missing" records, and be
sure that there are no leading spaces, no trailing spaces, and no trailing
carriage return or line feed character.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"ReneeD" wrote in message
...
I have tried the query you told me to create and it runs the query but it
only shows 17 records and it should a lot more than that. Why wouldn't the
other entries show up when I know that the unit #'s match in both tables?

Renee

"Ken Snell" wrote:

Use a non-equijoin to join the two tables. This type of join allows you
to
use expressions/functions in the join's ON clause. Note that this type of
query will not be updatable.

You'll need to build this type of query in the SQL view (cannot do it in
datagrid/design view).

SELECT ASSETS.*, UNITINFO.*
FROM ASSETS INNER JOIN UNITINFO
ON CStr(ASSETS.[UNIT #]) = UNITINFO.[UNIT #];

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"ReneeD" wrote in message
...
I have create a query which combines two tables; ASSETS and UNITINFO.
The
UNITINFO table is linked from another database and the ASSETS table has
a
field that looks up a field called UNIT # from the UNITINFO table.
Both
fields are called UNIT # but the datatype on the UNITINFO table is set
to
Text and the UNIT # field on the ASSETS table is set to Number since it
is
a
lookup field. I cannot change the datatype on the UNITINFO table to
Number
as the field contains both numbers and text. So when I ran my query
that
I
created to pull data from both tables it gave me an error because the
two
fields datatypes don't match.

I am thinking I need to use a query to change the datatype on the UNIT
#
field in the ASSETS table to Text in order to get this query to work.

Can anyone show me how to go about this if its possible?

Thank you


.



.

Show us examples of the values that are not matching.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"ReneeD" wrote in message
...
All the unit # entries should match from the UNITINFO table to the ASSETS
table since the unit # field on the ASSETS table is a lookup field to the
unit # field on the UNITINFO table. That is why I don't understand why it
would only select certain records.

"Ken Snell" wrote:

If those records don't show up, they likely don't have the value in UNIT
#
that you think they do. Look at the values for the "missing" records, and
be
sure that there are no leading spaces, no trailing spaces, and no
trailing
carriage return or line feed character.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"ReneeD" wrote in message
...
I have tried the query you told me to create and it runs the query but
it
only shows 17 records and it should a lot more than that. Why wouldn't
the
other entries show up when I know that the unit #'s match in both
tables?

Renee

"Ken Snell" wrote:

Use a non-equijoin to join the two tables. This type of join allows
you
to
use expressions/functions in the join's ON clause. Note that this type
of
query will not be updatable.

You'll need to build this type of query in the SQL view (cannot do it
in
datagrid/design view).

SELECT ASSETS.*, UNITINFO.*
FROM ASSETS INNER JOIN UNITINFO
ON CStr(ASSETS.[UNIT #]) = UNITINFO.[UNIT #];

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"ReneeD" wrote in message
...
I have create a query which combines two tables; ASSETS and UNITINFO.
The
UNITINFO table is linked from another database and the ASSETS table
has
a
field that looks up a field called UNIT # from the UNITINFO table.
Both
fields are called UNIT # but the datatype on the UNITINFO table is
set
to
Text and the UNIT # field on the ASSETS table is set to Number since
it
is
a
lookup field. I cannot change the datatype on the UNITINFO table to
Number
as the field contains both numbers and text. So when I ran my query
that
I
created to pull data from both tables it gave me an error because
the
two
fields datatypes don't match.

I am thinking I need to use a query to change the datatype on the
UNIT
#
field in the ASSETS table to Text in order to get this query to
work.

Can anyone show me how to go about this if its possible?

Thank you


.



.