dlookup part of field value

cpampas

Registered User.
Local time
Yesterday, 16:22
Joined
Jul 23, 2012
Messages
221
myTable has a field called cPostal with these records :

2560-999
2399-142
2870-182
2999-600

I do have a dlookup that finds a match, but in case there is no match I would like to find a partial match for the first 4 characters, so let ´s say I am looking for the string
2870-000
since there is no match, I ' d like find the third record wich has the partial match for the first 4 characters (2870-182)

Code:
fString="2870"
distritoID = DLookup("[distritoID]", "myTable", "left([cPostal],4) = """ & fString & """")

Is this posible or should I try a DAO.recordset ?
 
It would but it would only display the first.

I would start by creating a query with your left([cPostal],4) As ShortPCode Then create a recordset

Code:
Dim R As DAO.Recordset
Dim fString As String

On Error GoTo HandleErr
fString="2870"
    Set R = CurrentDb.OpenRecordset("SELECT * FROM yourquery WHERE [ShortPCode]='" & fString & "'", dbOpenSnapshot)
    If R.RecordCount <> 0 Then
     ' Do Something
    End If

R.Close
Set R = Nothing

HandleExit:
    Exit Sub

HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select
 
Last edited:
thanks for your code. I was trying to avoid the DAO. recordset, mainly because by finding the first match would be just fine. I just cant find out how to do it with dlookup

DLookup("[distritoID]", "myTable", "left([cPostal],4) = """ & fString & """")
 
If you only want the first one I think you can simplify that to

DLookup("[distritoID]", "myTable", "cPostal Like '" & fString & "*'")
 
thanks for your code. I was trying to avoid the DAO. recordset, mainly because by finding the first match would be just fine. I just cant find out how to do it with dlookup

DLookup("[distritoID]", "myTable", "left([cPostal],4) = """ & fString & """")
You would code it like this:
Code:
If IsNull(YourFirstDLOOKUP) then
...here do your second DLOOKUP, which looks correct to me, as there is only one record where the left 4 is 2870
End if
 
Did you know in Access SQL you actually don't have to bother with double quotes? It will take single quotes too.
Try:
DLookup("[distritoID]", "myTable", "left([cPostal],4) = "'" & fString & "'")
 
The double quotes are used when you are not sure there is going to be a single quote in the String

Code:
If IsNull(YourFirstDLOOKUP) then
...here do your second DLOOKUP, which looks correct to me, as there is only one record where the left 4 is 2870
End if

The recordset was used because there could be more than one result and a dlookup will only return the first record
 
Good call, I forgot about that. If you allow quotes into your database (for example names like O'Donnell, it may be unavoidable).
 
The recordset was used because there could be more than one result and a dlookup will only return the first record
According to his screenshot there was one, and was OK with the first one anyway.
 
I remove them with a replace statment with '' in plan text note fields
 
I remove them now with a replace them with '' in the statment in plan text note fields
I like that. Even escaping them is better to me than the way double quoted double quotes are to look at :) Good one.
 
Thank you all for your help, everything is working fine ;)
 
Without an imposed order, there really is no such thing as 'first' or 'last' in a table. DLookup will return a random record from a table when there's no criteria. It might always be the 'first' record in the table, but it will not necessarily always be the same record.
 

Users who are viewing this thread

Back
Top Bottom