BuildCriteria Results --- To Marshall Barton or ?

[G]

Marshall,
You were assisting me a few days ago with the BuildCriteria. The thread is
no longer available due to hiccup.

In your post, you mentioned that I "might be using BuildCriteria's type
argument improperly. It's supposed to specify the type of the field in the
table/query and you'll get some funny results for numeric or date type
fields."
But in another paragraph you said "BuildCriteria will pretty much take care
of everything, including Date fields, as long as you tell it the proper type
of the field."
Can you explain the "funny results" I might expect?

I have done everything that you suggested and it seems to be working except
for a couple of the fields, where I am getting error messages.

These are text fields and in one field the data is like this "the customer
is ..."
I get an error saying "is" has to be used with null/not null. Is the
BuildCriteria possible interpreting this incorrectly?

I am also getting "The expression you entered contains invalid syntax, or
you need to enclose your text data in quotes". Could this be caused by
punctuation in a text field? Like periods, or commas?

Thank you!

G

 

[Dirk Goldgar]

Marshall,
You were assisting me a few days ago with the BuildCriteria. The
thread is no longer available due to hiccup.

In your post, you mentioned that I "might be using BuildCriteria's
type argument improperly. It's supposed to specify the type of the
field in the table/query and you'll get some funny results for
numeric or date type fields."
But in another paragraph you said "BuildCriteria will pretty much
take care of everything, including Date fields, as long as you tell
it the proper type of the field."
Can you explain the "funny results" I might expect?

I have done everything that you suggested and it seems to be working
except for a couple of the fields, where I am getting error messages.

These are text fields and in one field the data is like this "the
customer is ..."
I get an error saying "is" has to be used with null/not null. Is the
BuildCriteria possible interpreting this incorrectly?

I am also getting "The expression you entered contains invalid
syntax, or you need to enclose your text data in quotes". Could this
be caused by punctuation in a text field? Like periods, or commas?

Thank you!

G

Hi, G -

I just checked this out, and I think you are right -- BuildCriteria is
not interpreting your control's value correctly. BuildCriteria has all
kinds of logic in it to figure out what the user means when she supplies
a value to be used in a criterion expression, and the presence of the
keyword "Is" is significant in that logic. Yes, it depends on what the
meaning of "Is" is. <g>

If your criteria for text fields are going to contain the word "is" --
as a separate word, not in the middle of another word -- then I think
you're going to have to use logic like this if you want to use logic
like this:

' modified from the code posted in the other thread,
' where I see you are using the control's .Tag property
' to hold the field-type value:

sWhereClause = sWhereClause & " and " & _
BuildCriteria(.Name, .Tag, _
IIf(.Tag = dbText, _
"""" & Replace(.Value, Chr(34), """""") & """", _
.Value))

 

[G]

Thank you Dirk, I am going nuts with this one. I must say that all of you
that are answering questions are absolutely wonderful and I truly appreciate
ALL the help.

Will this also handle the punctuation issue, ie commas and periods?
G

 

[Dirk Goldgar]

Thank you Dirk, I am going nuts with this one. I must say that all of
you that are answering questions are absolutely wonderful and I truly
appreciate ALL the help.

We're all glad to help.

Will this also handle the punctuation issue, ie commas and periods?

I don't know enough details about what the "punctuation issue" is, but
the revision may handle that, too. Try it and see.

 

[G]

VOILA! handled all. I still have to go through all the controls, but the
first 3 that had these errors, now do not error out! That handled the "is"
and the punctuation! WHOO EEE!

I have lots of books on ACCESS, SQL and Development, etc, and was unable to
find much on the buildcriteria.

Many, many thanks! Now I just have a few small things to fix.

G

 

[Marshall Barton]

Marshall,
You were assisting me a few days ago with the BuildCriteria. The thread is
no longer available due to hiccup.

In your post, you mentioned that I "might be using BuildCriteria's type
argument improperly. It's supposed to specify the type of the field in the
table/query and you'll get some funny results for numeric or date type
fields."
But in another paragraph you said "BuildCriteria will pretty much take care
of everything, including Date fields, as long as you tell it the proper type
of the field."
Can you explain the "funny results" I might expect?

I have done everything that you suggested and it seems to be working except
for a couple of the fields, where I am getting error messages.

These are text fields and in one field the data is like this "the customer
is ..."
I get an error saying "is" has to be used with null/not null. Is the
BuildCriteria possible interpreting this incorrectly?

I am also getting "The expression you entered contains invalid syntax, or
you need to enclose your text data in quotes". Could this be caused by
punctuation in a text field? Like periods, or commas?

