Max Nested Query Problem

[Matt Brown]

Hi all,
I need some really quick help here:

Can anyone see any problem with the following:

SQL = _
"SELECT trans00.SUPP_CUST, trans00.PART, trans00.MFTR, trans00.TRANS_QTY," & _
"trans00.TRANS_DATE, trans00.SELL_PR " & _
"FROM trans00" & _
" where PART = '" & AktProductNo & "' " & _
" AND SUPP_CUST = '" & rs1!CustomerNo & "' " & _
" AND trans_date IN (SELECT MAX(TRANS_DATE) FROM trans00 WHERE PART = '" & AktProductNo & "' and SUPP_CUST = " & rs1!CustomerNo & ") "


The problem is the nested query line, it returns a "Too few parameters expected" error.

I have looked at the parameters and cannot see why its doing this.

One thing is the max transdate is not returning anything, is my syntax wrong?

Any ideas anyone, please.

Matt

 

[Jon K]

SQL = _
"SELECT trans00.SUPP_CUST, trans00.PART, trans00.MFTR, trans00.TRANS_QTY," & _
"trans00.TRANS_DATE, trans00.SELL_PR " & _
"FROM trans00" & _
" where PART = '" & AktProductNo & "' " & _
" AND SUPP_CUST = '" & rs1!CustomerNo & "' " & _
" AND trans_date IN (SELECT MAX(TRANS_DATE) FROM trans00 WHERE PART = '" & AktProductNo & "' and SUPP_CUST = " & rs1!CustomerNo & ") "


What is the data type of the field [SUPP_CUST]?

In the main query, it is treated as text (as its value is surrounded by single quotes). In the sub-query, it is treated as numeric (as its value is not surrounded by single quotes).
.

 

[Matt Brown]

Hi Jon k,

The data type for field SUPP_CUST is Text.

I take it that would cause a problem ?

Does the Max part of the sub query look correct?

Matt

 

[Jon K]

For text, its value needs to be surrounded by single quotes.

SQL = _
"SELECT trans00.SUPP_CUST, trans00.PART, trans00.MFTR, trans00.TRANS_QTY," & _
"trans00.TRANS_DATE, trans00.SELL_PR " & _
"FROM trans00" & _
" where PART = '" & AktProductNo & "' " & _
" AND SUPP_CUST = '" & rs1!CustomerNo & "' " & _
" AND trans_date IN (SELECT MAX(TRANS_DATE) FROM trans00 WHERE PART = '" & AktProductNo & "' and SUPP_CUST = '" & rs1!CustomerNo & "') "


In the SQL string, the values of the [Part] field are surrounded by single quotes, so the [Part] field must also be a text field. If the [Part] field is a numeric field, remove the four single quotes that surrounded its values.

.

 

[Pat Hartman]

Jet does not optimize subselects properly. You may find that the query will run much faster if you create a query to return the Max() values and join to that query.

 

[Matt Brown]

OMG!!
I have just found out that Sub Queries are not supported in MySQL 3.1.

I am using an ODBC driver to connect to a mySQL db which in turn queries our business system.

No wonder it wasn't working!!!