Convert datatype using query

R

ReneeD

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
 
K

Ken Snell

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 #];
 
K

KARL DEWEY

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

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

ReneeD

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 said:
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 said:
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
Click to expand...
Click to expand...
 
R

ReneeD

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 said:
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 said:
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
Click to expand...


.
Click to expand...
 
K

Ken Snell

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 said:
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 said:
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 said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
R

ReneeD

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 said:
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 said:
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 said:
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/


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


.
Click to expand...
Click to expand...


.
Click to expand...
 
K

Ken Snell

Show us examples of the values that are not matching.

--

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


ReneeD said:
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 said:
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 said:
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

:

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/


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


.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Design Query to select Child Records with no parent record from 1field 8
Filter a Query 1
Displaying Data 8
where do I set the datatype for a field 5
Need help with SQL Query 1
query syntax error 1
Adding information to a dataset... 2
Make table query: [Enter 1] AS Test 1

Top