DLookup code not working

[lukekelly]

Hi all,

I have this code run through a macro to open a project:

Function OpenAProject()
  
  Dim p
  p = InputBox("Please enter Project Number", "Open a Project")
  If p > "" Then
    p = UCase(p)
    If DLookup("[Project Number]", "Projects", "[Project Number] = '" & p & "'") = p Then
      DoCmd.OpenForm "Projects", , , "[Project Number] = '" & p & "'"
    Else
      MsgBox "Sorry - " & p & " isn't a valid project number", vbExclamation, "CPAMS"
    End If
  End If
  
End Function

I have a table called Projects, the "UID" is the Project Number column, and also have a form called Projects. The above code produces a box to prompt for the Project number and then to open that record up. However im getting an error:

"Data type mismatch in criteria expression"

and the following line is highlighted as the problem:

If DLookup("[Project Number]", "Projects", "[Project Number] = '" & p & "'") = p Then

I'm perplexed as I'm sure I have used this code before in other projects. If anyone has any pointers or could assist me that would be great.

Thanks


Luke

[maximus]

DLookup("[Project Number]", "Projects", "[Project Number] = '" & p & "'") = "p"

try this

[lukekelly]

Hi Maximus thanks for your reply,

I have tried the quotations around the p with still no effect,

Is there anything else you could possibly suggest?

Thanks

Luke

[maximus]

Project Number is that a Field with field Type text or integer.

[lukekelly]

Hi Maximus,

Its an autonumber integer

Thanks

[lukekelly]

Formatting wouldn't be an issue would it?

[maximus]

Now here are some issues that you need to look into:

Sure it can be an Issue is that is a autonumber, I would have my lookup as :
DLookup("[Project Number]", "Projects", "[Project Number] =" & p ) = p

[lukekelly]

Hi Maximus,

Yes I now realised that the last time I used this code it was for a text UID where the project number was assigned using another piece of code,

I have tried your suggestion thanks, It takes me a step further however I'm just getting a runtime error now - "You cancelled the previous operation"

Looking into it now

Thanks

[maximus]

I would rather have the code like this:

I have assumed:
1) Project Number is a integer
2) P has to be integer
3) Search whether The projec number exist.
4) if yes open a form where Project Number = intp
5) Else error msgbox.

Dim intp as integer
Dim intCount as Integer

intp=InputBox("Please enter Project Number", "Open a Project")

intCount=iif(isNull(Dcount("[Project Number]", "Projects", "[Project Number] = " & intp)),0,Dcount("[Project Number]", "Projects", "[Project Number] = " & intp))

if intCount=0 then
MsgBox "Sorry - " & intp & " isn't a valid project number", vbExclamation, "CPAMS"
else
DoCmd.OpenForm "Projects", , , "[Project Number] = " & intp

end if

[lukekelly]

Hi Maximus, was half way there

Works a treat, thanks a lot for your time and help.

Luke