stlinkcriteria syntax. multiple fields and wildcards

[NewsLeecher User]

I am struggling with stlinkcriteria and more than one selection
criteria

The underlying query has the two fields I am interest in:

Workflow
TFCompleteBy

The Workflow can contain several entries, but the one I am
particularly interested in is where the data is "In Evaluation Loop"

If I then use stdocname/stlinkcriteria as:

Dim stDocName As String
Dim stlinkcriteria As String

stDocName = "StatusPrintForEvalUnderReview"

stlinkcriteria = "[Workflow] = 'In Evaluation Loop'"
stDocName = "StatusPrintForEvalUnderReview"

DoCmd.OpenReport stDocName, acPreview, , stlinkcriteria

This works perfectly, previewing the report from the underlying
query with all documents at the status "In Evaluation Loop"

The documents can then be further subdivided as to priority, and the
TFCompleteBy field will do that. At the moment I am only using one
filter.

I therefore want either to pull out the documents where TFCompleteBy
= "PRT" or pull all of the documents out for an all up report.

I want to do this using wildcards as later I may want to add further
categories and keep the same underlying code.

I set up a combo box with two entries "PRT" and "*" for the
wildcard.

Forms![frm_TestGroupTestFormTracker]!eventfiltercombo

The combo box updates a textbox called txtevent (uses main form Open
event to ensure that it always contains data)

The main form is called

What I want to do is use stlinkcriteria to filter a report called by
a Command Button such that I can filter the report
on the Workflow and TF CompleteBy data. The one listed above works
fine, but I can neither get the wildcard option
working, or both criteria working together. I used to have a great
cribsheet which showed how to "add" string statements
as in >stlinkcriteria = "[Workflow] = 'In Evaluation Loop'"< to
data derived from combo boxes or text bokes, but I have lost it.

I suppose what I am trying to get is a working version of:

stlinkcriteria = "[Workflow] = 'In Evaluation Loop'" & "AND
TFCompleteBy = Forms![frm_TestGroupTestFormTracker]!txtevent"

Where it would work if the TFCompleteBy field was PRT or * so the
wildcard would work.

Any help appreciated, I have spen half the day googling this but
nothing appears to match what I am trying to do, and a lot of the
stlinkcriteria
questions appear to be around dates.

Thanks

J

 

[John W. Vinson]

I suppose what I am trying to get is a working version of:

stlinkcriteria = "[Workflow] = 'In Evaluation Loop'" & "AND
TFCompleteBy = Forms![frm_TestGroupTestFormTracker]!txtevent"

You need to include the LIKE operator, and concatenate the value of the form
reference rather than its name. You also need some blanks. Try


stlinkcriteria = "[Workflow] = 'In Evaluation Loop' AND
TFCompleteBy LIKE '" & Forms![frm_TestGroupTestFormTracker]!txtevent & "*'"

This will generate a string such as

[Workflow] = 'In Evaluation Loop' AND TFCompleteBy LIKE 'ABC*'
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[NewsLeecher User]

John,


When I use the following in the string criteria and put a Msgbox in to capture the string


stlinkcriteria = "[Workflow] = 'In Evaluation Loop' AND TFCompleteBy LIKE '" & Forms!
[frm_TestGroupTestFormTracker]!txtevent & "*'"

I get with the PRT selected in the combo:

[Workflow = 'In Evaluation Loop' AND TFCompleteBy Like 'PRT*'

[Workflow = 'Ready For Evaluation' AND TFCompleteBy Like 'PRT*'


I get with the * selected in the combo:

[Workflow = 'In Evaluation Loop' AND TFCompleteBy Like '**'

[Workflow = 'Ready For Evaluation' AND TFCompleteBy Like '**'

Weirdly, I miss a whole subsection of documents. There are 4 STG,PTG,RTG and WTG. When I use anything other than
[Workflow] = 'In Evaluation Loop' this happens, but the documents are there in the underlying query.

If I change your string to:

stlinkcriteria = "[Workflow] = 'In Evaluation Loop' AND TFCompleteBy LIKE '" & Forms!
[frm_TestGroupTestFormTracker]!txtevent & "'"

I get with the PRT selected in the combo:

[Workflow = 'In Evaluation Loop' AND TFCompleteBy Like 'PRT'

[Workflow = 'Ready For Evaluation' AND TFCompleteBy Like 'PRT'


I get with the * selected in the combo:

[Workflow = 'In Evaluation Loop' AND TFCompleteBy Like '*'

[Workflow = 'Ready For Evaluation' AND TFCompleteBy Like '*'

However, both documents are the same whether the combo is PRT, or * so I dont think the filter is working.

The problem is I only want to differentiate between PRT and all at the moment, but I may want to change later by
using another code, which in theory I could just put in the combo box as another row.

Why the hell all the WTG documents go missing when I use the multiple field addition I have no idea!!

Thanks
JBN

 

[NewsLeecher User]

John,

I found another article on google similar to your response, and after replacing a variable with the straight text
'PRT' I still ended up with the same result.

I have adopted a different tack, removed the txtevent textbox and just read in the contents of the cmbo to the
code behind the report command button.

This works fine, although I would have rather used the combo to retain future flexibility without having to hard
code changes into the button.. But time presses on !!

Code used is below, and thanks for your assistance.. JBN


Dim stDocName As String
Dim stlinkcriteria As String

stDocName = "StatusPrintForInTestingAt100%"

If Forms!frm_TestGroupTestFormTracker!eventfiltercombo.Column(1) = "PRT" Then

stlinkcriteria = "[Workflow] = 'Ready For Evaluation' AND [TFCompleteBy] = 'PRT'"

Else: stlinkcriteria = "[Workflow] = 'Ready For Evaluation'"

End If


DoCmd.OpenReport stDocName, acPreview, , stlinkcriteria

 

[John W. Vinson]

I get with the PRT selected in the combo:

[Workflow = 'In Evaluation Loop' AND TFCompleteBy Like 'PRT*'

[Workflow = 'Ready For Evaluation' AND TFCompleteBy Like 'PRT*'

If this is actually what your code is producing, then you're missing a close
bracket after [Workflow - might that be the problem?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[NewsLeecher User]

John,

That was a senior moment, bracket was in the code...

JBN