Solved

dlookup with more than 1 criteria - proper syntax

Posted on 2014-02-05
12
404 Views
Last Modified: 2014-02-06
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
Comment
Question by:Karen Schaefer
12 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 300 total points
ID: 39837558
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
 

Author Comment

by:Karen Schaefer
ID: 39837564
no they are text string

alpha numeric data types

thanks REy, however, that did not work.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39837572
<however, that did not work. >

how it did not work?

give more details in your comments.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:Karen Schaefer
ID: 39837600
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39837630
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
 
LVL 10

Assisted Solution

by:Gozreh
Gozreh earned 100 total points
ID: 39837749
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
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
ID: 39838134
You also need to:

Dim DupCheck As Variant

/gustav
0
 

Author Comment

by:Karen Schaefer
ID: 39839360
as you can see I made the suggested changes and I am still getting error.

errmsg
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39839384
i don't think what you used is the same as this


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




.
0
 

Author Comment

by:Karen Schaefer
ID: 39839408
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39839501
a piece of advice,

read carefully the comments given to you.
0
 

Author Closing Comment

by:Karen Schaefer
ID: 39839506
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question