asked on

SQL WHERE with Multiple conditions

I have a SQL statement that is working:
sql = "select * from tblOrderDetail Where tblOrderDetail.DetailID = (select MAX(DetailID) from tblOrderDetail)"

I would like to add an additional condition, the OrderID field must match.
dim oid as Long
oid = Me.OrderID.Value

I have tried the following but it's not working;
sql = "select * from tblOrderDetail Where tblOrderDetail.DetailID = (select MAX(DetailID) from tblOrderDetail) AND tblOrderDetail.OrderID = ('" & oid & "')"

Any suggestions?

Raja Jegan R

>> I have tried the following but it's not working;

Syntax is correct. Kindly explain what you meant by not working either not fetching results or error while executing or ...

And try this one out..

select * from tblOrderDetail
Where DetailID = (select MAX(DetailID) from tblOrderDetail)
AND OrderID = ('" & oid & "')"

Gary Samuels

ASKER

The following did not work. I'm getting the same run time error: Data type mismatch.
So, I assume it's a syntax error. Please note that the final variable (oid) is a Long data type.

Dim oid As Long
oid = Me.OrderID.Value
 
 
sql = "select * from tblOrderDetail Where DetailID = (select MAX(DetailID) from tblOrderDetail)AND OrderID = ('" & oid & "')"
 
Set currentDetail = CurrentProject.Connection.Execute(sql)

Open in new window

shru_0409

your query is right i m not seem any wrong in your query

ASKER CERTIFIED SOLUTION

Markus Fischer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gary Samuels

ASKER

I have to attend a meeting that will last all day so I will try it as soon as I can, thanks

shru_0409

sql = "select * from tblOrderDetail Where DetailID = (select MAX(DetailID) from tblOrderDetail)AND cast (OrderID as char) = ('" & oid & "')"

Raja Jegan R

>> Data type mismatch.

AND tblOrderDetail.OrderID = ('" & oid & "')"

Why are you appending a single quote ' before and after your oid variable.
Single quotes before and after '1' makes this as a string instead of 1

Kindly remove single quotes and Open and Close braces so that it look like

AND tblOrderDetail.OrderID = " & oid

Gary Samuels

ASKER

Perfect, thanks for seeing what I really wanted