Dlookup not returning value

[vegemite]

Hey experts!
Im recycling code from another database I have where I ask the user to enter the project number they want to go to and it either goes to it or has an error message. The only difference is that the project number is a string in this new database (at least thats what I think the error is)...

Im asking the input box to return PrjNo
the table JobManagement has the ProjectNumber I want to match it to... I think it will just be something simple I cannot see for looking.....


Private Sub cmdGotoJob_Click()
'Runs the a query to prompt the user to type a Project Number
'If the Project Number does not exist a message box appears

On Error GoTo Handler
Dim PrjNo As String

PrjNo = InputBox("Type Project Number", "Search By Project Number")

If IsNull(DLookup("ProjectNumber", "tblJobManagement", _
"ProjectNumber = " & PrjNo & "")) Then
MsgBox "The specified Project Number does not exist.", vbExclamation, "Error!"
'DoCmd.Close
Else
DoCmd.OpenForm "frmJobManagement", acNormal, , "ProjectNumber = " & PrjNo
End If

Handler:
'Some Error message
If Err = 13 Then
Exit Sub
End If
End Sub

 

[theDBguy]

Hi. If project number is a text field, then you should enclose the variable in single quotes.

 

[vegemite]

I do dont I? Or am I missing somewhere?

 

[theDBguy]

I do dont I? Or am I missing somewhere?

You didn't; hence, my suggestion.

 

[vegemite]

Sorry if I am being thick.. Might need a little more guidance...

"ProjectNumber = " & PrjNo & ""))

 

[theDBguy]

Sorry if I am being thick.. Might need a little more guidance...

"ProjectNumber = " & PrjNo & ""))

First, I said, "if" ProjectNumber is a Text field, but you didn't confirm it. So, is it?

 

[vegemite]

AHA. The ball drops. Yes

 

[theDBguy]

AHA. The ball drops. Yes

Okay then, if it's a Text field, then this is what I meant:

..."ProjectNumber = '" & PrjNo & "'"))

Please notice the single quotes I added.

 

[vegemite]

Bummer! I thought that was it but it is like that :-(

PrjNo = InputBox("Type Project Number", "Search By Project Number")

If IsNull(DLookup("ProjectNumber", "tblJobManagement", _
"ProjectNumber = " & PrjNo & "")) Then
MsgBox "The specified Project Number does not exist.", vbExclamation, "Error!"
'DoCmd.Close
Else
DoCmd.OpenForm "frmJobManagement", acNormal, , "ProjectNumber = " & PrjNo
End If

 

[theDBguy]

Bummer! I thought that was it but it is like that :-(

PrjNo = InputBox("Type Project Number", "Search By Project Number")

If IsNull(DLookup("ProjectNumber", "tblJobManagement", _
"ProjectNumber = " & PrjNo & "")) Then
MsgBox "The specified Project Number does not exist.", vbExclamation, "Error!"
'DoCmd.Close
Else
DoCmd.OpenForm "frmJobManagement", acNormal, , "ProjectNumber = " & PrjNo
End If

Hi. But it is not like that though. Unless I am blind, I don't see a single quote in your code. All I see is a space after the equal sign and an empty string before the closing parens.

 

[vegemite]

lol. I am half blind and I couldnt see yours. Once I put the screen up to 150% I can but cant really determine which sides they go.

 

[vegemite]

Got them but am I still missing somewhere else?

Private Sub cmdGotoJob_Click()
'Runs the a query to prompt the user to type a Project Number
'If the Project Number does not exist a message box appears

On Error GoTo Handler
Dim ProjectNumber As String

ProjectNumber = InputBox("Type Project Number", "Search By Project Number")

If IsNull(DLookup("ProjectNumber", "tblJobManagement", _
"ProjectNumber = '" & ProjectNumber & "'")) Then
MsgBox "The specified Project Number does not exist.", vbExclamation, "Error!"
'DoCmd.Close
Else
DoCmd.OpenForm "frmJobManagement", acNormal, , "ProjectNumber = "" & ProjectNumber"
End If

 

[vegemite]

Sorry that was an old version I think I have it. I used to do this before kids 15 odd years ago I never should have accepted this job!!!

 

[theDBguy]

Sorry that was an old version I think I have it. I used to do this before kids 15 odd years ago I never should have accepted this job!!!

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[vegemite]

Thanks for your help

 

[theDBguy]

Thanks for your help

Hi. You're welcome.

 

[fertooos]

Hi. If project number is a text field, then you should enclose the variable in single quotes.