Dlookup for Two Values?

[Fattire]

I'm trying to validate two values on a form against the database to make sure that they exist, check number and check amount - if they are the same as what's in the table then open up a form, else a msgbox saying the data entered is wrong.

I tried a sql statement but the window opens even if the data from the form is not correct.

    CHECK_NUMBER = Me.txtChkNum
    CHECK_AMOUNT = Format(Me.txtChkAmount, "########0.00")
    CHKAMT = CVar(CHECK_AMOUNT)
    
    sqlstr = ""
    sqlstr = sqlstr & " SELECT tblMainTable.CHECK_NUMBER as CHKNUMB, tblMainTable.AMOUNT "
    sqlstr = sqlstr & " FROM tblMainTable "
    sqlstr = sqlstr & " WHERE (((tblMainTable.CHECK_NUMBER)= " & CHECK_NUMBER & ") "
    sqlstr = sqlstr & " AND ((tblMainTable.AMOUNT)= " & CHECK_AMOUNT & ")) "
    
    'Debug.Print sqlstr
    
    rec2.Open sqlstr, con, adOpenDynamic

    If IsNull(rec2!AMOUNT) Then
        MsgBox "Check does not exist in database, please re-enter.", vbOKOnly, "Ooops"
    Else

    stDocName = "frmSpecialHandling"
    DoCmd.OpenForm stDocName, , , "[CHECK_NUMBER] = " & Me.txtChkNum

Is there a better way to work with an empty recordset to make an event?

 

[randy700]

Something like...

If IsNull(DLookUp("Check_Number", "tblMainTable", "Check_Number = " & txtCheckNumber) Then
   MsgBox "Check does not exist."
Else
   If IsNull(DLookUp("Check_Amount", "tblMainTable", "Check_Number = " & txtCheckNumber & " AND Check_Amount = " & txtCheckAmount) Then
      MsgBox "Check exists, amount is incorrect."
   Else
      rec2.OpenRecordset(strSQL)
   End If
End If

NOTE: Typed but not tested.


Randy

 

[Fattire]

Randy, thank you! I just can't this thing to work still though...

I've tried just about every which way to program it and it gives me Run Time Error 13, I'm assuming because it probably can't handle two criteria arguments.

 

[Remou]

Is Check_Number text field? If so, you need single quotes:

[tt]If IsNull(DLookUp("Check_Number", "tblMainTable", "Check_Number = '" & txtCheckNumber & "'") Then[/tt]

 

[Zion7]

Yes, as Remou said, err(13) is a data type mismatch.
If it's not what Remou suggested then, maybe it's because you're converting check amount,
to a string? CHECK_AMOUNT = Format(Me.txtChkAmount, "########0.00")

Maybe this
CHECK_AMOUNT = CCur(Me.txtChkAmount)


BTW, using Randy's suggestion, opening a recordset
is unnecessary.
If I understand the logic correctly, then simply this

If IsNull(DLookUp("Check_Amount", "tblMainTable", "Check_Number = " & txtCheckNumber & " AND Check_Amount = " & txtCheckAmount) Then
MsgBox "Check exists, amount is incorrect."
Else
stDocName = "frmSpecialHandling"
DoCmd.OpenForm stDocName, , , "[CHECK_NUMBER] = " & Me.txtChkNum
End If

 

[Fattire]

Yeah thanks guys - it can take an "AND" statement...appreciate it.

I was placing my )) in the wrong place.

 

[Zion7]

Yes, The aggregate function, WHERE statements, conform
to standard SQL, statements.
You can use,

DLookUp("pkID","tblSale","Amount IN(56,99,908)")
DLookUp("pkID","tblSale","Date Between #1/1/05# AND #2/1/05#")
DLookUp("pkID","tblSale","Contact Like '[a-c]*'" & _
" OR IN('Donald',Smith')")

..as many AND/OR clauses as you like.
etc...

 

[Remou]

It is also possible to look up a function:

[tt]=DLookUp("Left(Contact,1)","tblSale","Contact Like '[a-c]*'")[/tt]

With other domain aggregate functions, too:

[tt]=DSum("[Amount] * 1.25","tblSale","Date Between #1/1/05# AND #2/1/05#")[/tt]

Note: American date format.