Two Criteria for opening new form

[Fireman1143]

I am trying to open a form ("frmpopupTaskDescript") with two (2) criteria. The first criteria works fine alone and the popup works as designed. My problem is how to specify an additional criteria (which is a number). I keep getting a type mismatch no matter how I set the quotes etc.

I have also changed the & in the DoCmd statement to AND with no success.

Thanks,
Fireman1143

==========================
Private Sub fldTaskDescription_DblClick(Cancel As Integer)
On Error GoTo Err_fldTaskDescription_DblClick

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria2 As Integer

stDocName = "frmpopupTaskDescript"

stLinkCriteria1 = "[fldTaskProjNo]=" & "'" & Me![fldTaskProjNo] & "'"
stLinkCriteria2 = "[fldTaskOrder] = " & Me.fldTaskOrder
DoCmd.OpenForm stDocName, , , stLinkCriteria1 & stLinkCriteria2

Exit_fldTaskDescription_DblClick:
Exit Sub

Err_fldTaskDescription_DblClick:
MsgBox Err.Description
Resume Exit_fldTaskDescription_DblClick
End Sub
=======================================

 

[TheAceMan1]

How are ya Fireman1143 . . . . .

Replace the [blue]DoCmd line[/blue] with the following:

[blue]DoCmd.OpenForm stDocName, , , [purple][b]stLinkCriteria1 & " AND " & stLinkCriteria2[/b][/purple][/blue]

See Ya! . . . . . .

 

[GingerR]

also, you don't dim stLinkCriteria1, you dim stLinkCriteria which you don't use, and you dim stLinkCriteria2 as an integer but it's a string.

and tho it doesn't make a different, you can make Criteria1 with fewer chuncks by putting the first single quote mark inside the first set of quotes:

stLinkCriteria1 = "[fldTaskProjNo] = '" & Me![fldTaskProjNo] & "'"

Why did you split the two 'criterias'? maybe because one item is a string and one is not? If so, that doesn't matter. Unless you're referencing one or both of these half-criterias later on, i'd just put them together:

stLinkCriteria = "[fldTaskProjNo] = ' " & Me![fldTaskProjNo] & "' and [fldTaskOrder] = " & Me.fldTaskOrder

Hope this helps.

g

 

[TheAceMan1]

A little rearranging & shortening of names gives you:

[blue]   Dim frmName As String, Criteria As String

   frmName = "frmpopupTaskDescript"
   Criteria = "[fldTaskProjNo] = '" & Me![fldTaskProjNo] & "' " & _
              "And " & _
              "[fldTaskOrder] = " & Me.fldTaskOrder
   DoCmd.OpenForm frmName, , , Criteria[/blue]

See Ya! . . . . . .

 

[Fireman1143]

Thanks GingerR and AceMan

My biggest problem is the single (') and double (") and where to use what.

I'll give this a try Monday morning.


Fireman1143

 

[GingerR]

Double quotes go around the entire statement.
Since double-quotes are used for that, use single quotes around text that say, if you were putting criteria in a query grid, you'd put double-quotes around.
No quotes go around numbers.
So whatever words you want to be parsed exactly as they are in the statement, leave in double-quotes.

So your example, you're going for something like this:

[Field1] = "Blah" and [Field2] = 1

In order for it to be in your sql statement in code, the entire thing has to be surrounded by double-quotes, and any words you want to be written exactly as you type them have to be in double-quotes,making any text fields inside change to single-quotes:

"[Field1] = 'Blah' and [Field2] = 1"

then since 'Blah' and the number 1 are gleaned from controls on your form, replace them:

"[Field1] = '" & [Control1] & "' and [Field2] = " & [Control2]

 

[PHV]

Single quotes for surrounding text
Hash (#) for surrounding date

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cmmrfrds]

You can also use the chr() function to assign the quotes. chr(39) = single quote and chr(34) = double quote.

In this example.
Criteria = "[fldTaskProjNo] = " & chr(39)
& Me![fldTaskProjNo] & chr(39) & _
" And " & _
"[fldTaskOrder] = " & Me.fldTaskOrder