Using Dlookup to find value in table

[chizzy42]

Hi all hope all is good. I seem to be getting an issue with Dlookup checking a table for a value and returning the correct response. I had a similar question recently and the dbGuy helped and noticed i had missed single quotes for a string search. I've tried various iterations of :

Dim RPSno As String
Dim str As String
'str = Me.Text22
'MsgBox ("Text22 " & str)
RPSno = Nz(DLookup("[cell]", "tblSkills", "[cell]=' " & Forms![frmcellskills]![Text24] & " ' "), 0)
'RPSno = Nz(DLookup("[Cell]", "tblSkills", " [Cell] =  ' " & str & " ' "), 0)
    If RPSno <> 0 Then
    MsgBox (RPSno)
     MsgBox ("number DOES exist in database")
        'Cancel = True

        Else
        MsgBox (RPSno)
        MsgBox ("Number Does NOT exist in the Database")

End If

but all i get is 0 returned no matter what i do , so i thought id try a different approach and tried isNull with a constant value to try and cut out any variable ambiguity.

If IsNull(DLookup("[Cell]", "tblSkills", "[Cell] = ' Cell6' ")) Then

    MsgBox (" No Record Found")

Else
MsgBox ("Need to add it")

End If

Again i cant seem to find a value of the field cell in table tblSkills(the value just being cell1......cell9). I tried using another table with the cell field in it (data type short text) am i wearing the clown shoes again and missing something obvious.....any help would be gratefully received.

 

[theDBguy]

Can you show us a screenshot of your table with sample data?

 

[chizzy42]

Hi dbGuy thanks for the quick reply i've attached the rough db i'm playing with , the form that i'm using to search is frmCellSkills and i was trying the search using the buttons command19 and command26

 

[theDBguy]

Hi dbGuy thanks for the quick reply i've attached the rough db i'm playing with , the form that i'm using to search is frmCellSkills and i was trying the search using the buttons command19 and command26

Hi. Thanks for posting a sample db. I think I see your problem. Here's the code you're using:

RPSno = Nz(DLookup("[cell]", "tblSkills", "[cell]='X" & Forms![frmcellskills]![Text24] & "X' "), 0)

Please notice I inserted an "X" in place of spaces you were using in your code. Try taking out those spaces from your code.

 

[chizzy42]

Hi dbGuy i tried removing the spaces but got the same issue

'RPSno = Nz(DLookup("[cell]", "tblSkills", "[cell]='" & Forms![frmcellskills]![Text24] & "' "), 0)
RPSno = Nz(DLookup("[Cell]", "tblSkills", " [Cell] =  '" & str & "' "), 0)

 

[theDBguy]

Hi dbGuy i tried removing the spaces but got the same issue

'RPSno = Nz(DLookup("[cell]", "tblSkills", "[cell]='" & Forms![frmcellskills]![Text24] & "' "), 0)
RPSno = Nz(DLookup("[Cell]", "tblSkills", " [Cell] =  '" & str & "' "), 0)

All I did with this one is take out the spaces for you. See what happens now.

 

[chizzy42]

hi dbguy , command19 button has a data mismatch error13 and the command26 button still doesnt return a value

 

[theDBguy]

hi dbguy , command19 button has a data mismatch error13 and the command26 button still doesnt return a value

Hi. I only modified command19, so you can see how I might remove the spaces. You now get a type mismatch error because you're trying to compare "cell2" (string/text) with a number (0) in your code with:

If RPSno <> 0 Then

 

[chizzy42]

Thanks for that dbGuy for bringing that to my attention :

RPSno = Nz(DLookup("[cell]", "tblSkills", "[cell]='" & Forms![frmcellskills]![Text24] & "' "), "")
'RPSno = Nz(DLookup("[Cell]", "tblSkills", " [Cell] =  ' " & str & " ' "), 0)
    If RPSno <> "" Then

just realised i was tring to compare a string with number....much appreciated. Just trying to get a few things under my belt before i try converting a spreadsheet to a db as a learning project and as pat hartman said ill delete and start again and keep it clean.

thanks again

 

[theDBguy]

Thanks for that dbGuy for bringing that to my attention :

RPSno = Nz(DLookup("[cell]", "tblSkills", "[cell]='" & Forms![frmcellskills]![Text24] & "' "), "")
'RPSno = Nz(DLookup("[Cell]", "tblSkills", " [Cell] =  ' " & str & " ' "), 0)
    If RPSno <> "" Then

just realised i was tring to compare a string with number....much appreciated. Just trying to get a few things under my belt before i try converting a spreadsheet to a db as a learning project and as pat hartman said ill delete and start again and keep it clean.

thanks again

You're welcome. @Pat Hartman and I were happy to assist. Good luck!