Re: Query to search entire record, not one field?

I thought doing it with the "*" would also look for blank values. Maybe
not.

I have not tested this, but try something like this...

Is Null or Like [Forms]![SearchUnbound]![State] & "*"


--
Rick B



"consjoe" <consjoe@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:72530352-6ED7-4F97-99D8-6F3AC695E489@xxxxxxxxxxxxxxxx
> Okay, nevermind my last post. I have a form with unbound fields now,
(that
> was my problem).
> The problem I am having now is this:
> I put Like Forms![MySearchFormName]![MySearchFieldName] & "*"
> in criteria for all fields in my Query.
>
> For this example I used State Like [Forms]![SearchUnbound]![State] & "*"
>
> There is not information for the "State" field in my master table for this
> particular record.
>
> I enter Company in my search form and leave State blank. Run my query and
> do not get this result.
>
> If I enter a state in my Master Record and then leave State blank (or
enter
> the State) in my search I will retrieve the record.
>
> This problem is only when I have the
> Like Forms![MySearchFormName]![MySearchFieldName] & "*"
> criteria in the query for a field that has a null value in my master
record.
>
> How can I keep this criteria in every field if the master recrods contain
> Null values?
>
> Sorry if this is confusing, I am trying to explain the best I can.
> Thanks so much. (Again)
>
> "Rick B" wrote:
>
> > Not sure what you are asking.
> >
> > You need to build an unbound form.
> >
> > This form will contain a blank UNBOUND field that the users can enter.
> >
> > You can then reference each of these in your query.
> >
> > --
> > Rick B
> >
> >
> >
> > "consjoe" <consjoe@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:52B22AC6-E51D-4BC4-9754-799D1D4A3CD2@xxxxxxxxxxxxxxxx
> > > Thanks for your help Rick.
> > > I understand the second part but am not sure about the first half.
> > > My Table name is "Master"
> > > My Search form is "Search" and is based on a different table with the
same
> > > fields as "Master" But I think it should be based of a query of
"Master"
> > ??
> > > So I need a blank form that you can type in the information you want
to
> > > search by and then click search and all records with the matching
> > information
> > > will show up.
> > > I tried to put in Like Forms! [Search]![State] & "&" but it put Forms!
in
> > []
> > > and when I ran the query it gave me the pop up asking for
> > Foms!Search!State!,
> > > I undertand why I got the pop up but I don't want the pop up, I want
to
> > enter
> > > the information into a blank form and then search.
> > > Thanks again.
> > >
> > > "Rick B" wrote:
> > >
> > > > To search based on a filed in a form that may or may not contain a
> > value,
> > > > you'd use...
> > > >
> > > > Like Forms![MySearchFormName]![MySearchFieldName] & "*"
> > > >
> > > > Putting this in all the various fields in your query would pull
values
> > that
> > > > match if the user had made an entry. If no entry was made for a
> > particular
> > > > field, then all the values in that field would be valid.
> > > >
> > > > --
> > > > Rick B
> > > >
> > > >
> > > >
> > > > "consjoe" <consjoe@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > news:BE45E375-8EE8-48ED-BC84-EE30F1C30CB5@xxxxxxxxxxxxxxxx
> > > > > I have a form based on a table with 20 fields. My clients want
the
> > > > ability
> > > > > to search all fields to find the record they are looking for. Is
> > there a
> > > > way
> > > > > for me to create a form (with all 20 fields) where they can chose
> > which
> > > > > fields to search by and those records come up?
> > > > > Ex. I know that state, company, and first name of a record I
need. I
> > go
> > > > > into the 'Search Form' and type that information in and that
record is
> > > > > retrieved.
> > > > > It is kind of like an and/or type of query but they need to be
able to
> > > > > choose which fields to search by and leave the others blank.
> > > > > Thanks in advance.
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >


.

Relevant Pages

  • Re: Query to search entire record, not one field?
    ... I have a form with unbound fields now, ... in criteria for all fields in my Query. ... criteria in the query for a field that has a null value in my master record. ... "Rick B" wrote: ...
    (microsoft.public.access.queries)
  • Re: Query - Omit records
    ... In the query builder, this would be the equivalent of typing a calculated ... "Rick B" wrote: ... > FROM Categories INNER JOIN (Suppliers INNER JOIN Products ON ... >> you use NOT in criteria it complicates things. ...
    (microsoft.public.access.queries)
  • Re: How do I have access show me a query with just the last 5 days
    ... DROP the quotes around the 5. ... Which is what Rick B posted. ... Try posting the SQL text view of your query for further help. ... Oh and I copied the wrong criteria ...
    (microsoft.public.access.queries)
  • Re: Query to search entire record, not one field?
    ... I think we (okay YOU) have solved the problem. ... >> in criteria for all fields in my Query. ... >> If I enter a state in my Master Record and then leave State blank (or ...
    (microsoft.public.access.queries)
  • Re: Query to search entire record, not one field?
    ... I have a form with unbound fields now, ... >> in criteria for all fields in my Query. ... >> If I enter a state in my Master Record and then leave State blank (or ...
    (microsoft.public.access.queries)