Passing vars to a query

Discussion in 'Microsoft Access Form Coding' started by Guest, Oct 9, 2007.

  1. Guest

    Guest Guest

    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!
     
    Guest, Oct 9, 2007
    #1
    1. Advertisements

  2. Guest

    Jeff Boyce Guest

    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" <> wrote in message
    news:...
    > 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!
     
    Jeff Boyce, Oct 9, 2007
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    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" <> wrote in message
    > news:...
    > > 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!

    >
    >
    >
     
    Guest, Oct 9, 2007
    #3
  4. Guest

    Jeff Boyce Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    >> news:...
    >> > 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!

    >>
    >>
    >>
     
    Jeff Boyce, Oct 9, 2007
    #4
  5. Guest

    Guest Guest

    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" <> wrote in message
    > news:...
    > > 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" <> wrote in message
    > >> news:...
    > >> > 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!
    > >>
    > >>
    > >>

    >
    >
    >
     
    Guest, Oct 9, 2007
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. AndyB

    Re: Passing query value to report?

    AndyB, Jul 11, 2003, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    530
    AndyB
    Jul 11, 2003
  2. Epilepsie Montreal Metropolitain

    Passing a Query-based Report a Query Parameter

    Epilepsie Montreal Metropolitain, Jul 16, 2003, in forum: Microsoft Access Form Coding
    Replies:
    4
    Views:
    265
    Andy Cole
    Jul 16, 2003
  3. Lisa

    passing value from control to query

    Lisa, Dec 22, 2003, in forum: Microsoft Access Form Coding
    Replies:
    3
    Views:
    177
    Dan Artuso
    Dec 23, 2003
  4. kiln

    msaccess.exe remains in task manager; which vars cause?

    kiln, Apr 9, 2004, in forum: Microsoft Access Form Coding
    Replies:
    15
    Views:
    333
    Dirk Goldgar
    Apr 24, 2004
  5. Atlas

    Finding unreferenced vars

    Atlas, Aug 3, 2004, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    226
    Sandra Daigle
    Aug 3, 2004
Loading...

Share This Page