Multiple WHERE clauses in docmd.openform

[Steve@trop]

I'm trying to open a form to a specific record using the criteria in the docmd.openform command. I know the criteria are correct individually because they both work when I use them one at a time but when I try to put them together I get errors. Here is how I have it together in it's latest form:

     DoCmd.OpenForm "CTableSearchF", , , "[ctable].[lastname] = '" & LastNamestr & "'" And "[ctable].[phonenumber] = '" & PhoneNumberstr & "'"

When I run it I get a "type mismatch" error. If I pull out one or the other criteria (before or after the "and") it works fine but I need both criteria to open the correct record.

I know it's probably something really simple like a different type of quote or something. I know it's really close because the criteria work OK by themselves it's just when I try to put them together that it fails.

As usual any type of help you can give on this is greatly appreciated!

 

[pbaldy]

You've made a common mistake. Delete the double quotes on each side of And.

 

[ozinm]

I think you've got a " in the wrong place.

try this:

 DoCmd.OpenForm "CTableSearchF", , , "[ctable].[lastname] = '" & LastNamestr & "'And [ctable].[phonenumber] = '" & PhoneNumberstr & "'"

 

[pbaldy]

I think you've got a " in the wrong place.

try this:

 DoCmd.OpenForm "CTableSearchF", , , "[ctable].[lastname] = '" & LastNamestr & "'" "And [ctable].[phonenumber] = '" & PhoneNumberstr & "'"

That will fail as well. It could work if you threw an & in there, but the extra concatenation isn't necessary.

 

[ozinm]

That will fail as well. It could work if you threw an & in there, but the extra concatenation isn't necessary.

Yep sorry. Half asleep!.

This should do:

DoCmd.OpenForm "CTableSearchF", , , "[ctable].[lastname] = '" & LastNamestr & "' And [ctable].[phonenumber] = '" & PhoneNumberstr & "'"

Also it might be worth chucking in a couple of nz's in case you have null values in the vars.

 

[Steve@trop]

Thanks all for your help. I tried the last one first and it worked.

About the "nz": I'm curious (how would that work?) but pretty sure I won't need it in this case. This code is part of a script that opens an existing record if the user attempts to create a new record that duplicates one that already exists in the table (violating a table index). The violation won't occur unless both fields have a value that matches an existing record so neither should be null.

Thanks again!

Steve

 

[ozinm]

Thanks all for your help. I tried the last one first and it worked.

About the "nz": I'm curious (how would that work?) but pretty sure I won't need it in this case. This code is part of a script that opens an existing record if the user attempts to create a new record that duplicates one that already exists in the table (violating a table index). The violation won't occur unless both fields have a value that matches an existing record so neither should be null.

Thanks again!

Steve

so Nz can be used to return a value if a variable (or function / calculation) returns a null.
So you can do something like this:

DoCmd.OpenForm "CTableSearchF", , , "[ctable].[lastname] = '" & Nz(LastNamestr,"Name Unknown") & "'" And "[ctable].[phonenumber] = '" & Nz(PhoneNumberstr,"Phone Unknown") & "'"

Also when concatenating strings together it's sometimes useful to understand the difference between the + and & operators.

The + will join strings together but if one of the variables is null the entire result will be null.
The & will join strings together and ignore any nulls.

This can be useful as a quick way to avoid adding extra spaces, commas etc.

eg

FirstName = "John"
MiddleNames = NULL
LastName = "Smith"

'This will display "John◊Smith" with only one space
debug.print FirstName & Nz("◊" + MiddleNames , "") & "◊" & LastName
'This is because " " + Middlenames = NULL

'This will display "John◊◊Smith" with two spaces
debug.print FirstName & "◊" & MiddleNames &  "◊" & LastName

NB: I've replaced the space characters with ◊'s to make it easier to see.

 

[Falcone203]

Dim st1 As Integer
Dim st2 As Integer

If (IsNull(Me![Sub Item])) = False Then
st1 = Me![Sub ID]
st2 = Me![IID]

If (IsNull([st1])) Then
'Do Nothing
Else
DoCmd.OpenForm "43 FIRE DEPT subform2", acNormal, , "[43 fire dept].id = " & st1 & "And [tblfae].FAIDFK = " & st2

End If
End If

 

[pbaldy]

Dim st1 As Integer
Dim st2 As Integer

If (IsNull(Me![Sub Item])) = False Then
st1 = Me![Sub ID]
st2 = Me![IID]

If (IsNull([st1])) Then
'Do Nothing
Else
DoCmd.OpenForm "43 FIRE DEPT subform2", acNormal, , "[43 fire dept].id = " & st1 & "And [tblfae].FAIDFK = " & st2

End If
End If

Are you asking a question or is this an answer to a 5 year old question?