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

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

Clifford Bass via AccessMonster.com

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

Marshall Barton

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

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
 
C

Clifford Bass via AccessMonster.com

Hi Marshall,

I would not recommend the second method as it will produce bad results
if TRANS_NUMBER contains leading or trailing zeroes.

In general my preference is not to use functions in joins as they either
will not show in the query designer, therefore giving a false view of the
query; or the query designer will refuse to show and then you are stuck with
just the SQL view.

Clifford Bass
 
M

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!
 
M

MM

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

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!
 
M

Marshall Barton

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

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

Top