DLookUp returning first record when I need a specific record 2

[christophercubitt]

Private Sub Contact_ID_Exit(Cancel As Integer)

Company_Name = DLookup("CompanyName", "Contacts", "[Contact_ID] = " & Contact_ID)

End Sub

I am getting the DLookUp to work, but its returning the first result in the table. My code is above. how do I make it more specific to access the Company Name of the Contact ID entered on the form?

 

[MajP]

VbaJock,
Why would anyone need to test this. It is well known that there is a big difference. As shown your code will throw an error with a null value in the control.
Company_Name = DLookup("CompanyName", "Contacts", "[Contact_ID] = '" & Contact_ID+"'")

But maybe MS will convince you that these two will act in very different ways.

Rules For (+):
Both expressions are numeric data types (Byte, Boolean, Integer, Long, Single, Double, Date, Currency, or Decimal): Add.
Both expressions are String: Concatenate.
One expression is a numeric data type and the other is any Variant except Null: Add.
One expression is a String and the other is any Variant except Null: Concatenate.
One expression is an Empty Variant Return the remaining expression: unchanged as result.
One expression is a numeric data type and the other is a String: A Type mismatch error occurs.
Either expression is Null: result is Null.

If both expressions are Variant expressions, the following rules apply:
If Both Variant expressions are numeric: Add.
Both Variant expressions are strings: Concatenate.
One Variant expression is numeric and the other is a string: Add.


Rules for (&)
Both expressions are numeric data types (Byte, Boolean, Integer, Long, Single, Double, Date, Currency, or Decimal): Concatenate a string.
Both expressions are String: Concatenate.
One expression is a numeric data type and the other is any Variant except Null: concatenate.
One expression is a String and the other is any Variant except Null: Concatenate.
One expression is an Empty Variant Return the remaining expression: returns the one non empty value.
One expression is a numeric data type and the other is a String: concatenates a string
Either expression is Null: a string of the first value.

If both expressions are Variant expressions, the following rules apply:
If Both Variant expressions are numeric: concatenate.
Both Variant expressions are strings: Concatenate.
One Variant expression is numeric and the other is a string: concatenate.

This is a lot easier to see in a table format, but it is clear that there is a big difference in many cases.

 

[vbajock]

Logic is logic. & and + mean the same thing, like I said, try it. + will concatenate any string value that & does. Like I said, test it. Here:


Public Function TestIt() As Date

Dim str As String

str = "john doe"
TestIt = DLookup("LastAccess", "tblUsers", "UserName = '" & str + "'")

End Function

Immediate window output:

?TestIt
12/10/2010

I

 

[dhookom]

I agree with theAceman that + and & are not the same when used in VBA or expressions. I use one or the other when I want different results. Open the immediate window (Press Ctrl+G) and try the following:

? "This" & Null 

? "This" + Null

Do you get the same results?


Duane
Hook'D on Access
MS Access MVP

 

[vbajock]

My comments were in regards to concatenation of strings. "&" and "+" behave differently when nulls are involved, if you know the difference, you can use the differences to your advantage, especially when looping thru tables.

 

[MajP]

My comments were in regards to concatenation of strings

No they were not limited to string datatypes. They were in regards to Ace Man's comments of changing this

Company_Name = DLookup("CompanyName", "Contacts", "[Contact_ID] = '" & Contact_ID+"'")

to

DLookup("CompanyName", "Contacts", "[Contact_ID] = '" & Contact_ID & "'")

you said

2 Aceman1 & and + are interchangeable, makes no difference which one you use

In this example they are clearly not interchangeable, because all control and field properties return a variant datatype regardless of the field properties. Which I thought was the whole point of the discussion. They are not interchangeable in the pertinent discussion because your notation will provide a runtime error when the contact_ID is null and Ace Man's code will not.

Sure there are cases when it makes no difference, but that sure does not make interchangeable. I provided from Microsoft all the cases when it does not provide the same results. And as we all pointed out, we understand the cases when they return the same results, but the entire point of this conversation is when they do not.

Being an expert with 10 years of experience, I would not expect you to change your ways, and your wasting your time trying to convince us you are right. However, I would not recommend this to novice users who do not fully understand all the differences. Provide them the most reliable solution, that is not going to get them in trouble.

BTW if you said
"[Contact_ID] = '" + NZ(Contact_ID,"") + "'"
Then I would agree they were interchangeable because you are ensuring the control value is converted to an empty string when null

 

[boblarson]

I agree with those who say + and & are not interchangeable in VBA. But I have something to say about the single quotes. Many times Business names have single quotes in them (Like Bob's Restaurant, Sam's Place, etc). So using single quotes when looking for company names is potentially an error producer.

So, from the original post, I would suggest using this instead:

Company_Name = DLookup("CompanyName", "Contacts", "[Contact_ID] = " & Chr(34) & Contact_ID) & Chr(34)

You could use triple double quotes """ but I find that to be just confusing and using Chr(34) makes it a lot easier to read, especially to newer users.


Bob Larson
FORMER Microsoft Access MVP (2008-2009, 2009-2010)
Free Tutorials/Samples/Tools:

http://www.btabdevelopment.com

 

[MajP]

I actually use standard functions in my code library to do this. Saves a lot of writing and errors.

Function SQLDate(varDate As Variant) As Variant
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

Public Function sqlTxt(varItem As Variant) As Variant
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    sqlTxt = "'" & varItem & "'"
  End If
End Function

Making the above
"[Contact_ID] = " & sqlTxt(Contact_ID)

"[Contact_Date] = " & sqlDate(someControl)

 

[TheAceMan1]

Lets not forget [blue]substrings[/blue] using double quotes:

[blue]   Dim DQ As String, Cri As String
   
   [purple][b]DQ[/b][/purple] = """"
   Cri = "[Contact_ID] = " & [purple][b]DQ[/b][/purple] & Contact_ID & [purple][b]DQ[/b][/purple]
   Debug.Print Cri
   
   Company_Name = DLookup("CompanyName", "Contacts", Cri)[/blue]

Another example:

[blue]   Dim DQ As String, txtTest As String
   
   [purple][b]DQ[/b][/purple] = """"
   txtTest = [purple][b]DQ[/b][/purple] & "Sammy[red][b]'[/b][/red]s Diners[red][b]'[/b][/red] by Tommy[red][b]'[/b][/red]s" & [purple][b]DQ[/b][/purple]
   Debug.Print txtTest[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]