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

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?
    ... > in criteria for all fields in my Query. ... > There is not information for the "State" field in my master table for this ... >> Rick B ...
    (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 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)
  • Re: Disappearing criteria!
    ... "Rick B" wrote: ... >> In a query I wrote the following in the 'Criteria' of a field called ... >> At first this worked & gave me the information on all of the Schools if ... >> the same query resulting in the query always giving data on all of the ...
    (microsoft.public.access.gettingstarted)