PC Review


Reply
Thread Tools Rate Thread

Passing vars to a query

 
 
=?Utf-8?B?RGVubmlz?=
Guest
Posts: n/a
 
      9th Oct 2007
Okay, despite the simplicity suggested by the thread title, this one is a bit
unusual. In Access2002, I have a query set up. (So far, simple, right?)
Anyway, I have a form that I want the user to select one-to-may assorted
criteria, such as a date range, a building location, a person's name, a
vehicle license plate number, etc. Again, the user can select as few or as
many as he wants.

Now, at this point, I've defined several PUBLIC functions that are used to
pass these values into the query. Also easy enough. BUT.... what if the user
leaves some textbox controls empty (isn't using them for the record lookup.)
How can I tell the query to use that function's value UNLESS it's blank, in
which case ignore that one and select "all" for that column? I have no idea
how to make that happen.

Now, I can build my own SQL string for the combined query criteria, and I
know I can create pass-through queries using that SQL string, so might this
be the way to go? If so, what VBA commands/syntax would I use to create a new
query, fill it with the SQL information, save it, execute it, and delete it
when done? Also, the report would have to know to use that query as its
record source. Can that even be done (in advance) in the report, when I'd be
creating it on-the-fly?

Any suggestions would be most helpful. Again, this is for a report, not to
pull up records in a form. Access2002.

thanks for any ideas you might come up with!
 
Reply With Quote
 
 
 
 
Jeff Boyce
Guest
Posts: n/a
 
      9th Oct 2007
Dennis

Rather than trying to customize a query to handle any/all possible
combination of selection criteria from your form, consider doing this
entirely IN your form.

If you dynamically build a SQL statement in code-behind-form to handle any
number of selection criteria, then you can open the report, passing it the
SQL string as a WHERE clause, using a command button on the form.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Dennis" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Okay, despite the simplicity suggested by the thread title, this one is a
> bit
> unusual. In Access2002, I have a query set up. (So far, simple, right?)
> Anyway, I have a form that I want the user to select one-to-may assorted
> criteria, such as a date range, a building location, a person's name, a
> vehicle license plate number, etc. Again, the user can select as few or as
> many as he wants.
>
> Now, at this point, I've defined several PUBLIC functions that are used to
> pass these values into the query. Also easy enough. BUT.... what if the
> user
> leaves some textbox controls empty (isn't using them for the record
> lookup.)
> How can I tell the query to use that function's value UNLESS it's blank,
> in
> which case ignore that one and select "all" for that column? I have no
> idea
> how to make that happen.
>
> Now, I can build my own SQL string for the combined query criteria, and I
> know I can create pass-through queries using that SQL string, so might
> this
> be the way to go? If so, what VBA commands/syntax would I use to create a
> new
> query, fill it with the SQL information, save it, execute it, and delete
> it
> when done? Also, the report would have to know to use that query as its
> record source. Can that even be done (in advance) in the report, when I'd
> be
> creating it on-the-fly?
>
> Any suggestions would be most helpful. Again, this is for a report, not to
> pull up records in a form. Access2002.
>
> thanks for any ideas you might come up with!



 
Reply With Quote
 
 
 
 
=?Utf-8?B?RGVubmlz?=
Guest
Posts: n/a
 
      9th Oct 2007
AHA! I see that now! I never noticed that before. Okay, so what do I use as
the "Control Source" for the report itself? Just blank it out?

"Jeff Boyce" wrote:

> Dennis
>
> Rather than trying to customize a query to handle any/all possible
> combination of selection criteria from your form, consider doing this
> entirely IN your form.
>
> If you dynamically build a SQL statement in code-behind-form to handle any
> number of selection criteria, then you can open the report, passing it the
> SQL string as a WHERE clause, using a command button on the form.
>
> Good luck!
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "Dennis" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Okay, despite the simplicity suggested by the thread title, this one is a
> > bit
> > unusual. In Access2002, I have a query set up. (So far, simple, right?)
> > Anyway, I have a form that I want the user to select one-to-may assorted
> > criteria, such as a date range, a building location, a person's name, a
> > vehicle license plate number, etc. Again, the user can select as few or as
> > many as he wants.
> >
> > Now, at this point, I've defined several PUBLIC functions that are used to
> > pass these values into the query. Also easy enough. BUT.... what if the
> > user
> > leaves some textbox controls empty (isn't using them for the record
> > lookup.)
> > How can I tell the query to use that function's value UNLESS it's blank,
> > in
> > which case ignore that one and select "all" for that column? I have no
> > idea
> > how to make that happen.
> >
> > Now, I can build my own SQL string for the combined query criteria, and I
> > know I can create pass-through queries using that SQL string, so might
> > this
> > be the way to go? If so, what VBA commands/syntax would I use to create a
> > new
> > query, fill it with the SQL information, save it, execute it, and delete
> > it
> > when done? Also, the report would have to know to use that query as its
> > record source. Can that even be done (in advance) in the report, when I'd
> > be
> > creating it on-the-fly?
> >
> > Any suggestions would be most helpful. Again, this is for a report, not to
> > pull up records in a form. Access2002.
> >
> > thanks for any ideas you might come up with!

>
>
>

 
Reply With Quote
 
Jeff Boyce
Guest
Posts: n/a
 
      9th Oct 2007
Dennis

Create a "base" query that would return ALL records as the source for the
report. Then, when you open the report with the WHERE clause, the base
query is, in effect, customized.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Dennis" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> AHA! I see that now! I never noticed that before. Okay, so what do I use
> as
> the "Control Source" for the report itself? Just blank it out?
>
> "Jeff Boyce" wrote:
>
>> Dennis
>>
>> Rather than trying to customize a query to handle any/all possible
>> combination of selection criteria from your form, consider doing this
>> entirely IN your form.
>>
>> If you dynamically build a SQL statement in code-behind-form to handle
>> any
>> number of selection criteria, then you can open the report, passing it
>> the
>> SQL string as a WHERE clause, using a command button on the form.
>>
>> Good luck!
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "Dennis" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Okay, despite the simplicity suggested by the thread title, this one is
>> > a
>> > bit
>> > unusual. In Access2002, I have a query set up. (So far, simple, right?)
>> > Anyway, I have a form that I want the user to select one-to-may
>> > assorted
>> > criteria, such as a date range, a building location, a person's name, a
>> > vehicle license plate number, etc. Again, the user can select as few or
>> > as
>> > many as he wants.
>> >
>> > Now, at this point, I've defined several PUBLIC functions that are used
>> > to
>> > pass these values into the query. Also easy enough. BUT.... what if the
>> > user
>> > leaves some textbox controls empty (isn't using them for the record
>> > lookup.)
>> > How can I tell the query to use that function's value UNLESS it's
>> > blank,
>> > in
>> > which case ignore that one and select "all" for that column? I have no
>> > idea
>> > how to make that happen.
>> >
>> > Now, I can build my own SQL string for the combined query criteria, and
>> > I
>> > know I can create pass-through queries using that SQL string, so might
>> > this
>> > be the way to go? If so, what VBA commands/syntax would I use to create
>> > a
>> > new
>> > query, fill it with the SQL information, save it, execute it, and
>> > delete
>> > it
>> > when done? Also, the report would have to know to use that query as its
>> > record source. Can that even be done (in advance) in the report, when
>> > I'd
>> > be
>> > creating it on-the-fly?
>> >
>> > Any suggestions would be most helpful. Again, this is for a report, not
>> > to
>> > pull up records in a form. Access2002.
>> >
>> > thanks for any ideas you might come up with!

>>
>>
>>



 
Reply With Quote
 
=?Utf-8?B?RGVubmlz?=
Guest
Posts: n/a
 
      9th Oct 2007
Killer! I already have that, so all I have to do is build that SQL string
(also about 80% complete already) and pass it to the report on the Open
command.

Thanks a million!

"Jeff Boyce" wrote:

> Dennis
>
> Create a "base" query that would return ALL records as the source for the
> report. Then, when you open the report with the WHERE clause, the base
> query is, in effect, customized.
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "Dennis" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > AHA! I see that now! I never noticed that before. Okay, so what do I use
> > as
> > the "Control Source" for the report itself? Just blank it out?
> >
> > "Jeff Boyce" wrote:
> >
> >> Dennis
> >>
> >> Rather than trying to customize a query to handle any/all possible
> >> combination of selection criteria from your form, consider doing this
> >> entirely IN your form.
> >>
> >> If you dynamically build a SQL statement in code-behind-form to handle
> >> any
> >> number of selection criteria, then you can open the report, passing it
> >> the
> >> SQL string as a WHERE clause, using a command button on the form.
> >>
> >> Good luck!
> >>
> >> Regards
> >>
> >> Jeff Boyce
> >> Microsoft Office/Access MVP
> >>
> >> "Dennis" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > Okay, despite the simplicity suggested by the thread title, this one is
> >> > a
> >> > bit
> >> > unusual. In Access2002, I have a query set up. (So far, simple, right?)
> >> > Anyway, I have a form that I want the user to select one-to-may
> >> > assorted
> >> > criteria, such as a date range, a building location, a person's name, a
> >> > vehicle license plate number, etc. Again, the user can select as few or
> >> > as
> >> > many as he wants.
> >> >
> >> > Now, at this point, I've defined several PUBLIC functions that are used
> >> > to
> >> > pass these values into the query. Also easy enough. BUT.... what if the
> >> > user
> >> > leaves some textbox controls empty (isn't using them for the record
> >> > lookup.)
> >> > How can I tell the query to use that function's value UNLESS it's
> >> > blank,
> >> > in
> >> > which case ignore that one and select "all" for that column? I have no
> >> > idea
> >> > how to make that happen.
> >> >
> >> > Now, I can build my own SQL string for the combined query criteria, and
> >> > I
> >> > know I can create pass-through queries using that SQL string, so might
> >> > this
> >> > be the way to go? If so, what VBA commands/syntax would I use to create
> >> > a
> >> > new
> >> > query, fill it with the SQL information, save it, execute it, and
> >> > delete
> >> > it
> >> > when done? Also, the report would have to know to use that query as its
> >> > record source. Can that even be done (in advance) in the report, when
> >> > I'd
> >> > be
> >> > creating it on-the-fly?
> >> >
> >> > Any suggestions would be most helpful. Again, this is for a report, not
> >> > to
> >> > pull up records in a form. Access2002.
> >> >
> >> > thanks for any ideas you might come up with!
> >>
> >>
> >>

>
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing Vars with Seek Function dbarmer Microsoft Access VBA Modules 2 13th Nov 2009 06:15 AM
Pivot tables - can change row/col vars but can't get rid of existing vars Andreww Microsoft Excel Programming 0 25th May 2007 11:50 AM
Pass Javascript vars to global vars in asp.net rbutch@coair.com Microsoft ADO .NET 1 21st Mar 2005 07:39 AM
app vars and cache vars Jon Microsoft ASP .NET 3 14th Dec 2004 09:52 PM
passing vars through forms Daniel SÚlen Secches Microsoft VB .NET 2 15th Mar 2004 07:51 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:49 AM.