Puzzle with query expression

[Frank]

I have the following in a database:

MainTable - - fields- phone1, phone2 phone3 phone 4

MainQuery - based on Main table with all four fields


Report - all four fields in the query


Information stored in each of the field are numbers but I replaced null
values in all fields with the text "n/a".
I did this so that the report would not show any null values.

What I what the report to reflect is this.

If a record has a "n/a" entry in all four fields, I would like the report
not show that record but show all other records that have valid telephone
numbers even if some of the fields have a "n/a" value.

For example, record one has the following data: 122 111 1111, 121 111
1111, n/a, n/a. That record would show in the report; however, record
two has the following data: n/a, n/a, n/a, n/a - it would not show but
all other records that don't have n/a in all fields would show.

How would I do this?

Any assistance is greatly appreciated.


Frank

 

[Pieter Wijnen]

Use The Detail Format Event

Private Sub Detail_Format(FormatCount As Integer, Cancel As Integer)
Cancel= IsNull(Me.Ctl1.Value) And IsNull(Me.Ctl2.Value) And
IsNull(Me.Ctl3.Value) And IsNull(Me.Ctl4.Value)
'Cancel = Me.Ctl1.Value="n/a" And Me.Ctl2.Value="n/a" .....
End Sub

Pieter

 

[John W. Vinson]

I have the following in a database:

MainTable - - fields- phone1, phone2 phone3 phone 4

Well, then your table is not properly normalized. Someday you'll need FIVE
phones, and you'll be stuck! You should instead have a one to many
relationship to a Phones table, with zero, one, four, or seven records per
person. A Crosstab query will give you the "wide flat" view for reporting
purposes, with no need for null phones or "n/a" entries.

MainQuery - based on Main table with all four fields


Report - all four fields in the query


Information stored in each of the field are numbers but I replaced null
values in all fields with the text "n/a".
I did this so that the report would not show any null values.

What I what the report to reflect is this.

If a record has a "n/a" entry in all four fields, I would like the report
not show that record but show all other records that have valid telephone
numbers even if some of the fields have a "n/a" value.

For example, record one has the following data: 122 111 1111, 121 111
1111, n/a, n/a. That record would show in the report; however, record
two has the following data: n/a, n/a, n/a, n/a - it would not show but
all other records that don't have n/a in all fields would show.

How would I do this?

A criterion of

<> "N/A"

on each of the four phone fields on the same row in the query grid should do
this.

John W. Vinson [MVP]

 

[Frank]

Peter:

Thanks for follow up. I am somewhat new to this. Should my code look like
this:


Cancel=IsNull(me.phone1.value="n/a").... for all fields.







"Pieter Wijnen"

 

[Pieter Wijnen]

yes, follow the IsNull "template"
I do however agree with Larry, I'd never set it up this way myself

Pieter

 

[Frank]

Peter:

Thanks for reply. I tried your code as noted. It compiled correctly;
however, it did not filter out records that had N/A in each of the phone
number fields. What did I do wrong? Note that the report is based on a
Table rather than a query. Sorry for overlooking this.




"Pieter Wijnen"

 

[Frank]

Thanks John:

When trying your solution, the query returns only those records that have
all number fields completed with phone numbers. That is not exactly what I
was looking for. As noted, I would like the query to exclude all records
that have N/A in all phone number fields. Thus, for example, a record could
have:

xxxxx, N/A,N/A,N/A
or
xxxxx, xxxxx,N/A,N/A
or
xxxxx, xxxxx,xxxxx,N/A
or
xxxxx,xxxxx,xxxxx,xxxxx

No N/A, N/A,N/A,N/A records would be
returned.


Sorry for any misunderstanding I may have caused.

 

[Pieter Wijnen]

then you should use the IsNull approach instead, referencing the actual
fields
dependet on your access version you'd also need to add the actual fields as
(hidden) controls on the report
As a general note : always use Queries as the base for Forms/Reports

Pieter

 

[John W. Vinson]

When trying your solution, the query returns only those records that have
all number fields completed with phone numbers

Hrm. Shouldn't! Please open the query in SQL view and post the SQL text here.
Sounds like you may have some OR conditions, or some *other* conditions that
are interfering.

