Using Dlookup to find value in table (1 Viewer)

[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.

 

[Pat Hartman]

Regarding your form - Go back to square 1. Do not pass Go. Do not collect $200.

Every control you add to a form needs a meaningful name. If you don't give it one, you will never be able to remember what text24 on any given form means. This is not something you will go back and fix later. Do not deceive yourself. Fix it now or live with it forever. The more code you have, the more trouble it will be to fix the crap.

PS, when you are writing code in a form/report's class module that references objects/properties/methods of that form/report, use Me. rather than Forms!someformname! to write code. This will give you intellisense which is very helpful.

Me.SomeGoodName rather than Forms![frmcellskills]![Text24]

 

[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!