[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

dlookup with more than 1 criteria - proper syntax

Posted on 2014-02-05
12
Medium Priority
?
414 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1200 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 400 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 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 400 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

650 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