DLookup using two fields 1

[davejackson]

Hi,

I am trying to find the value of my key field based on two fields entered seperately. I have the following code where my table City-Bank contains three fields, Bank, City, BankID.

Result = DLookup("[BankID]", "[City-Bank]", "[Bank] = '" & Bank2 & "' And [City] = " & City2)

I am retrieving Bank2 and City2 from a form but get the error: 'The object doesn't contain the Automation object 'New York'' This is obviously one of my cities on the table. Am I barking up the wrong tree? It seems like it should be easy enough but in VB it is giving me problems.

Thanks in advance,

Dave

 

[LonnieJohnson]

Looks like your missing your tick marks (') before and after the variable for City2. ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[davejackson]

Lonnie,
Thanks for the reply but it still doesn't seem to be working. I can't place (')s in those places and when I play around with them, I either receive the same error or a syntax error. Any other ideas pleeeeease?

Dave

 

[LonnieJohnson]

Is this how you were adding the tick marks to City2?

Result = DLookup("[BankID]", "[City-Bank]", "[Bank] = '" & Bank2 & "' And [City] = '" & City2 & "'&quot

Putting one tick mark after the equal sign that follows [City] and one tick mark inside of qoutation marks followed by an ampersand (&) after the variable City2.

Hope this helps... B-)
ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[davejackson]

Still don't really understand why it works but it does. Thanks a million.
dave

 

[MichaelRed]

Result = DLookup("[BankID]", "[City-Bank]", "[Bank] = " & Chr(34) & Bank2 & " And [City] = & Chr(34) & City2 & Chr(34))

The below are other examples, fromthe immediate window.

? dlookup("[DateN]", "tblPMSReplace", "[Id] = '456789' AND [DateN] = '4100'&quot
4100

? dlookup("[DateN]", "tblPMSReplace", "[Id] = '456789' AND [EntryDate] = #4/1/00#&quot
4100


The last is using a function to return the single value.
It reeally should have (at least) the value arguments for DLookUp as call arguments to the function, but I'm not really sure you would use it this way.

Public Function basDLookUpTest()

'    [tblPMSReplace]
'    [EntryDate]    [Id]    [DateN]
'    1/1/00         123456  1100
'    1/1/00         123456  1100
'    1/1/00         123456  1100
'    1/1/00         123456  1100
'    1/1/00         123456  1100
'    1/1/00         123456  1100
'    4/1/00         456789  4100
'    5/1/00         456789  5100
'    6/1/00         456789  6100
'    7/1/00         456789  7100

    Dim MyId As String
    Dim MyEntryDate As Date

    MyId = "456789"
    MyEntryDate = #4/1/2000#
    Dim tmpVar As Variant


    tmpVar = DLookup("[DateN]", _
                     "tblPMSReplace", _
                     "[Id] = " & Chr(34) & MyId & Chr(34) & _
                     " AND [EntryDate] = " _
                     & Chr(35) & MyEntryDate & Chr(35))

    basDLookUpTest = tmpVar

End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over