RE: query criteria entered via a form

It worked!!!! Thank-you so much for your help and patience!!! Lori

"Ofer" wrote:

> Try if this give you the resault you are looking for
>
> SELECT tbl_Vendor.Vendor_Nbr, tbl_Vendor.Vendor_Name,
> tbl_Vendor.Chain_Suffix, tbl_Vendor.Region_Code, tbl_Vendor.Status_Code
> FROM tbl_Vendor
> WHERE (tbl_Vendor.Chain_Suffix=[Forms]![frm_rpt_test]![Chain_Suffix] Or
> [Forms]![frm_rpt_test]![Chain_Suffix] Is Null) AND
> (tbl_Vendor.Region_Code=[Forms]![frm_rpt_test]![Region_Code] Or
> [Forms]![frm_rpt_test]![Region_Code] Is Null) AND
> (tbl_Vendor.Status_Code=[Forms]![frm_rpt_test]![Status_Code] Or
> [Forms]![frm_rpt_test]![Status_Code] Is Null)
>
> --
> \\// Live Long and Prosper \\//
> BS"D
>
>
> "Lori" wrote:
>
> > Here's the SQL - I used every possible combination - all 3 criteria with
> > values, each by itself, and each combination of only 2 criteria being
> > selected. Also, I was mistaken earlier - the only way the result is correct
> > is when I pick only one criteria from the form. Thanks for your help!
> >
> > SELECT tbl_Vendor.Vendor_Nbr, tbl_Vendor.Vendor_Name,
> > tbl_Vendor.Chain_Suffix, tbl_Vendor.Region_Code, tbl_Vendor.Status_Code
> > FROM tbl_Vendor
> > WHERE (((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
> > ((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code]) AND
> > ((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
> > (((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
> > ((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code])) OR
> > (((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
> > ((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
> > (((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code]) AND
> > ((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
> > (((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix])) OR
> > (((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code])) OR
> > (((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code]));
> >
> >
> > "Ofer" wrote:
> >
> > > can you post the SQL you have now?
> > >
> > > --
> > > \\// Live Long and Prosper \\//
> > > BS"D
> > >
> > >
> > > "Lori" wrote:
> > >
> > > > Hi,
> > > >
> > > > I got the two criteria to work. I have since added a third - I can get it
> > > > to work if I fill in only one field or all but not if I do 2 out of the
> > > > three. I need some way to represent a value not being selected from the 3rd
> > > > field. Any suggestions?
> > > >
> > > > "Ofer" wrote:
> > > >
> > > > > What happen when you run only "2" from the Region field, without the status?
> > > > >
> > > > > If it run correctly, it could be that the criteria you are looking for is OR
> > > > > and not AND as you have now.
> > > > > If that the case move the Region criteria one box under, or in SQL view
> > > > > change the AND to OR
> > > > >
> > > > >
> > > > > --
> > > > > \\// Live Long and Prosper \\//
> > > > > BS"D
> > > > >
> > > > >
> > > > > "Lori" wrote:
> > > > >
> > > > > > I am using Access 2002 and created an unbound form with a couple fields to be
> > > > > > used for search criteria to be selected on the fly to drive a query. I have
> > > > > > a field called Region and one called Status. I entered the form fields in
> > > > > > the query criteria but can only get the results to work using one selection
> > > > > > criteria at a time. How can I make the query run if both selection criteria
> > > > > > have a value selected from it?
> > > > > >
> > > > > > For example, if I select "active" from the status drop-down field on the
> > > > > > form, the query runs correctly. But if I select "active" from the status
> > > > > > field and "2" from the Region field - the results are inaccurate.
> > > > > >
> > > > > > Here is what I have in the query criteria: Under the Region field -
> > > > > > [Forms]![frm_rpt_test]![Region_Code] and under the Status field -
> > > > > > [Forms]![frm_rpt_test]![Status_Code]. I know I am not finding the right
> > > > > > combination or rows to place this data in the query design. Thanks!
.

Relevant Pages

  • RE: query criteria entered via a form
    ... Try if this give you the resault you are looking for ... "Lori" wrote: ... > is when I pick only one criteria from the form. ... Prev by Date: ...
    (microsoft.public.access.queries)
  • Re: Why do Access queries stop working if I use more than 2 criter
    ... Ofer and Dave, ... Criteria line are ANDed together and that multiple Criteria lines are ... "Nora" wrote: ...
    (microsoft.public.access.queries)
  • RE: Referencing Unbound Option Group in Query
    ... "Lori" wrote: ... > called "DateComp" data type of Date. ... This is the only criteria in the query so far. ...
    (microsoft.public.access.forms)
  • RE: Multiple date parameters...simplify
    ... Greetings Ofer and thank you for the reply...however, ... I'm not able to tab out of the criteria field (of my date ... field in the query) w/ out receiving a syntax error message... ... Steve ...
    (microsoft.public.access.forms)
  • Re: record criteria
    ... > Hi Ofer, ... > no, if you want to put the criteria in a textbox, it is necessary to use ... > Like or the Instr function. ...
    (microsoft.public.access.queries)