When I said that, you were using dbText as the type for
every field, so "funny results" was referring to what might
happen when you tell BuildCriteria the wrong field data
type.

As for the issue with the word IS in Text type fields, this
is one of the reasons why I said that BuildCriteria may just
be too powerful . . . You need to include the quotes in
the text box where you typed "the customer is ..."

Dirk has provided a way to deal with this problem so you
should finally be up and running!? The only issue with
DIrk's approach is that it will prevent your users from
using a criteria along the lines of
Fred OR George

The "best" solution to this problem is to educate all your
users in all the nuances of query criteria expressions so
they know when to supply the quotes themselves instead of
you trying to do it in a heavy handed automatic way. Based
on my experience with users, that will never happen for
every single one of them, so you may want to skip the "best"
way to deal with this and live with the limitations Dirk's
code imposes.

 

[Dirk Goldgar]

The only issue with
DIrk's approach is that it will prevent your users from
using a criteria along the lines of
Fred OR George

Quite right. The flexibility and power of BuildCriteria is reduced.

The "best" solution to this problem is to educate all your
users in all the nuances of query criteria expressions so
they know when to supply the quotes themselves instead of
you trying to do it in a heavy handed automatic way. Based
on my experience with users, that will never happen for
every single one of them, so you may want to skip the "best"
way to deal with this and live with the limitations Dirk's
code imposes.

I believe I've just been called "heavy-handed". I'm hurt.

Okay, I'm over it now. The only intermediate solution I can think of
would be to examine the contents of the text fields in advance and try
to determine whether they are going to pose a problem to the
BuildCriteria method, wrapping them in quotes only if they will. That
would involve looking for such things as "Is " not followed by "Null" or
"Not Null", and whatever the particular punctuation issues are that G
mentioned. You probably couldn't achieve perfection, but you might
arrive at "good enough", at the expense of a certain amount of extra
code.

Another thing to do is trap any error raised by the BuildCriteria
method, and either report it nicely to the user or try to fix it up
yourself. Seems to me I did that once, in some app or other.

 

[G]

Thank you Marshall!
Your method is really good!

Yes, the fix that Dirk provided did take care of that, but as you say, it
will not handle "or".
But I have another question, Some of the date/number fields are "max" and
"min", where I will have to show "between". Would your method of using the
prefix "srch" work if I added "btwn" instead of "srch" for these fields and
set it up that if is not null, then get the "between x and Y" and add that to
the sWhereClause?

G

 

[G]

Dirk,
See my other post regarding "between"
I believe you are correct, in that if it is text, see if it contains *is*,
then wrap it. And the same for commas, periods, apostrophes. Seems to me that
would only add a few lines of code.
I gotta hand it to you and Marshall, though, this is quite a powerful tool,
and you are really wonderful to help me to understand it.

G

 

[Marshall Barton]

YOU are not heavy handed Dirk ;-)

I have selectively used the quote approach for criteria from
a combo box where an expression wouldn't make much sense.

As for trying to parse the user's criteria expression, **I**
wouldn't want to try to anticipate all the ways user's could
inadvertently use a keyword (operator) in an inappropriate
manner. **MY** attitude is to go ahead and bang my head
against the wall of their learning curve and let the users
that "get it" straighten it out for the occasional dense
user ;-)

I definitely like the idea or using error handling to
provide a warning message and either bailing out by wrapping
the text in quotes, dropping the criteria altogether
(depending on whether you want to provide too many matches
or none at all) or resetting the focus back to the offending
control until they get it right.

 

[Marshall Barton]

Thank you Marshall!
Your method is really good!

Yes, the fix that Dirk provided did take care of that, but as you say, it
will not handle "or".
But I have another question, Some of the date/number fields are "max" and
"min", where I will have to show "between". Would your method of using the
prefix "srch" work if I added "btwn" instead of "srch" for these fields and
set it up that if is not null, then get the "between x and Y" and add that to
the sWhereClause?

Prefixing the controls with "srch" was just a way to tell
which controls were to be used with BuildCriteria. That had
nothing to do with the kind of criteria (although your idea
could be used to decide if the code should automatically
provide the surrounding quotes for some text fields).

As I said earlier, I prefer to let the users specify their
criteria. In the case of a range, they (the users) can
learn to type it the way Access wants you (the query writer)
to do it in the query design window:

Between 1/1/05 And 6/31/05
or
Between #1/1/05# And #6/31/05#

depending on if the user's locale is set for USA dates or
not.

