Re: BuildCriteria Results --- To Marshall Barton or ?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: G (G_at_discussions.microsoft.com)
Date: 02/28/05 

Date: Sun, 27 Feb 2005 20:49:02 -0800

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

"Marshall Barton" wrote:

> 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]
>
>
>
> G wrote:
> >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".
> >
> >
> >"Marshall Barton" wrote:
> >> Did you type the > in the text box for the date field?
> >>
> >> Exactly what did you type in the text box?
> >>
> >>
> >> G wrote:
> >> >On "attempting" to get the "buildcriteria" to show x > somedate, it always
> >> >shows "x = somedate"
> >> >Am I missing something?
> >> >
> >> >
> >> >> G wrote:
> >> >> >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?
> >> >>
> >> >>
> >> >"Marshall Barton" wrote:
> >> >> 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.
>
>

Quantcast