A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Additional fields for form based parameter query/null fields



 
 
Thread Tools Display Modes
  #1  
Old January 4th, 2005, 12:35 AM
geeksdoitbetter
external usenet poster
 
Posts: n/a
Default Additional fields for form based parameter query/null fields

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.
  #2  
Old January 7th, 2005, 06:08 AM
Tim Anderson
external usenet poster
 
Posts: n/a
Default

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?=
wrote in
:

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.


  #3  
Old January 7th, 2005, 11:05 PM
geeksdoitbetter
external usenet poster
 
Posts: n/a
Default

*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?=
wrote in
:

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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Report Based Upon Parameter Query with Form References Vincent DeLuca Setting Up & Running Reports 4 July 19th, 2004 01:55 AM
Control value based on parameter query ORSP New Users 2 July 16th, 2004 05:42 PM
Populate two+ fields on a Form, based on a pop up form's record Michael Miller Using Forms 0 June 9th, 2004 02:56 PM
Multiple ranks per record based on different fields. Looney Running & Setting Up Queries 4 May 28th, 2004 11:31 PM


All times are GMT +1. The time now is 01:30 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.