OpenForm and syntax

[Dumferling]

I have the following code to open a form:
DoCmd.OpenForm "frmCStdContractPOPIA", acNormal, , "[CVName] = " & vblType & " AND [VersionNumber] = " & vblVersion
The variables work fine but it gives a syntax error. It looks right to me. What am I doing wrong?

 

[The_Doc_Man]

If either of your variables are strings, you need quoting marks. If BOTH were actually string types, it might look like this:

DoCmd.OpenForm "frmCStdContractPOPIA", acNormal, , "[CVName] = '" & vblType & "' AND [VersionNumber] = '" & vblVersion & "'"

When in doubt, assign some variable to hold the criteria as a completed string so that you can put in a breakpoint for debugging and do a manual Debug.Print (which would be visible in the Immediate window).

 

[Dumferling]

Thank you. The problem is now a data type mismatch. My first variable is a string, the second is an integer. Does that change the syntax?

 

[The_Doc_Man]

Remove the apostrophes for the case that is the integer. Literal numbers don't need quotes in this context, but strings do.

DoCmd.OpenForm "frmCStdContractPOPIA", acNormal, , "[CVName] = '" & vblType & "' AND [VersionNumber] = " & vblVersion

 

[Gasman]

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

 

[Dumferling]

That worked, thank you. Great to have the help to sort out what I find to be very confusing.

 

[Pat Hartman]

Best practice when building complex strings in VBA is to build them into a variable so you can use debug.print to find typos and logic errors.

Dim strWhere as String
strWhere = "[CVName] = '" & vblType & "' AND [VersionNumber] = " & vblVersion
DoCmd.OpenForm "frmCStdContractPOPIA", acNormal, , strWhere