query relationship text --> Number 18,6 type mismatch

  • Thread starter Thread starter MM
  • Start date Start date
M

MM

I need to pull a list of posted invoice with prepaid terms (sql below)
using two tables and INNER JOIN [ARTrans] ON
[InvoiceHeaderHistory].Invoice = [ARTrans].TRANS_NUMBER.

[InvoiceHeaderHistory].Invoice is 18,6 Number
[ARTrans].TRANS_NUMBER is text

How can I overcome the yype mismatch?

SELECT [InvoiceHeaderHistory].[CustID], [InvoiceHeaderHistory].
[CustNm], [InvoiceHeaderHistory].[Terms Desc] AS OPEN_T,
[InvoiceHeaderHistory].[InvoiceDate], [ARTrans].balance
FROM [InvoiceHeaderHistory] INNER JOIN [ARTrans] ON
[InvoiceHeaderHistory].Invoice = [ARTrans].TRANS_NUMBER
WHERE ((([InvoiceHeaderHistory].[TermsDesc]) Like "*pre*") AND
(([ARTrans].balance)>0));
 
MM said:
I need to pull a list of posted invoice with prepaid terms (sql below)
using two tables and INNER JOIN [ARTrans] ON
[InvoiceHeaderHistory].Invoice = [ARTrans].TRANS_NUMBER.

[InvoiceHeaderHistory].Invoice is 18,6 Number
[ARTrans].TRANS_NUMBER is text

How can I overcome the yype mismatch?

SELECT [InvoiceHeaderHistory].[CustID], [InvoiceHeaderHistory].
[CustNm], [InvoiceHeaderHistory].[Terms Desc] AS OPEN_T,
[InvoiceHeaderHistory].[InvoiceDate], [ARTrans].balance
FROM [InvoiceHeaderHistory] INNER JOIN [ARTrans] ON
[InvoiceHeaderHistory].Invoice = [ARTrans].TRANS_NUMBER
WHERE ((([InvoiceHeaderHistory].[TermsDesc]) Like "*pre*") AND
(([ARTrans].balance)>0));
Click to expand...

Try convert one to the type of the other. E.g.

ON InvoiceHeaderHistory.Invoice=Val(ARTrans.TRANS_NUMBER)
or
ON CStr(InvoiceHeaderHistory.Invoice)=ARTrans.TRANS_NUMBER
 
Hi,

     Remove the join and make it part of the where clause, using the Val()
function to convert the text item into a number:

..FROM [InvoiceHeaderHistory], [ARTrans]
WHERE ([InvoiceHeaderHistory].Invoice = Val([ARTrans].TRANS_NUMBER)
) AND ((([InvoiceHeaderHistory].[TermsDesc]) Like "*pre*") AND
(([ARTrans].balance)>0));

     Clifford Bass


I need to pull a list of posted invoice with prepaid terms (sql below)
using two tables and INNER JOIN [ARTrans] ON
[InvoiceHeaderHistory].Invoice = [ARTrans].TRANS_NUMBER.
Click to expand...
[InvoiceHeaderHistory].Invoice is 18,6 Number
[ARTrans].TRANS_NUMBER is text
Click to expand...
How can I overcome the yype mismatch?
Click to expand...
SELECT [InvoiceHeaderHistory].[CustID], [InvoiceHeaderHistory].
[CustNm], [InvoiceHeaderHistory].[Terms Desc] AS OPEN_T,
[InvoiceHeaderHistory].[InvoiceDate], [ARTrans].balance
FROM [InvoiceHeaderHistory] INNER JOIN [ARTrans] ON
[InvoiceHeaderHistory].Invoice = [ARTrans].TRANS_NUMBER
WHERE ((([InvoiceHeaderHistory].[TermsDesc]) Like "*pre*") AND
(([ARTrans].balance)>0));
Click to expand...
Click to expand...

Cool! It's golden!
 
MM said:
I need to pull a list of posted invoice with prepaid terms (sql below)
using two tables and INNER JOIN [ARTrans] ON
[InvoiceHeaderHistory].Invoice = [ARTrans].TRANS_NUMBER.
Click to expand...
[InvoiceHeaderHistory].Invoice is 18,6 Number
[ARTrans].TRANS_NUMBER is text
Click to expand...
How can I overcome the yype mismatch?
Click to expand...
SELECT [InvoiceHeaderHistory].[CustID], [InvoiceHeaderHistory].
[CustNm], [InvoiceHeaderHistory].[Terms Desc] AS OPEN_T,
[InvoiceHeaderHistory].[InvoiceDate], [ARTrans].balance
FROM [InvoiceHeaderHistory] INNER JOIN [ARTrans] ON
[InvoiceHeaderHistory].Invoice = [ARTrans].TRANS_NUMBER
WHERE ((([InvoiceHeaderHistory].[TermsDesc]) Like "*pre*") AND
(([ARTrans].balance)>0));
Click to expand...

Try convert one to the type of the other.  E.g.

ON InvoiceHeaderHistory.Invoice=Val(ARTrans.TRANS_NUMBER)
or
ON CStr(InvoiceHeaderHistory.Invoice)=ARTrans.TRANS_NUMBER
Click to expand...

Thanks for your post!
 
Good points Clifford, but I tend to work in SQL view so my
preference is the first method.
 
Back
Top