How do I change a data type using SQL?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is the Situation;

Table A with Field A, data type = Numeric, needs to join Table B with Field
B, data type = Text.

How do I change the data type on Field B to make the join?

Thanks
 
Dear Skeske:

You could either convert the text values to a number (using CInt(), CLng(),
etc.) or you could convert the numbers to text (CStr). There could be some
important differences in how these work. For example 123 is not the same as
"123b" (where b represents a space or any non-digit). It may be that the
CInt() version is best, but some testing is likely a best bet.

Tom Ellison
Microsoft Access MVP
 
I suggest that you go the other direction as you can convert a number to a
string; however all strings can't be converted to a number.

SELECT B.*
FROM B INNER JOIN A ON B.[Field B] = CStr(A.[Field A]);

You can't do the above in the QBE grid. Need to use the SQL view.
 
So if I understand you correctly, my FROM statement might look like the
following:

FROM (dbo_tblLoadDispatch RIGHT JOIN dbo_tblLoads ON
dbo_tblLoadDispatch.LoadID = dbo_tblLoads.LoadID) RIGHT JOIN
dbo_tblCheckCalls ON dbo_tblLoadDispatch.LoadID =
CINT(dbo_tblCheckCalls.[LoadNumber])

Bear in the mind that LoadID and LoadNumber will be the same value. For
example:

dbo_tblLoadDispatch.LoadID = 30686
dbo_tblCheckCalls.LoadNumber = 30686

The data type on the field throws me off.

Jerry Whittle said:
I suggest that you go the other direction as you can convert a number to a
string; however all strings can't be converted to a number.

SELECT B.*
FROM B INNER JOIN A ON B.[Field B] = CStr(A.[Field A]);

You can't do the above in the QBE grid. Need to use the SQL view.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


skeske said:
Here is the Situation;

Table A with Field A, data type = Numeric, needs to join Table B with Field
B, data type = Text.

How do I change the data type on Field B to make the join?

Thanks
Click to expand...
Click to expand...
 
So if I understand you correctly, my FROM statement might look like the
following:

FROM (dbo_tblLoadDispatch RIGHT JOIN dbo_tblLoads ON
dbo_tblLoadDispatch.LoadID = dbo_tblLoads.LoadID) RIGHT JOIN
dbo_tblCheckCalls ON dbo_tblLoadDispatch.LoadID =
CINT(dbo_tblCheckCalls.[LoadNumber])

Bear in the mind that LoadID and LoadNumber will be the same value. For
example:

dbo_tblLoadDispatch.LoadID = 30686
dbo_tblCheckCalls.LoadNumber = 30686

The data type on the field throws me off.
 
I perfer to go the other direction with CStr as a number can convert to a
string; however, a string might not convert to a number. For example
CStr("123a") works while CInt("123a") will cause an error.

Since your example numbers/text are up above 30,000 consider using CDbl if
there could be decimals or CLng if all integers. CInt will give you overflow
errors on numbers larger than 32767.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

skeske said:
So if I understand you correctly, my FROM statement might look like the
following:

FROM (dbo_tblLoadDispatch RIGHT JOIN dbo_tblLoads ON
dbo_tblLoadDispatch.LoadID = dbo_tblLoads.LoadID) RIGHT JOIN
dbo_tblCheckCalls ON dbo_tblLoadDispatch.LoadID =
CINT(dbo_tblCheckCalls.[LoadNumber])

Bear in the mind that LoadID and LoadNumber will be the same value. For
example:

dbo_tblLoadDispatch.LoadID = 30686
dbo_tblCheckCalls.LoadNumber = 30686

The data type on the field throws me off.

Jerry Whittle said:
I suggest that you go the other direction as you can convert a number to a
string; however all strings can't be converted to a number.

SELECT B.*
FROM B INNER JOIN A ON B.[Field B] = CStr(A.[Field A]);

You can't do the above in the QBE grid. Need to use the SQL view.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


skeske said:
Here is the Situation;

Table A with Field A, data type = Numeric, needs to join Table B with Field
B, data type = Text.

