Setting Max ID criteria in Ms Access SQL

Question

This query does not run at the beginning. Could someone please help look at what is wrong? If there is any other way to achieve this kindly suggest.

strSQL1 = "SELECT * FROM PharmSales WHERE HospitalNo='" & Me.txtRegNo & 
          "' And TDate = #" & Format(Me.txtTDate, "M\/dd\/yyyy") & 
          "# AND SalesItem1 = '" & Me.txtSalesItem1 & "' And
          PharmSalesID=
               (SELECT MAX(PharmSalesID) FROM PharmSales)"

    Set pr = db.OpenRecordset(strSQL1)

    With pr
    If Not .BOF And Not .EOF Then  'Ensure that the recordset contains records
    .MoveLast
    .MoveFirst
     If .Updatable Then  'To ensure record is not locked by another user
     .Edit  'Must start an update with the edit statement
     If IsNull(![TotalPaid]) = True And Me.txtGrand_TotalPay.Value >= Me.txtSalesAmt1.Value Then
     ![DispQty1] = Nz(![DispQty1] + Me.txtSalesQty1.Value, 0)
      .Update
           ElseIf IsNull(![TotalPaid]) = False And (Me.txtGrand_TotalPay.Value - Me.txtSalesAmt1.Value) >= (txtGrand_TotalFee - Me.txtGrand_TotalPay.Value + Me.txtSalesAmt1.Value) Then
     ![DispQty1] = Nz(![DispQty1] + Me.txtSalesQty1.Value, 0)
      .Update

     Else: MsgBox ("Insufficient balance!")
     End If
    End If
    End If

     pr.Close 
     Set pr = Nothing 
    Set db = Nothing
    End With

End Sub

why you mean doesnt run? Any error? Also use parameters other wise you are vulnerable to sql injection attack Check here: https://stackoverflow.com/questions/10352211/vba-ado-connection-and-query-parameters — Juan Carlos Oropeza, Aug 21 '17 at 15:34
Points to consider: are HospitalNo and SalesItem1 both text, because including them in single quotes treats them as Text; this will only return something if the given HospitalNo Date and SalesItem1 happen to match those which also has the max(PharmsalesID). — Jonathan Willcock, Aug 21 '17 at 15:56
Also your Title talks about setting max value; but your SQL selects based on the existing max. What actually are you trying to do! — Jonathan Willcock, Aug 21 '17 at 15:57

score 0 · Accepted Answer · answered Aug 21 '17 at 16:17

0

Your SQL checks multiple criteria, but your subquery doesn't have any of these criteria, so it will probably select a record that doesn't conform to your other criteria, causing your recordset to always be empty.

You need to add these criteria to the subquery, not the main query. Since the subquery will just return one record, you don't have to add them to both.

strSQL1 = "SELECT * FROM PharmSales" & _ 
          " WHERE PharmSalesID=" & _ 
               " (SELECT MAX(PharmSalesID) FROM PharmSales" & _ 
               " WHERE HospitalNo='" & Me.txtRegNo & _
               "' And TDate = #" & Format(Me.txtTDate, "M\/dd\/yyyy") & _
               "# AND SalesItem1 = '" & Me.txtSalesItem1 & "')"

answered Aug 21 '17 at 16:17

Erik A

31,639
12
42
67

Thank God for using you to proffer solution. I really appreciate you for pointing me to the right direction! – Liberty Crown Infotech Aug 21 '17 at 19:25
I do not wish to bother you again Sir. But is it possible to create a WHERE condition based on the previous record ID? – Liberty Crown Infotech Aug 21 '17 at 19:37
That's not an easy one. It might warrant another question, but please try to be explicit in what you want exactly. Do you want one lower than `Max()` for a certain `WHERE` condition? Are you just iterating through something, and want to do something with the previous record? – Erik A Aug 21 '17 at 19:54
Good Morning Mr Erik. There is no need to bother you further, I was actually able to pick up the previous record value based on the subsequent conditions in the expression. For the record it is a value one lower than max(). Thank you once again. – Liberty Crown Infotech Aug 22 '17 at 09:47

Setting Max ID criteria in Ms Access SQL

1 Answers1