Solved

dlookup with more than 1 criteria - proper syntax

Posted on 2014-02-05
12
395 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 119

Accepted Solution

by:
Rey Obrero earned 300 total points
Comment Utility
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
Comment Utility
no they are text string

alpha numeric data types

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

Expert Comment

by:Rey Obrero
Comment Utility
<however, that did not work. >

how it did not work?

give more details in your comments.
0
 

Author Comment

by:Karen Schaefer
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
Comment Utility
You also need to:

Dim DupCheck As Variant

/gustav
0
 

Author Comment

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

errmsg
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
a piece of advice,

read carefully the comments given to you.
0
 

Author Closing Comment

by:Karen Schaefer
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now