How do I change the data type on Field B to make the join?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
I get the following error and there are no suggestions for the syntax

Join Expression Not Supported

Here is all the SQL:

SELECT DISTINCT dbo_tblLoads.LoadID, dbo_tblLoads.ShipName,
dbo_tblLoads.PickupDate, dbo_tblLoadDispatch.CarrierRef,
dbo_tblLoads.BillToRef, dbo_tblLoads.TotalWeight, dbo_tblLoads.Status,
dbo_tblLoads.SatStatus


FROM (dbo_tblLoadDispatch RIGHT JOIN dbo_tblLoads ON
dbo_tblLoadDispatch.LoadID = dbo_tblLoads.LoadID) LEFT JOIN dbo_tblCheckCalls
ON CSTr(dbo_tblLoadDispatch.LoadID) = dbo_tblCheckCalls.LoadNumber


GROUP BY dbo_tblLoads.LoadID, dbo_tblLoads.ShipName,
dbo_tblLoads.PickupDate, dbo_tblLoadDispatch.CarrierRef,
dbo_tblLoads.BillToRef, dbo_tblLoads.TotalWeight, dbo_tblLoads.Status,
dbo_tblLoads.SatStatus, dbo_tblLoads.BillToID, dbo_tblLoads.InvoiceDate,
dbo_tblLoadDispatch.PayType
HAVING (((dbo_tblLoads.Status)<>"ARRIVED") AND ((dbo_tblLoads.SatStatus)=5)
AND ((dbo_tblLoads.BillToID) In (3549,3359)) AND ((dbo_tblLoads.InvoiceDate)
Is Null) AND ((dbo_tblLoadDispatch.PayType)="FLAT"))
ORDER BY dbo_tblLoads.LoadID;


Jerry Whittle said:
I perfer to go the other direction with CStr as a number can convert to a
string; however, a string might not convert to a number. For example
CStr("123a") works while CInt("123a") will cause an error.

Since your example numbers/text are up above 30,000 consider using CDbl if
there could be decimals or CLng if all integers. CInt will give you overflow
errors on numbers larger than 32767.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

skeske said:
So if I understand you correctly, my FROM statement might look like the
following:

FROM (dbo_tblLoadDispatch RIGHT JOIN dbo_tblLoads ON
dbo_tblLoadDispatch.LoadID = dbo_tblLoads.LoadID) RIGHT JOIN
dbo_tblCheckCalls ON dbo_tblLoadDispatch.LoadID =
CINT(dbo_tblCheckCalls.[LoadNumber])

Bear in the mind that LoadID and LoadNumber will be the same value. For
example:

dbo_tblLoadDispatch.LoadID = 30686
dbo_tblCheckCalls.LoadNumber = 30686

The data type on the field throws me off.

Jerry Whittle said:
I suggest that you go the other direction as you can convert a number to a
string; however all strings can't be converted to a number.

SELECT B.*
FROM B INNER JOIN A ON B.[Field B] = CStr(A.[Field A]);

You can't do the above in the QBE grid. Need to use the SQL view.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Here is the Situation;

Table A with Field A, data type = Numeric, needs to join Table B with Field
B, data type = Text.

How do I change the data type on Field B to make the join?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Dear Skeske:

I don't know all the details of your situation. It seemed to me that what I
suggested could be useful. Can you try it?

Tom Ellison
Microsoft Access MVP


skeske said:
So if I understand you correctly, my FROM statement might look like the
following:

FROM (dbo_tblLoadDispatch RIGHT JOIN dbo_tblLoads ON
dbo_tblLoadDispatch.LoadID = dbo_tblLoads.LoadID) RIGHT JOIN
dbo_tblCheckCalls ON dbo_tblLoadDispatch.LoadID =
CINT(dbo_tblCheckCalls.[LoadNumber])

Bear in the mind that LoadID and LoadNumber will be the same value. For
example:

dbo_tblLoadDispatch.LoadID = 30686
dbo_tblCheckCalls.LoadNumber = 30686

The data type on the field throws me off.

Tom Ellison said:
Dear Skeske:

You could either convert the text values to a number (using CInt(),
CLng(),
etc.) or you could convert the numbers to text (CStr). There could be
some
important differences in how these work. For example 123 is not the same
as
"123b" (where b represents a space or any non-digit). It may be that the
CInt() version is best, but some testing is likely a best bet.

Tom Ellison
Microsoft Access MVP
Click to expand...
Click to expand...
 
Tom,

Here is the SQL I tried and it did not work:

I get the following error and there are no suggestions for the syntax

Join Expression Not Supported


SELECT DISTINCT dbo_tblLoads.LoadID, dbo_tblLoads.ShipName,
dbo_tblLoads.PickupDate, dbo_tblLoadDispatch.CarrierRef,
dbo_tblLoads.BillToRef, dbo_tblLoads.TotalWeight, dbo_tblLoads.Status,
dbo_tblLoads.SatStatus


FROM (dbo_tblLoadDispatch RIGHT JOIN dbo_tblLoads ON
dbo_tblLoadDispatch.LoadID = dbo_tblLoads.LoadID) LEFT JOIN dbo_tblCheckCalls
ON CSTr(dbo_tblLoadDispatch.LoadID) = dbo_tblCheckCalls.LoadNumber


GROUP BY dbo_tblLoads.LoadID, dbo_tblLoads.ShipName,
dbo_tblLoads.PickupDate, dbo_tblLoadDispatch.CarrierRef,
dbo_tblLoads.BillToRef, dbo_tblLoads.TotalWeight, dbo_tblLoads.Status,
dbo_tblLoads.SatStatus, dbo_tblLoads.BillToID, dbo_tblLoads.InvoiceDate,
dbo_tblLoadDispatch.PayType
HAVING (((dbo_tblLoads.Status)<>"ARRIVED") AND ((dbo_tblLoads.SatStatus)=5)
AND ((dbo_tblLoads.BillToID) In (3549,3359)) AND ((dbo_tblLoads.InvoiceDate)
Is Null) AND ((dbo_tblLoadDispatch.PayType)="FLAT"))
ORDER BY dbo_tblLoads.LoadID;

Tom Ellison said:
Dear Skeske:

I don't know all the details of your situation. It seemed to me that what I
suggested could be useful. Can you try it?

Tom Ellison
Microsoft Access MVP


skeske said:
So if I understand you correctly, my FROM statement might look like the
following:

FROM (dbo_tblLoadDispatch RIGHT JOIN dbo_tblLoads ON
dbo_tblLoadDispatch.LoadID = dbo_tblLoads.LoadID) RIGHT JOIN
dbo_tblCheckCalls ON dbo_tblLoadDispatch.LoadID =
CINT(dbo_tblCheckCalls.[LoadNumber])

Bear in the mind that LoadID and LoadNumber will be the same value. For
example:

dbo_tblLoadDispatch.LoadID = 30686
dbo_tblCheckCalls.LoadNumber = 30686

The data type on the field throws me off.

Tom Ellison said:
Dear Skeske:

You could either convert the text values to a number (using CInt(),
CLng(),
etc.) or you could convert the numbers to text (CStr). There could be
some
important differences in how these work. For example 123 is not the same
as
"123b" (where b represents a space or any non-digit). It may be that the
CInt() version is best, but some testing is likely a best bet.

Tom Ellison
Microsoft Access MVP


Here is the Situation;

Table A with Field A, data type = Numeric, needs to join Table B with
Field
B, data type = Text.

How do I change the data type on Field B to make the join?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Dear Skeske:

If the problem is in the JOIN, I suggest you simplify just to get that much
working. First, reduce the complexity of your query to just this:

SELECT *
FROM dbo_tblLoadDispatch

Then add just one JOIN:

Select *
FROM dbo_tblLoadDispatch
RIGHT JOIN dbo_tblLoads
ON dbo_tblLoadDispatch.LoadID = dbo_tblLoads.LoadID

Then add the other JOIN:

Select *
FROM (dbo_tblLoadDispatch
RIGHT JOIN dbo_tblLoads
ON dbo_tblLoadDispatch.LoadID = dbo_tblLoads.LoadID)
LEFT JOIN dbo_tblCheckCalls
ON CStr(dbo_tblLoadDispatch.LoadID) = dbo_tblCheckCalls.LoadNumber

