Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • 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 SchaeferBI ANALYSTAuthor 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Karen SchaeferBI ANALYSTAuthor 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 SchaeferBI ANALYSTAuthor 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 SchaeferBI ANALYSTAuthor 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 SchaeferBI ANALYSTAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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