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

[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));

 

[Marshall Barton]

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));

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

 

[MM]

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.

[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));

Cool! It's golden!

 

[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));

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

Thanks for your post!

 

[Marshall Barton]

Good points Clifford, but I tend to work in SQL view so my
preference is the first method.