Null fields in tables

Family Mailing List, Personal Profiles, Activities and Skills, Attendance, Visitations

Moderators: Moderators, Tech Support

Post Reply
carpy01
Posts: 25
Joined: Tue Sep 15, 2020 7:33 pm

Null fields in tables

Post by carpy01 »

03/23/23
I create the members email list for our church.
My problem I want to include emails from 9 status codes, without fields with no email.
PowerChurch will not do, except for one status code at a time,. So I built a query in ACCESS to create the list.
Copied the MA and ME tables to ACCESS.
Run query! I got all the members in the status codes,
But I got all the null?, empty?, blank? email fields.
The criteria in the query = email criteria = Is Not Null. (I don't know how powerchurch deals with blank, null, empty fields.
I have tried trimming = doesn't work.
I tried making a new table and then running query on that table. Didn't work.
I tried deleting the email field in the table = no work. I tried replace field ="" , with null, Didn't work.
The only way I can accomplish this is to copy file to excel and manipulate
the data to delete the members with no emails.
If I knew what powerchurch call "blank" fields in a table. I could do in ACCESS.
Dave Carpenter

NeilZ
Posts: 10209
Joined: Wed Oct 08, 2003 1:20 am
Location: Dexter NM
Contact:

Re: Null fields in tables

Post by NeilZ »

carpy01 wrote:
Thu Mar 23, 2023 2:39 pm
03/23/23
I create the members email list for our church.
My problem I want to include emails from 9 status codes, without fields with no email.
PowerChurch will not do, except for one status code at a time,. So I built a query in ACCESS to create the list.
Copied the MA and ME tables to ACCESS.
Run query! I got all the members in the status codes,
But I got all the null?, empty?, blank? email fields.
The criteria in the query = email criteria = Is Not Null. (I don't know how powerchurch deals with blank, null, empty fields.
I have tried trimming = doesn't work.
I tried making a new table and then running query on that table. Didn't work.
I tried deleting the email field in the table = no work. I tried replace field ="" , with null, Didn't work.
The only way I can accomplish this is to copy file to excel and manipulate
the data to delete the members with no emails.
If I knew what powerchurch call "blank" fields in a table. I could do in ACCESS.
Dave Carpenter
First off ... where were you trying to create the email list, what module or report?
Neil Zampella

Using PC+ since 1999.

carpy01
Posts: 25
Joined: Tue Sep 15, 2020 7:33 pm

Re: Null fields in tables

Post by carpy01 »

03/04/23

Need emails per person from ME table based on 9 status codes
I can use the "Email Directory" to create a report.
However the report shows records where the email field
is null, blank or empty (I don't how that field was formated).
When I set it up in ACCESS, query email criteria = Is not null,
it still gives me the "empty" email fields?
I have tried criteria at "=<>", tired IIF statement, tried to replace
empty, null fields. Nothing works!
The only way I have found so far, is to create new table,
and go back and manually delete the empty email fields
one record at a time.
This means that the program thinks these field are
"not null or blank or empty or ?"
I can also copy the table to excel and manipulate there
to get the results I want.
I want to set this up so its a push button operation
that anyone can do.
I'm afraid this is not possible in PowerChurch, reason
I have gone to ACCESS to write complex queries.
Dave Carpenter

NeilZ
Posts: 10209
Joined: Wed Oct 08, 2003 1:20 am
Location: Dexter NM
Contact:

Re: Null fields in tables

Post by NeilZ »

carpy01 wrote:
Fri Mar 24, 2023 1:37 pm
03/04/23

Need emails per person from ME table based on 9 status codes
I can use the "Email Directory" to create a report.
However the report shows records where the email field
is null, blank or empty (I don't how that field was formated).
When I set it up in ACCESS, query email criteria = Is not null,
it still gives me the "empty" email fields?
I have tried criteria at "=<>", tired IIF statement, tried to replace
empty, null fields. Nothing works!
The only way I have found so far, is to create new table,
and go back and manually delete the empty email fields
one record at a time.
This means that the program thinks these field are
"not null or blank or empty or ?"
I can also copy the table to excel and manipulate there
to get the results I want.
I want to set this up so its a push button operation
that anyone can do.
I'm afraid this is not possible in PowerChurch, reason
I have gone to ACCESS to write complex queries.
Dave Carpenter
I was able to do what you wanted using the Personal Profile CUSTOM REPORT feature. You would create 9 status code lines as follows:

Code: Select all

(Personal Status Code exactly matches Member or
Personal Status Code exactly matches Family Member or
Personal Status Code exactly matches Friend of Congregation) and
Email is not an empty field

I selected the personal profile EMAIL data item for output, and only got those records with personal emails.

Have you tried this ??
Neil Zampella

Using PC+ since 1999.

Post Reply