Unexpected results with DLookup
Windows 10 
Access 2013 32bit
The code below is a "one time" module intended to audit cell numbers in a table to determine if they are in fact numbers listed with mobile carriers in one of the northern regions in Nevada, area code 775. If not, they are to be moved to the landline field of the table and the cell field set to a zero-length string. If the cell "area code - prefix" isn't listed, I'm expecting DLookup to return a Null but never does. I've included a snippet of the tables screenshot at the bottom so you can see the form of the table being searched.
[/CODE]
Option Compare Database
Option Explicit
Public Function Cells()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=* =*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' CELL NUMBER AUDIT...............
' Loop through the mobile numbers listed in the Registry. If they're not found in the
' list of area code 775 mobile carriers, assume they are land lines move to the land
' line column and clear the Cell field.
'
' The format of the tblMobilePrefix is a single field of the form xxx-xxx
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=* =*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim rsReg As DAO.Recordset
Dim strTemp As String
Set rsReg = DBEngine(0)(0).OpenRecordset("QRegistry")
With rsReg
.MoveFirst
While Not .EOF
If Not IsNull(!Cell) Then
If left(!Cell, 5) = "(775)" Then
strTemp = Mid(!Cell, 2, 3) & "-" & Mid(!Cell, 7, 3) 'strTemp of the form xxx-xxx
If IsNull(DLookup("MobilePrefix", "tblMobilePrefix", "[MobilePrefix] = " & strTemp)) Then
'Current number not listed as being a mobile phone
.Edit
!LandLine = !Cell
!Cell = ""
.Update
End If
End If
End If
.MoveNext
Wend
End With
rsReg.Close
Set rsReg = Nothing
End Function
[/CODE]
Reply With Quote
Originally Posted by GraeagleBill
