Re: Cannot get a Wild Card Query to show Records.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Van T. Dinh (VanThien.Dinh_at_discussions.microsoft.com)
Date: 03/24/05 

Date: Fri, 25 Mar 2005 10:48:17 +1100

It sounds to me that you want criteria something like:

WHERE ...
( ( Field4 = [Forms]![MasterForm]![TxtBx4] ) OR
      ( [Forms]![MasterForm]![TxtBx4] Is Null ) ) AND
( ( Field5 = [Forms]![MasterForm]![TxtBx5] ) OR
      ( [Forms]![MasterForm]![TxtBx5] Is Null ) ) AND
( ( Field6 = [Forms]![MasterForm]![TxtBx6] ) OR
      ( [Forms]![MasterForm]![TxtBx6] Is Null ) ) 

-- 
HTH
Van T. Dinh
MVP (Access)
"titlepusher" <titlepusher@discussions.microsoft.com> wrote in message
news:35A2C14B-8C5E-4647-A0B6-6E65D25FFA5E@microsoft.com...
> Brain Dead....    Ok.. my objective : if the form control field (text box)
is
> blank show all records, otherwise show the specific record that matches
the
> value in the text box.   I need this for three fields on the same form.
>
> I have a master form with six text boxes to query data from.  Three
fields(
> 1,2 and 3) (combo boxes) are easy.. Match the form control exactly) The
other
> three form controls  4,5, and 6)  text boxes) need to be able to be blank
or
> have data.
>
> I want to be able to construct a query so that If any one of form controls
> 4,5,6 is blank then return all records for that field and continue to
filter
> from the other fields.
>
> What I Have Is:  In the query design window    for instance in field 4
>
> IIF([Forms]![MasterForm]![TxtBx4] Is Null,
"*",[Forms]![MasterForm]![TxtBx4])
> I repeated this for fields 5 and 6 changing the field names respectively.
>
> I have also changed the syntax to [Forms]![MasterForm]![TxtBx4] &"*"
>
> Like*[Forms]![MasterForm]![TxtBx4]
>
> [Forms]![MasterForm]![TxtBx4]*
> and a host of others.
>
> When I substitute the "[Forms]![MasterForm]![TxtBx4]" part of the
statement
> for a known value and run it I do get records matching the known value, so
I
> know that it recognizes when the form control field is blank.
>
> Pulled all of my hair out and my dog is getting nervous.
>
> Help
>
>
>

Relevant Pages

  • Re: Cannot get a Wild Card Query to show Records.
    ... The query will only return ... records when I enter known values into the text boxes. ... >> I want to be able to construct a query so that If any one of form controls ... >> for a known value and run it I do get records matching the known value, ...
    (microsoft.public.access.queries)
  • RE: Need help with emailing a report in access
    ... I would probly use a query so how do I set that up? ... can just reference the form controls holding the correct values. ... Dim strMsgBody As String ...
    (microsoft.public.access.reports)
  • Cannot get a Wild Card Query to show Records.
    ... I have a master form with six text boxes to query data from. ... I want to be able to construct a query so that If any one of form controls ...
    (microsoft.public.access.queries)
  • RE: Changing the parameters of a form
    ... using references to your form controls as ... FROM [Personnel Roster]; ... This query produces each persons name and a square 0 in the "Available" ...
    (microsoft.public.access.queries)
  • Re: Query Criteria from Forms
    ... > I want to create a query that will collect criteria from a ... I've had success w/ substituting defaults when the form's control value ... In effect, if both form controls are null, all records between the ...
    (microsoft.public.access.queries)