where clause in unbound textbox

[maxmangion]

Hi,

When I use a parameter query i normally get the value from an unbound textbox from a form and then reference it in the criteria row of the query.

However, is there a way in which rather than passing a value from the unbound textbox to the criteria row, i would was a full criteria clause: i.e. if i type:

"value1" or "value2" in the unbound textbox, this would be the same as if i have typed it in the criteria row?

Thank You

 

[neileg]

No, I don't think so. You could build the query as a string and use the textbox contents in that way, though, and use RunSQL to execute it.

 

[maxmangion]

Hi,

Thanks for your reply. Actually, i had an idea that i could achieve that by your suggestion, but since i was not particularly familiar with that area of ms access, i hoped for an easier alternative.

So it's time to make some reading and experimenting

Thank You

 

[LPurvis]

What you ask about is somewhat analogous to the old expectation that a single parameter used in a scenario such as
SELECT *
FROM TableName
WHERE SomeField In (1,2,3)

could be replaced with
SELECT *
FROM TableName
WHERE SomeField In ([Enter Parameter])

The expectation being that the user can enter a parameter at runtime of
1,2,3
and that Jet would provide the same results (i.e. be able to interpret the single parameter as anything other than a single literal value!).
Naturally it can't - and we look to either dynamic SQL constructed in VBA, or create less efficient workarounds such as

SELECT *
FROM TableName
WHERE "," & Replace([Enter Parameter], " ", "") & "," Like "*," & SomeField & ",*"

to allow the same functionality by default (albeit with that already mentioned efficiency hit).
Text entered as "1" OR "2" really needs to be parsed in VBA to create an efficient (and well sanitised) query - as there are so many variations that a user might type in. Though you could implement a basic level of functionality using much the same concept. But the requirements upon the user to enter criteria text in just the right format is a distasteful application implementation to me personally.

FWIW, in the forced scenario searching on
"1" OR "2"
you could have something like
WHERE Replace([Enter Parameter], " ", "") Like "*""" & SomeField & """*"
as your where clause. It expects exact matching upon whole values of course - there's no "like within like" functionality there.
Similarly if your input was to be just
1 OR 2
you could use
WHERE "OR" & Replace([Enter Parameter], " ", "") & "OR" Like "*OR" & EmployeeID & "OR*"

Just theory for you really.
IMO dynamic SQL wins over this every time - even if it's fun and convenient.

Cheers.

 

[maxmangion]

Thank you very much all for your feedback, very much appreciated.

 

[maxmangion]

Hi all,

I know this is an old thread which i created myself, but i managed to find a solution how to go about it, and i thought i should share it with you.

i created the unbound textbox to accept the criteria string, and then i made use of the BuildCriteria function as follows:

Dim strCriteria As String
strCriteria = BuildCriteria("FieldName", dbText, Me.TextboxName)
DoCmd.OpenReport "rptReportName", acViewPreview, , strCriteria

 

[LPurvis]

OK. So you've effectively opted to go with a Filter (WhereCondition) - which is essentially analogous to a dynamic query (mentioned first by neileg).
(The fact that you've used BuildCriteria is not very relevant - it's just a choice of implementation for building your Where clause criteria).
The only reason WhereCondition wasn't explicitly mentioned was because you didn't cite a form source as being the implementation of your query.

That you're happy and sorted is the main thing though.

 

[maxmangion]

Hi LPurvis,

Thx for your feedback. Basically, we have a report based on a query and normally users enter a year in a textbox and run the report which filter the records based on the year entered. Since sometimes there is the need to print the report for 2 or more different years, the users were entering a year and print the report, then enter subsequent year(s) and reprint the report. This worked fine for out needs, but i thought if they could enter all the years in the textbox and run it once, it will save them a few clicks

That you're happy and sorted is the main thing though.

Until the next complain/request they seem to be happy

Thanks again

 

[dkinley]

... Just adding some other notes and observations.

My normal method of procedure is similar. In a main form I might have a person look-up section and use unbound textboxes for first and last names and then in the built query used the unbound critieria:

Like [Forms]![frmMain]![txtFirstName] & "*", etc ....

What I have also done is for reports (like your question of the multiple year and LPurvis example) is put a limit on the number of parameters. A label gave notice that only 3 inputs could be provided and I parsed those out using InStr() and then put each one on a hidden text box on the main form. The error checking and validation was a mondo headache.

I've attempted to build statements using functions like IFF that refer to the unbound text box. The goal was so I could use the same query for Admin types and User types only the query returns would be different (e.g., admins could see all records, users could see all but the 'deleted' records). The outcome was not as expected. I jacked around for a week (instead of spending 2 minutes building a second query) and I couldn't seem to get around the quotes or something.

It seems the only way to do this efficiently dynamic SQL is the only way to go for complex criteria.

Anyhow ... just contributing.

-dK