Data type mismatch

[Guest]

I have a query for 2 tables where the join connects a text and numeric field.
I know that the data types should match. Can I run a query to convert the
text to numeric or vice versa and then use that query? If so, how do I run
the conversion. I cannot change the data type in the table as the entire
database does not belong to me, however I am allowed to query the data.

SELECT oppro.opdivtype, oppro.oppronum, oppro.opstatus, oppro.opshipname,
oppro.oporgcity, oppro.oporgst, oppro.opconsnm, oppro.opconscty,
oppro.opconsst, oppro.oppickpdat, oppro.oppayrname, oppro.opbilltamt,
oppro.opbillporc, opmove.omtractor, opmove.omdriver1
FROM oppro INNER JOIN opmove ON oppro.oppronum = opmove.ompronum
WHERE (((oppro.opstatus)<>"C") AND ((oppro.oppickpdat) Between [Start Date]
And [End Date]));

Any help would be appreciated.

Thanks,
Judy

 

[Guest]

You can edit the join (it will give an error when you go to design view
saying it cannot display the join) like Val([YourTextField])

 

[Guest]

I don't which table contain the text and which table contain the number, but
you can convert the field in the connection

For example: if table oppro contain the rext field, and the opmove table
contain the number field (Long type) you can use something like

SELECT oppro.opdivtype, oppro.oppronum, oppro.opstatus, oppro.opshipname,
oppro.oporgcity, oppro.oporgst, oppro.opconsnm, oppro.opconscty,
oppro.opconsst, oppro.oppickpdat, oppro.oppayrname, oppro.opbilltamt,
oppro.opbillporc, opmove.omtractor, opmove.omdriver1
FROM oppro INNER JOIN opmove ON Clng(oppro.oppronum) = opmove.ompronum
WHERE (((oppro.opstatus)<>"C") AND ((oppro.oppickpdat) Between [Start Date]
And [End Date]))

 

[Guest]

I tried both options offered. Thanks for the quick response. The table that
contains the text field is opmove.ompronum. Both options let me enter the
date range but returned no data which I know to be incorrect. ???

I don't which table contain the text and which table contain the number, but
you can convert the field in the connection

For example: if table oppro contain the rext field, and the opmove table
contain the number field (Long type) you can use something like

SELECT oppro.opdivtype, oppro.oppronum, oppro.opstatus, oppro.opshipname,
oppro.oporgcity, oppro.oporgst, oppro.opconsnm, oppro.opconscty,
oppro.opconsst, oppro.oppickpdat, oppro.oppayrname, oppro.opbilltamt,
oppro.opbillporc, opmove.omtractor, opmove.omdriver1
FROM oppro INNER JOIN opmove ON Clng(oppro.oppronum) = opmove.ompronum
WHERE (((oppro.opstatus)<>"C") AND ((oppro.oppickpdat) Between [Start Date]
And [End Date]))
--
Good Luck
BS"D

I have a query for 2 tables where the join connects a text and numeric field.
I know that the data types should match. Can I run a query to convert the
text to numeric or vice versa and then use that query? If so, how do I run
the conversion. I cannot change the data type in the table as the entire
database does not belong to me, however I am allowed to query the data.

SELECT oppro.opdivtype, oppro.oppronum, oppro.opstatus, oppro.opshipname,
oppro.oporgcity, oppro.oporgst, oppro.opconsnm, oppro.opconscty,
oppro.opconsst, oppro.oppickpdat, oppro.oppayrname, oppro.opbilltamt,
oppro.opbillporc, opmove.omtractor, opmove.omdriver1
FROM oppro INNER JOIN opmove ON oppro.oppronum = opmove.ompronum
WHERE (((oppro.opstatus)<>"C") AND ((oppro.oppickpdat) Between [Start Date]
And [End Date]));

Any help would be appreciated.

Thanks,
Judy

 

[Guest]

OK...here's what I did. I created a query on the table (opmove) with the
function CLng(ompronum) and added all fields from the table. Then I started
another query and joined the now converted ompronum (Expr1) to oppro. Put in
my criteria and it worked. Thanks again for the quick response and ideas.
Judy

I tried both options offered. Thanks for the quick response. The table that
contains the text field is opmove.ompronum. Both options let me enter the
date range but returned no data which I know to be incorrect. ???

I don't which table contain the text and which table contain the number, but
you can convert the field in the connection

For example: if table oppro contain the rext field, and the opmove table
contain the number field (Long type) you can use something like

SELECT oppro.opdivtype, oppro.oppronum, oppro.opstatus, oppro.opshipname,
oppro.oporgcity, oppro.oporgst, oppro.opconsnm, oppro.opconscty,
oppro.opconsst, oppro.oppickpdat, oppro.oppayrname, oppro.opbilltamt,
oppro.opbillporc, opmove.omtractor, opmove.omdriver1
FROM oppro INNER JOIN opmove ON Clng(oppro.oppronum) = opmove.ompronum
WHERE (((oppro.opstatus)<>"C") AND ((oppro.oppickpdat) Between [Start Date]
And [End Date]))
--
Good Luck
BS"D

I have a query for 2 tables where the join connects a text and numeric field.
I know that the data types should match. Can I run a query to convert the
text to numeric or vice versa and then use that query? If so, how do I run
the conversion. I cannot change the data type in the table as the entire
database does not belong to me, however I am allowed to query the data.

SELECT oppro.opdivtype, oppro.oppronum, oppro.opstatus, oppro.opshipname,
oppro.oporgcity, oppro.oporgst, oppro.opconsnm, oppro.opconscty,
oppro.opconsst, oppro.oppickpdat, oppro.oppayrname, oppro.opbilltamt,
oppro.opbillporc, opmove.omtractor, opmove.omdriver1
FROM oppro INNER JOIN opmove ON oppro.oppronum = opmove.ompronum
WHERE (((oppro.opstatus)<>"C") AND ((oppro.oppickpdat) Between [Start Date]
And [End Date]));

Any help would be appreciated.

Thanks,
Judy