Hi, there.

I'm using Access 2002. I've made a form based parameter query using the same
help column ( http://office.microsoft.com/en-us/as...170771033.aspx
), and i also ran into the Is Null problem (described in the question from
1/1/05 titled "Help with form based parameter query/null fields") .

I added "None" to the value list for the ComboName, then expanded the
criteria based on the suggestion below from Steve Schapel, Microsoft Access
MVP:

> [Forms]![formname]![ComboName] Or Is Null And
> [Forms]![formname]![ComboName]="none"
> (watch for wordwrap in the newsreader... the above is all meant to be on
> one line!)

when i closed/reopend the query i found this in my original ComboName field:

Criteria: [Forms]![formname]![ComboName]
Or: Is Null

and a new field had been added:

Field Name: [Forms]![formname]![ComboName]
Or: "None"

The form works, but it's curious.

Second question - i'd like to be able to seach by different three fields on
my form. The first (described above) uses a combo box to search a text field.
The other two my user can type into directly as they will search memo fields.
The criteria for the second and third fields currently reads:

Like "*" & [Forms]![Test]![Select SecondField] & "*"
Like "*" & [Forms]![Test]![Select ThirdField] & "*"

I can't think what code to use in the criteria section so that my form will
return complete a recordset when when one or two of the fields have null
records. Right now, it only returns the Not Null records.

Example: There are 15 records with the same data in all three fields (but 5
of these records are missing data in field 2). When you use the form to seach
for "Name" in field 1, you should get 15 records, yes? I'm only getting the
10 (where all there fields are full).

I'm sorry to ramble so, it's late and i'm sure i've left out imporant
information. Please ask clarifying questions.

Your second question: If you have your form parameters all on one line
(in query design mode - criteria section), then ALL paremeters must be
found. Example:
Field1 Like "*" & [Forms]![FormName]![FormFld1] & "*"
Field2 Like "*" & [Forms]![FormName]![FormFld2] & "*"

If you want this to be an "OR" statement, then you will need to put the
criteria for field1 on criteria line 1 and the criteria for field2 on
line 2. I wish I would give you a screen shot but this is the best I can
do for you. If you look to the left of your criteria section, you will
see the work OR on the second line down. This should give you a visual.

Tim Anderson
Millennium III Mgt Consulting
www.m-iii.org



=?Utf-8?B?Z2Vla3Nkb2l0YmV0dGVy?=
<(E-Mail Removed)> wrote in
news:6ACA1183-76EC-4204-A16D-(E-Mail Removed):

> Hi, there.
>
> I'm using Access 2002. I've made a form based parameter query using
> the same help column (
> http://office.microsoft.com/en-us/as...170771033.aspx ),
> and i also ran into the Is Null problem (described in the question
> from 1/1/05 titled "Help with form based parameter query/null fields")
> .
>

> Second question - i'd like to be able to seach by different three
> fields on my form. The first (described above) uses a combo box to
> search a text field. The other two my user can type into directly as
> they will search memo fields. The criteria for the second and third
> fields currently reads:
>
> Like "*" & [Forms]![Test]![Select SecondField] & "*"
> Like "*" & [Forms]![Test]![Select ThirdField] & "*"
>
> I can't think what code to use in the criteria section so that my form
> will return complete a recordset when when one or two of the fields
> have null records. Right now, it only returns the Not Null records.
>
> Example: There are 15 records with the same data in all three fields
> (but 5 of these records are missing data in field 2). When you use the
> form to seach for "Name" in field 1, you should get 15 records, yes?
> I'm only getting the 10 (where all there fields are full).
>
> I'm sorry to ramble so, it's late and i'm sure i've left out imporant
> information. Please ask clarifying questions.
>

*smacks forehead*

i'm so embarrassed. i've been ignoring the perfectly nice *OR* section -
thank you ever so for point it out!

"Tim Anderson" wrote:

> Your second question: If you have your form parameters all on one line
> (in query design mode - criteria section), then ALL paremeters must be
> found. Example:
> Field1 Like "*" & [Forms]![FormName]![FormFld1] & "*"
> Field2 Like "*" & [Forms]![FormName]![FormFld2] & "*"
>
> If you want this to be an "OR" statement, then you will need to put the
> criteria for field1 on criteria line 1 and the criteria for field2 on
> line 2. I wish I would give you a screen shot but this is the best I can
> do for you. If you look to the left of your criteria section, you will
> see the work OR on the second line down. This should give you a visual.
>
> Tim Anderson
> Millennium III Mgt Consulting
> www.m-iii.org
>
>
>
> =?Utf-8?B?Z2Vla3Nkb2l0YmV0dGVy?=
> <(E-Mail Removed)> wrote in
> news:6ACA1183-76EC-4204-A16D-(E-Mail Removed):
>
> > Hi, there.
> >
> > I'm using Access 2002. I've made a form based parameter query using
> > the same help column (
> > http://office.microsoft.com/en-us/as...170771033.aspx ),
> > and i also ran into the Is Null problem (described in the question
> > from 1/1/05 titled "Help with form based parameter query/null fields")
> > .
> >
>
> > Second question - i'd like to be able to seach by different three
> > fields on my form. The first (described above) uses a combo box to
> > search a text field. The other two my user can type into directly as
> > they will search memo fields. The criteria for the second and third
> > fields currently reads:
> >
> > Like "*" & [Forms]![Test]![Select SecondField] & "*"
> > Like "*" & [Forms]![Test]![Select ThirdField] & "*"
> >
> > I can't think what code to use in the criteria section so that my form
> > will return complete a recordset when when one or two of the fields
> > have null records. Right now, it only returns the Not Null records.
> >
> > Example: There are 15 records with the same data in all three fields
> > (but 5 of these records are missing data in field 2). When you use the
> > form to seach for "Name" in field 1, you should get 15 records, yes?
> > I'm only getting the 10 (where all there fields are full).
> >
> > I'm sorry to ramble so, it's late and i'm sure i've left out imporant
> > information. Please ask clarifying questions.
> >
>
>