Left Join query on form shows null state on chkbox and blank state on query (1 Viewer)

[Fernando]

I wanted to let the user choose which jobs he wanted on his reports and save the selection for the future so i created a new table .
tblJobView
- JobID as FK
- View as yes/no field

It works fine (the report shows the jobs selected only), the only downside is it shows null state checkboxes (as expected because record doesn't exist) so it confuses the user. However for some reason in the query view shows blanks when record doesn't exist.

So is there a way to show Blank checkboxes instead of Null checkboxes??

I was thinking of creating a new field for the job table but, since i have many reports, i would have to create one field for each one. Instead i would create a Report Table and add ReportID as FK on tblJobView but i will still have the same null checkboxes.

Attached the form and query where you can see one shows the nulls and the query doesn't.

Edit:

Im using this for the query

SELECT qryActiveJobs.JobID, qryActiveJobs.JobName, tblJobView.View
FROM qryActiveJobs LEFT JOIN tblJobView ON qryActiveJobs.JobID = tblJobView.JobID;

Because i dont have a record on tblJobView for each in qryActiveJobs. tblJobView records will be created when needed (or when the user selects it at least once in the form).

the form picture attached is using the query next to it.

 

[Telecom]

On your Query selection for the YesNo field. Add criteria under it.

Is Not Null

Try that...

 

[Fernando]

That wont work because it will show me only the records he selected in the past at least once (so it would have created a record on tblJobView). And i want to show all Job records like im doing on the example.

 

[Fernando]

Im using this for the query

SELECT qryActiveJobs.JobID, qryActiveJobs.JobName, tblJobView.View
FROM qryActiveJobs LEFT JOIN tblJobView ON qryActiveJobs.JobID = tblJobView.JobID;

Because i dont have a record on tblJobView for each in qryActiveJobs. tblJobView records will be created when needed (or when he selects it at least once in the form).

 

[Telecom]

I'm trying to re-create your queries and fields. I have two questions...

What table is your JobName field stored?

Not sure what you mean by FK. Do you mean Long Integer or AutoNumber?

 

[Fernando]

lol Telecom, ok lets put it this way.
This is my info
tblJob
-JobID as PK
-JobName as Text
-Inactive as boolean
and other non significant fields for this problem

qryActiveJobs
SELECT tblJob.JobID, tblJob.Jobname
FROM tblJob
WHERE (((tblJob.Inactive)=0));

tblJobView
-JobID as FK
-View as boolean

qryfrmSelectReport
SELECT qryActiveJobs.JobID, qryActiveJobs.JobName, tblJobView.View
FROM qryActiveJobs LEFT JOIN tblJobView ON qryActiveJobs.JobID = tblJobView.JobID;

Create dummy job records
Create a form bounded to qryfrmSelectReport
You will see all checkboxes are null so select some of them and unselect some of the ones you already selected
You will see null state,blank state and checked state checkboxes
Recreation complete

 

[Telecom]

I see what your saying where it shows all the Null Values in the Form. Unlike you I'm seeing that both in the query and form.

I thought I had it with a IIF statement or Nz statement but it didn't work. I'll mess more with it on Monday.

 

[Fernando]

You cant have them with IFF or NZ because then you cant update them, thanks anyways.