Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

dlookup with more than 1 criteria - proper syntax

I need to have two sets of criteria, what is the proper syntax for a Dlookup statement.

DupCheck= dlookup("IdInvoice", "tblinvoice", "InvoiceNumber=" & gInvID & " And " "ContractNumber = " & gContractID & ")"


what is wrong with my syntax.

K
0
Karen Schaefer
Asked:
Karen Schaefer
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
try this, assuming InvoiceNumber, ContractNumber are Number data type


DupCheck= dlookup("IdInvoice", "tblinvoice", "InvoiceNumber=" & gInvID & " And ContractNumber = " & gContractID)


if not

DupCheck= dlookup("IdInvoice", "tblinvoice", "InvoiceNumber='" & gInvID & "' And ContractNumber = '" & gContractID & "')"
0
 
Karen SchaeferAuthor Commented:
no they are text string

alpha numeric data types

thanks REy, however, that did not work.
0
 
Rey Obrero (Capricorn1)Commented:
<however, that did not work. >

how it did not work?

give more details in your comments.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Karen SchaeferAuthor Commented:
Here is my code,   I attempting determine duplicate invoices on enter of an invoice #.

the Dlookup does not accept the syntax - no error message - does not compile.

If I try using rs.recordcount on a sql string then I get  typemismatch, however, when I execute the sql string as query it runs without error.

Feel free to modify either method to determine if the invoice # is a duplicate per the Contractnumber.



Thanks,

Karen
Select IDInvoice, ContractNumber FROM TblInvoice WHERE (InvoiceNumber='10813') And (ContractNumber = "CO00105")

Open in new window


Private Sub InvoiceNumber_AfterUpdate()
Dim DupCheck As String
Dim x As Integer
Dim strSQL As String
Dim rs As Recordset

gInvID = Nz(Me.IDInvoice)
gContractID = CStr(Me.ContractNumber)
'DupCheck= dlookup("IdInvoice", "tblinvoice", "InvoiceNumber=" & gInvID & " And ContractNumber = " & gContractID & ")
strSQL = _
    "Select IDInvoice, ContractNumber" & _
    " FROM TblInvoice" & _
    " WHERE (InvoiceNumber=" & Chr(39) & gInvID & Chr(39) & ") And" & _
    " (ContractNumber = " & Chr(34) & gContractID & Chr(34) & ")"
    Debug.Print strSQL
Set rs = CurrentDb.OpenRecordset(strSQL)
    If rs.RecordCount = 0 Then
' Null value indicates no duplicate invoice found
    'If IsNull(DupCheck) Then
        End
     Else:
    
        Select Case MsgBox("Invoice#" & "" & gInvID & " " _
                           & vbCrLf & " "" has been used before for this contract. Do you wish to review the DUPLICATE invoice for more information!""" _
                           , vbYesNo Or vbCritical Or vbDefaultButton1, "Duplicate Invoice Found")
        
            Case vbYes
                DoCmd.OpenForm "FrmInvoiceEdit", acNormal, , DupCheck, acFormReadOnly
            Case vbNo
               'Clear Invoice Number box with duplicate invoice number being used
                Call MsgBox("Please enter a different Invoice Number.", _
                    vbCritical Or vbDefaultButton1, "Duplicate Invoice Found")
                Me.InvoiceNumber.SetFocus
                Me.InvoiceNumber = Null
        End Select
    End If
End Sub

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
you said "alpha numeric data types"

then why did you not use the second syntax I posted above for Text Data type of field?
0
 
GozrehCommented:
Karen you used in your code this
DupCheck= dlookup("IdInvoice", "tblinvoice", "InvoiceNumber=" & gInvID & " And ContractNumber = " & gContractID & ")

Open in new window

if its numeric data it should be fine, but you need to remove the last < & " >


Rey's second option was this
DupCheck= dlookup("IdInvoice", "tblinvoice", "InvoiceNumber='" & gInvID & "' And ContractNumber = '" & gContractID & "')"

Open in new window

Ray's option should work if its an alpha data type, you just need to fix last few digits to < & "'") >
0
 
Gustav BrockCIOCommented:
You also need to:

Dim DupCheck As Variant

/gustav
0
 
Karen SchaeferAuthor Commented:
as you can see I made the suggested changes and I am still getting error.

errmsg
0
 
Rey Obrero (Capricorn1)Commented:
i don't think what you used is the same as this


DupCheck= dlookup("IdInvoice", "tblinvoice", "InvoiceNumber='" & gInvID & "' And ContractNumber = '" & gContractID & "'")




.
0
 
Karen SchaeferAuthor Commented:
Disregard previous post.

I realize a few issues.

1.I was looking at the RecordID (gInvID, when it should be InvoiceNumber (not the Autonumber field)

2.  Modified  Dim statements and the Dlookup to reference the correct value, however, I am sill getting issues

See Attached.
errmsg
Private Sub InvoiceNumber_AfterUpdate()
Dim DupCheck As Variant
Dim x As Integer
Dim strSQL As String
Dim rs As Recordset
Dim nInvNo As String

nInvNo = Nz(Me.InvoiceNumber)
gContractID = Me.ContractNumber
DupCheck = DLookup("InvoiceNumber", "tblinvoice", "InvoiceNumber='" & nInvNo & "' And ContractNumber = '" & gContractID)

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
a piece of advice,

read carefully the comments given to you.
0
 
Karen SchaeferAuthor Commented:
got the solution, thanks for the assistance.  Turns out once I change the correct value for InvoiceNumber not ID resolved the issue.

Thanks.

Private Sub InvoiceNumber_AfterUpdate()
Dim DupCheck As Variant
Dim x As Integer
Dim strSQL As String
Dim rs As Recordset
Dim nInvNo As Variant

nInvNo = Nz(Me.InvoiceNumber)
gContractID = Me.ContractNumber
DupCheck = DLookup("InvoiceNumber", "tblinvoice", "InvoiceNumber='" & nInvNo & "' And ContractNumber = '" & gContractID & "'")
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now