BuildCriteria will deal with that kind of criteria as long
as you tell it that its for a Date type field, which I
presume you're already doing. This way, the users have a
free hand to decide if they want a closed range as above, or
an open range such as:

1/1/05

or
< 6/31/05

or an exact match:

= 1/1/05
or just:
1/1/05

or any number of other possibilities without you having to
do anything more than you have working now.

 

[Dirk Goldgar]

Between 1/1/05 And 6/31/05
or
Between #1/1/05# And #6/31/05#

30 days hath September ...

 

[Marshall Barton]

30 days hath September ...

Well, yeah Dirk, but that doesn't always apply in my own
little world ;-)

 

[G]

On "attempting" to get the "buildcriteria" to show x > somedate, it always
shows "x = somedate"
Am I missing something?

G

 

[Marshall Barton]

Did you type the > in the text box for the date field?

Exactly what did you type in the text box?

 

[G]

that is what you meant by having the user decide........duh......
I'll be back in a min

Did you type the > in the text box for the date field?

Exactly what did you type in the text box?
--
Marsh
MVP [MS Access]

On "attempting" to get the "buildcriteria" to show x > somedate, it always
shows "x = somedate"
Am I missing something?

 

[G]

Now when I type in > 1/1/2005 and hit search
it's asking for the parameter type, then returning all records.
I have the type set in the tag.

gotta be really simple at this point.......

G

Did you type the > in the text box for the date field?

Exactly what did you type in the text box?
--
Marsh
MVP [MS Access]

On "attempting" to get the "buildcriteria" to show x > somedate, it always
shows "x = somedate"
Am I missing something?

 

[G]

I hate to be such a pest......
I had named the control incorrectly.
Now it does not ask for paramenter.
but it brings to mind another question.......if a user is going to put in a
beginning date in one ctl and an ending date on another ctl, but they are
both based on the same field..........profiledate.......then they can't be
named the same.........how is that handled?
I know it loops thru each ctl that has "srch".

G

Did you type the > in the text box for the date field?

Exactly what did you type in the text box?
--
Marsh
MVP [MS Access]

On "attempting" to get the "buildcriteria" to show x > somedate, it always
shows "x = somedate"
Am I missing something?

 

[Marshall Barton]

When you're using BuildCriteria, you wouldn't need to use
two text boxes for start and end dates. It's up to the user
to type the criteria as
Between {startdate} And {enddate}
or

={startdate} AND <={enddate}

If the users are insisting that they have to use two text
boxes, you might want to include a baseball bat in your
discussions on the issue ;-)
Otherwise, you'll have unconfuse your control naming
because you can't have two text boxes named srch(DateField}.

Then you could add a bunch of code to check if neither, one,
the other, or both are filled in and then construct a string
to put them together. Or you could tell them to enter

={startdate} in one and <={enddate} in the other or leave

one text box empty and use the Between expression in the
other, but then what was the point in having two text boxes
in the first place.

 

[G]

Marshall,
Thank you ever so much. And thank you for keeping your sense of humor about
this. The baseball bat sounds good! *S* I should have used it the first round
when they said they wanted to import an Excel file and do some searches on
it. After a few weeks, they informed me that they wanted to be able to search
on 75 fields, with no possible drilldown. Whew! I tried to explain to them
that it was not the recommended method, but it could be done. Yes, the
baseball bat......
Once again, thank you for taking the time to explain this build criteria to
me, step by step.
And thanks to all the other pros who helped me.

G

When you're using BuildCriteria, you wouldn't need to use
two text boxes for start and end dates. It's up to the user
to type the criteria as
Between {startdate} And {enddate}
or
>={startdate} AND <={enddate}

If the users are insisting that they have to use two text
boxes, you might want to include a baseball bat in your
discussions on the issue ;-)
Otherwise, you'll have unconfuse your control naming
because you can't have two text boxes named srch(DateField}.

Then you could add a bunch of code to check if neither, one,
the other, or both are filled in and then construct a string
to put them together. Or you could tell them to enter

={startdate} in one and <={enddate} in the other or leave

one text box empty and use the Between expression in the
other, but then what was the point in having two text boxes
in the first place.
--
Marsh
MVP [MS Access]

I hate to be such a pest......
I had named the control incorrectly.
Now it does not ask for paramenter.
but it brings to mind another question.......if a user is going to put in a
beginning date in one ctl and an ending date on another ctl, but they are
both based on the same field..........profiledate.......then they can't be
named the same.........how is that handled?
I know it loops thru each ctl that has "srch".