John W. Vinson [MVP]

 

[Frank]

Hi John

See SQL text below as requested:


SELECT Addresses.Phone1, Addresses.Phone2, Addresses.Phone3,
Addresses.Phone4
FROM Addresses
WHERE (((Addresses.Phone1)<>"N/A") AND ((Addresses.Phone2)<>"N/A") AND
((Addresses.Phone3)<>"N/A") AND ((Addresses.Phone4)<>"N/A"));

 

[Frank]

Hi Pieter:

Moved report to query, put your code in report detail section, the filtered
results still do not work as the report returns all records in the query.
See my post to John's most recent post.





"Pieter Wijnen"

 

[Pieter Wijnen]

Hmm, your query requires data to be entered in all fields & my suggestion
doesn't work...
which means ZLS is allowed (zero length strings)
first execute the query
UPDATE MyTable
Set Phone1 = Null
Where Phone1 =""

For all phone fields

and remove allow ZLS for the fields in the table, (why this "feature" is
allowed & even the default in Jet db's is beyond my comprehension)

then your query should work id you change it to
SELECT Addresses.Phone1, Addresses.Phone2, Addresses.Phone3,
Addresses.Phone4
FROM Addresses
WHERE phone1 is not null and phone2 is not null and phone3 is not null and
phone4 is not null

hth

Pieter

 

[Frank]

Pieter:

The address table is already set to disallow zero lengths. Still can'g get
to work.


"Pieter Wijnen"

 

[John W. Vinson]

Hi John

See SQL text below as requested:


SELECT Addresses.Phone1, Addresses.Phone2, Addresses.Phone3,
Addresses.Phone4
FROM Addresses
WHERE (((Addresses.Phone1)<>"N/A") AND ((Addresses.Phone2)<>"N/A") AND
((Addresses.Phone3)<>"N/A") AND ((Addresses.Phone4)<>"N/A"));

Does the table actually contain the text string "N/A" in all these fields - or
does it contain NULLs which are just being displayed as N/A? As written, a
record containing "N/A" in all four of these fields should NOT be retrieved or
displayed; records containing phone numbers, NULLs, or any other text in any
of the fields will be shown.


John W. Vinson [MVP]

 

[Frank]

Hi John:

None of the subject fields have null values in them. It's either a phone
number or the "N/A" text. I checked the table the query is based on and the
allow zero string is disabled for all phone fields.

Your last paragraph is correct.

 

[John W. Vinson]

Hi John:

None of the subject fields have null values in them. It's either a phone
number or the "N/A" text. I checked the table the query is based on and the
allow zero string is disabled for all phone fields.

Your last paragraph is correct.

Ok... my last paragraph was:

a record containing "N/A" in all four of these fields should NOT be retrieved

and you said:

As noted, I would like the query to exclude all records
that have N/A in all phone number fields.

Those sound to me like they're synonymous. Am I misunderstanding?

John W. Vinson [MVP]

 

[Frank]

No, that' right.

If all four fields have N/A in them they should not be retrieved. Any other
combination should be retrieved.

 

[John W. Vinson]

If all four fields have N/A in them they should not be retrieved. Any other
combination should be retrieved.

And that's not what my query is doing???

John W. Vinson [MVP]

 

[Frank]

John:

That is right.

Your query functions sort of like a filter within a filter. You filter for
one set of criteria, then within that filter, you filter for another. In
your query, it working like that. The end result of it is that it return
only two records, those that have phone numbers in all four fields. It seems
that it working in reverse.

Maybe this is beyond what Access can handle.

 

[J_Goddard via AccessMonster.com]

Hi -

Did you verify that none of the "N/A" fields accidentally contain leading
blanks? Trailing blanks are not usually a problem, but leading ones are (or
can be)

John

John:

That is right.

Your query functions sort of like a filter within a filter. You filter for
one set of criteria, then within that filter, you filter for another. In
your query, it working like that. The end result of it is that it return
only two records, those that have phone numbers in all four fields. It seems
that it working in reverse.

Maybe this is beyond what Access can handle.
[quoted text clipped - 3 lines]

John W. Vinson [MVP]