Additional fields for form based parameter query/null fields

[Guest]

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/assistance/HA011170771033.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.

 

[Tim Anderson]

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?=

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/assistance/HA011170771033.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.

 

[Guest]

*smacks forehead*

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

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?=

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/assistance/HA011170771033.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.