Query and Chart Questions

[alphaomega3]

I have it as this:

PARAMETERS Startdate DateTime, Enddate DateTime;
SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date, tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA
FROM tblMaster INNER JOIN tblSupplier ON tblMaster.suppName=tblSupplier.Supplier
WHERE (((tblMaster.Date) Between [Startdate] And [Enddate]) AND ((tblMaster.Audit)=False) AND ((tblMaster.PA)=False));

tblMaster.Audit and tblMaster.PA are check boxes so I only want to pull them in where the checkboxes are not checked. If they are checked they mean something else to the database.

[NeoPa]

This SQL looks exactly the same as the one you posted before (post #11).
Was there any point in my posting the comments?
As this is the same AND you haven't explained why AND you haven't posted any MetaData so that I can check for myself, I'm running a bit blind here.
Conceptually, you need to change the JOIN to a LEFT JOIN and check that the linked field is Null but I can't post any SQL that I have confidence in because ...

[alphaomega3]

Okay you are using terminology I am unfamiliar with. I do not understand what Metadata is...... or where to find it. Would I not want the checkbox info to be =False if I dont want it to show up cause if it is checked it means that an Audit has been performed or that a Preventive action has been dispositioned, which is something other than an NCR.

[NeoPa]

Okay you are using terminology I am unfamiliar with. I do not understand what Metadata is...... or where to find it. Would I not want the checkbox info to be =False if I dont want it to show up cause if it is checked it means that an Audit has been performed or that a Preventive action has been dispositioned, which is something other than an NCR.

Sorry about the MetaData thing. I was in a screaming rush to get away from work when I posted. What I would normally have included to accompany that would be this example :

[b]Table Name=tblStudent[/b]
[i]Field; Type; IndexInfo[/i]
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

As far as checking the value of a CheckBox goes, the answer is No.
Like any Boolean type field, the value in the field is actually the result of a comparison. This means it can take the place of the whole comparison in a check.
Consider this :

chkStored = (4 = 4)

==> If 4 = 4 Then... is equivalent to If chkStored Then...
If chkStored is a TRUE value then it will carry out the required action. If not then it won't.
If chkStored = True Then... is equivalent to If (4 = 4) = True Then...
Furthermore, as any numeric value which is not 0 is considered to be TRUE. This will not be treated correctly if compared to the value True (False = 0; True = -1).

chkStored = 5
If chkStored Then MsgBox 1
If chkStored = True Then MsgBox 2

Would only show 1.

[NeoPa]

If you could post MetaData for tblMaster & tblSupplier then I think we can put this to bed :)

[alphaomega3]

I will post the metadata on Monday.... WAY too many deadlines due today...AND More keeps piling up...UGH gotta love Friday afternoon rushes!!

[NeoPa]

No problem. That gives me a bit of a break too :)

[alphaomega3]

I am actually a bit embarrassed to post this here.... I am sure you will look at this and go GEEZ this guy is nuts, but anyway here it is:

Table Name=tblMaster
Field; Type; Indexinfo
rptNumber; Autonumber; PK
ptNumber; Text
Revision; Text
ptDesc; Text
suppName; Text
Originator; Text
audtype; Text
rptDate; Date/Time
poNumber; Text
soNumber; Text
woNumber; Text
QTy; Number
sampSize; Number
Qacc; Number
Qrej; Number
Nonconformance; Text
Specification; Text
Finding; Text
Comments; Text
RTV; Yes/No
rtvqty; Number
Useasis; Yes/No
useasisqty; Number
Rework; Yes/No
rwkqty; Number
Scrap; Yes/No
scrapqty; Number
Audit;Yes/No
CA; Yes/NO
PA; Yes/No
Auditreq; Yes/No
Major; Yes/no
Minor; Yes/no
Observation; Yes/No
Eng; Text
QA; Text
Authorizedby; Text
Assignedto; Text
AssignDate; Date/Time
DueDate; Date/Time
Results; Memo
CAPAtaken; Memo
PA2; Yes/No
CA2; Yes/no
ANSBY; Text
ansdate; Date/Time
QAApp; Text
Reviewby; Text
ReviewDate; Date/Time
clyes;Yes/No
clno;Yes/NO

Table Name=tblSupplier
Field; Type; Indexinfo
Supplier; Text

Don't laugh too hard at how long this is and how it could have been broken up and if I need scolding then do it...

[NeoPa]

Well...At least tblSupplier doesn't look too complicated ;)
Will get to this when I get home tonight.

[NeoPa]

Now I understand why you have the strange tblSupplier link ;)
Try this SQL and tell me if it works for you :

PARAMETERS Startdate DateTime,
           Enddate DateTime;
SELECT M.rptNumber,
       M.suppName, 
       M.Date, 
       M.Nonconformance,
       M.Audit,
       M.PA
FROM tblMaster AS M LEFT JOIN tblSupplier AS S
  ON M.suppName=S.Supplier
WHERE ((M.Date Between [Startdate] And [Enddate])
  AND (Not M.Audit)
  AND (Not M.PA)
  AND (S.Supplier Is Null))

This is working on the basis that only unmatched suppliers are required and that the Audit and PA fields are still both FALSE.
You can have the layout as you need - I have it laid out like this simply for displaying easily in the forums.

[alphaomega3]

Thanks That did it!!!!!!!!!!

Thanks for putting up with me, you have truly been very patient!

[NeoPa]

You're not the first member I've had to tell about this - It's against site rules to sound surprised when my suggestions actually work.

Seriously, that's fine. Someone telling me I'm patient is one for the scrapbook though :D