Multiple criteria on StLinkCriteria

[Guest]

Hello,

I created a table-based form that contains records and a button that opens a
query-based report that filters based on one criteria.

However, what I would like to do is to incude multiple criterias. I typed
the following code, but I keep getting "Type Mismatch". Can one use more
than one criteria when using StLinkCriteria os should I use a different
approach? Please see code below:

Private Sub Print_Slip_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "GT_Grant_Route_Trans"

stLinkCriteria = ("[Grant_Number]=" & "'" & Me![Grant_Number] & "'") And
("[Appl_Recvd_Date]=" & "'" & Me![Appl_Recvd_Date] & "'") And
("[Act_Type_Num]=" & "'" & Me![Act_Type_Num] & "'")
"
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

End Sub

Thank you in advance for your help

 

[Dirk Goldgar]

Hello,

I created a table-based form that contains records and a button that
opens a query-based report that filters based on one criteria.

However, what I would like to do is to incude multiple criterias. I
typed the following code, but I keep getting "Type Mismatch". Can
one use more than one criteria when using StLinkCriteria os should I
use a different approach? Please see code below:

Private Sub Print_Slip_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "GT_Grant_Route_Trans"

stLinkCriteria = ("[Grant_Number]=" & "'" & Me![Grant_Number] &
"'") And ("[Appl_Recvd_Date]=" & "'" & Me![Appl_Recvd_Date] & "'") And
("[Act_Type_Num]=" & "'" & Me![Act_Type_Num] & "'")
"
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

End Sub

Thank you in advance for your help

Yes, you can specify multiple criteria in the Where-Condition argument
(the "link criteria"), but you haven't done it correctly. You have
parentheses and quotes in the wrong places.

Your code wraps each of the three control values in single-quotes. That
implies that they are all text fields. If that's really so, then this
statement should work:

stLinkCriteria = _
"([Grant_Number]='" & Me![Grant_Number] & _
"') And ([Appl_Recvd_Date]='" & Me![Appl_Recvd_Date] & _
"') And ([Act_Type_Num]='" & Me![Act_Type_Num] & "')"

However, it wouldn't surprise me at all to find that a field named
"Appl_Recvd_Date" is actually a date/time field. If that's the case,
then you should format the value from the control as a date literal --
and for safety's sake, it would be a good idea to force it into
MM/DD/YYYY format. So if I'm right about that field, you should revise
the above statement to be more like this:

stLinkCriteria = _
"([Grant_Number]='" & Me![Grant_Number] & _
"') And ([Appl_Recvd_Date]=#" & _
Format(Me![Appl_Recvd_Date], "mm/dd/yyyy") & _
"#) And ([Act_Type_Num]='" & Me![Act_Type_Num] & "')"

It's also possible that one or both of the other fields is really a
numeric field, not text, in which case you'd need to remove the
single-quotes you're embedding around the control values.

 

[Guest]

Thanks Dirk,

It worked great.

--
Elyse

Hello,

I created a table-based form that contains records and a button that
opens a query-based report that filters based on one criteria.

However, what I would like to do is to incude multiple criterias. I
typed the following code, but I keep getting "Type Mismatch". Can
one use more than one criteria when using StLinkCriteria os should I
use a different approach? Please see code below:

Private Sub Print_Slip_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "GT_Grant_Route_Trans"

stLinkCriteria = ("[Grant_Number]=" & "'" & Me![Grant_Number] &
"'") And ("[Appl_Recvd_Date]=" & "'" & Me![Appl_Recvd_Date] & "'") And
("[Act_Type_Num]=" & "'" & Me![Act_Type_Num] & "'")
"
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

End Sub

Thank you in advance for your help

Yes, you can specify multiple criteria in the Where-Condition argument
(the "link criteria"), but you haven't done it correctly. You have
parentheses and quotes in the wrong places.

Your code wraps each of the three control values in single-quotes. That
implies that they are all text fields. If that's really so, then this
statement should work:

stLinkCriteria = _
"([Grant_Number]='" & Me![Grant_Number] & _
"') And ([Appl_Recvd_Date]='" & Me![Appl_Recvd_Date] & _
"') And ([Act_Type_Num]='" & Me![Act_Type_Num] & "')"

However, it wouldn't surprise me at all to find that a field named
"Appl_Recvd_Date" is actually a date/time field. If that's the case,
then you should format the value from the control as a date literal --
and for safety's sake, it would be a good idea to force it into
MM/DD/YYYY format. So if I'm right about that field, you should revise
the above statement to be more like this:

stLinkCriteria = _
"([Grant_Number]='" & Me![Grant_Number] & _
"') And ([Appl_Recvd_Date]=#" & _
Format(Me![Appl_Recvd_Date], "mm/dd/yyyy") & _
"#) And ([Act_Type_Num]='" & Me![Act_Type_Num] & "')"

It's also possible that one or both of the other fields is really a
numeric field, not text, in which case you'd need to remove the
single-quotes you're embedding around the control values.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Vancer]

Dirk Goldgar,
Hello, I used your code in my database and it worked well. I had to delete the underscores (_) though. Thanks, Vance Stewart