I changed CSTr to CStr, but I don't think that's the problem.

Now, at what point, if any, does this fail? Also, is
dbo_tblCheckCalls.LoadNumber a text value? If not, change to:

ON dbo_tblLoadDispatch.LoadID = dbo_tblCheckCalls.LoadNumber

Or, you could convert dbo_tblLoads.LoadID to be a long integer:

ON dbo_tblLoadDispatch.LoadID = CLng(dbo_tblCheckCalls.LoadNumber)

A text value may have leading or trailing spaces, so converting the text to
numeric is often safer, as the conversion will strip (ignore) those
leading/trailing spaces.

Please let me know what happens, OK?

Tom Ellison
Microsoft Access MVP


skeske said:
Tom,

Here is the SQL I tried and it did not work:

I get the following error and there are no suggestions for the syntax

Join Expression Not Supported


SELECT DISTINCT dbo_tblLoads.LoadID, dbo_tblLoads.ShipName,
dbo_tblLoads.PickupDate, dbo_tblLoadDispatch.CarrierRef,
dbo_tblLoads.BillToRef, dbo_tblLoads.TotalWeight, dbo_tblLoads.Status,
dbo_tblLoads.SatStatus


FROM (dbo_tblLoadDispatch RIGHT JOIN dbo_tblLoads ON
dbo_tblLoadDispatch.LoadID = dbo_tblLoads.LoadID) LEFT JOIN
dbo_tblCheckCalls
ON CSTr(dbo_tblLoadDispatch.LoadID) = dbo_tblCheckCalls.LoadNumber


GROUP BY dbo_tblLoads.LoadID, dbo_tblLoads.ShipName,
dbo_tblLoads.PickupDate, dbo_tblLoadDispatch.CarrierRef,
dbo_tblLoads.BillToRef, dbo_tblLoads.TotalWeight, dbo_tblLoads.Status,
dbo_tblLoads.SatStatus, dbo_tblLoads.BillToID, dbo_tblLoads.InvoiceDate,
dbo_tblLoadDispatch.PayType
HAVING (((dbo_tblLoads.Status)<>"ARRIVED") AND
((dbo_tblLoads.SatStatus)=5)
AND ((dbo_tblLoads.BillToID) In (3549,3359)) AND
((dbo_tblLoads.InvoiceDate)
Is Null) AND ((dbo_tblLoadDispatch.PayType)="FLAT"))
ORDER BY dbo_tblLoads.LoadID;

Tom Ellison said:
Dear Skeske:

I don't know all the details of your situation. It seemed to me that
what I
suggested could be useful. Can you try it?

Tom Ellison
Microsoft Access MVP


skeske said:
So if I understand you correctly, my FROM statement might look like the
following:

FROM (dbo_tblLoadDispatch RIGHT JOIN dbo_tblLoads ON
dbo_tblLoadDispatch.LoadID = dbo_tblLoads.LoadID) RIGHT JOIN
dbo_tblCheckCalls ON dbo_tblLoadDispatch.LoadID =
CINT(dbo_tblCheckCalls.[LoadNumber])

Bear in the mind that LoadID and LoadNumber will be the same value.
For
example:

dbo_tblLoadDispatch.LoadID = 30686
dbo_tblCheckCalls.LoadNumber = 30686

The data type on the field throws me off.

:

Dear Skeske:

You could either convert the text values to a number (using CInt(),
CLng(),
etc.) or you could convert the numbers to text (CStr). There could be
some
important differences in how these work. For example 123 is not the
same
as
"123b" (where b represents a space or any non-digit). It may be that
the
CInt() version is best, but some testing is likely a best bet.

Tom Ellison
Microsoft Access MVP


Here is the Situation;

Table A with Field A, data type = Numeric, needs to join Table B
with
Field
B, data type = Text.

How do I change the data type on Field B to make the join?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Hummm. It could be due to the totals query with all the Group By's, Having,
or maybe LEFT JOIN statement. Let's try to simplify and see what happens:

SELECT DISTINCT dbo_tblLoads.LoadID,
dbo_tblLoads.ShipName,
dbo_tblLoads.PickupDate,
dbo_tblLoadDispatch.CarrierRef,
dbo_tblLoads.BillToRef,
dbo_tblLoads.TotalWeight,
dbo_tblLoads.Status,
dbo_tblLoads.SatStatus
FROM (dbo_tblLoadDispatch RIGHT JOIN dbo_tblLoads ON
dbo_tblLoadDispatch.LoadID = dbo_tblLoads.LoadID) INNER JOIN
dbo_tblCheckCalls
ON CStr(dbo_tblLoadDispatch.LoadID) = dbo_tblCheckCalls.LoadNumber
ORDER BY dbo_tblLoads.LoadID;

Notice that I made it an INNER join. If the above works, change INNER to
LEFT and run again.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


skeske said:
I get the following error and there are no suggestions for the syntax

Join Expression Not Supported

Here is all the SQL:

SELECT DISTINCT dbo_tblLoads.LoadID, dbo_tblLoads.ShipName,
dbo_tblLoads.PickupDate, dbo_tblLoadDispatch.CarrierRef,
dbo_tblLoads.BillToRef, dbo_tblLoads.TotalWeight, dbo_tblLoads.Status,
dbo_tblLoads.SatStatus


FROM (dbo_tblLoadDispatch RIGHT JOIN dbo_tblLoads ON
dbo_tblLoadDispatch.LoadID = dbo_tblLoads.LoadID) LEFT JOIN dbo_tblCheckCalls
ON CSTr(dbo_tblLoadDispatch.LoadID) = dbo_tblCheckCalls.LoadNumber


GROUP BY dbo_tblLoads.LoadID, dbo_tblLoads.ShipName,
dbo_tblLoads.PickupDate, dbo_tblLoadDispatch.CarrierRef,
dbo_tblLoads.BillToRef, dbo_tblLoads.TotalWeight, dbo_tblLoads.Status,
dbo_tblLoads.SatStatus, dbo_tblLoads.BillToID, dbo_tblLoads.InvoiceDate,
dbo_tblLoadDispatch.PayType
HAVING (((dbo_tblLoads.Status)<>"ARRIVED") AND ((dbo_tblLoads.SatStatus)=5)
AND ((dbo_tblLoads.BillToID) In (3549,3359)) AND ((dbo_tblLoads.InvoiceDate)
Is Null) AND ((dbo_tblLoadDispatch.PayType)="FLAT"))
ORDER BY dbo_tblLoads.LoadID;


Jerry Whittle said:
I perfer to go the other direction with CStr as a number can convert to a
string; however, a string might not convert to a number. For example
CStr("123a") works while CInt("123a") will cause an error.

Since your example numbers/text are up above 30,000 consider using CDbl if
there could be decimals or CLng if all integers. CInt will give you overflow
errors on numbers larger than 32767.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

skeske said:
So if I understand you correctly, my FROM statement might look like the
following:

FROM (dbo_tblLoadDispatch RIGHT JOIN dbo_tblLoads ON
dbo_tblLoadDispatch.LoadID = dbo_tblLoads.LoadID) RIGHT JOIN
dbo_tblCheckCalls ON dbo_tblLoadDispatch.LoadID =
CINT(dbo_tblCheckCalls.[LoadNumber])

Bear in the mind that LoadID and LoadNumber will be the same value. For
example:

dbo_tblLoadDispatch.LoadID = 30686
dbo_tblCheckCalls.LoadNumber = 30686

The data type on the field throws me off.

:

I suggest that you go the other direction as you can convert a number to a
string; however all strings can't be converted to a number.

SELECT B.*
FROM B INNER JOIN A ON B.[Field B] = CStr(A.[Field A]);

You can't do the above in the QBE grid. Need to use the SQL view.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Here is the Situation;

Table A with Field A, data type = Numeric, needs to join Table B with Field
B, data type = Text.

How do I change the data type on Field B to make the join?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top