IIf return null values

[jasn_78]

I have a query I am creating where if a check box is ticked i want to return all null values of a specific field

this code is the line I am having problems with

((ACMFTBL.ACMF_SYSC_NUMBER)=IIf([Forms]![frmACCSTATEMENTS]![chkSTORE]=0,[SYSCTBL].[SYSC_NUMBER],(ACMFTBL.ACMF_SYSC_NUMBER) Is Null)))

I get an error saying the expressions is typed incorrectly or it is too complex to be evaluated.

Any suggestions would be appreciated

Jason

 

[WayneRyan]

Jason,

To me, that says:

If chkSTORE isn't checked, return --> [SYSC_NUMBER]
otherwise, return --> ACMFTBL.ACMF_SYSC_NUMBER Is Null

ACMFTBL.ACMF_SYSC_NUMBER = IIf([Forms]![frmACCSTATEMENTS]![chkSTORE] = 0,  <-- Condition
                               [SYSCTBL].[SYSC_NUMBER],                    <-- If True  ... that's a Number
                               ACMFTBL.ACMF_SYSC_NUMBER Is Null)           <-- If False ... that's a Boolean

Since this is the Queries section, I'm assuming this is in a Where clause.


Criteria:

[Forms]![frmACCSTATEMENTS]![chkSTORE] And ACMFTBL.ACMF_SYSC_NUMBER Is Null


BUT that will only return a bunch of Nulls!

Need more info,
Wayne

 

[jasn_78]

Wayne, what i am trying to say is if this check box is ticked return all accounts who use the account in multiple sites (which in this stupid database is a null value) otherwise if that box isnt ticked show accounts where the store number = the accounts store number which as you have said below seems like i am doing but cant see why it isnt working

 

[WayneRyan]

Jason,

We appear to have really lost me here. I think you have two tables here:

SYSCTBL and ACMFTBL

And, I think that you want the CheckBox to determine which table you get
the data from.

If that's the case ...

Select F1, F2, F3
From   SYSCTBL 
Where  [Forms]![frmACCSTATEMENTS]![chkSTORE] = True 
   UNION
Select F1, F2, F3
From   ACMFTBL 
Where  [Forms]![frmACCSTATEMENTS]![chkSTORE] = False

Just make sure that the number/datatypes "match up" in each query.

Just a guess,
Wayne

 

[jasn_78]

Sorry wayne here is all my sql

SELECT ACMFTBL.ACMF_NUMBER AS ACC_NUMBER, 
	ACMFTBL.ACMF_NAME AS ACC_NAME, 
	ACMFTBL.ACMF_ADDRESS_1 AS ADDRESS1, 
	ACMFTBL.ACMF_ADDRESS_2 AS ADDRESS2, 
	ACMFTBL.ACMF_ADDRESS_3 AS ADDRESS3, 
	ACMFTBL.ACMF_POST_CODE AS POSTCODE, 
	ACMFTBL.ACMF_PHONE_NO AS PHONE_NO, 
	ACMFTBL.ACMF_FAX_NO AS FAX_NO, 
	Round(ACMFTBL.ACMF_CURR_BALANCE,2) AS BALANCE, 
	SYSCTBL.SYSC_COMPANY AS STORE, 
	SYSCTBL.SYSC_ADDR_1 AS STORE_ADDRESS1, 
	SYSCTBL.SYSC_ADDR_2 AS STORE_ADDRESS2, 
	SYSCTBL.SYSC_ADDR_3 AS STORE_ADDRESS3, 
	SYSCTBL.SYSC_POST_CODE AS STORE_POSTCODE, 
	SYSCTBL.SYSC_ABN_NO AS STORE_ABN, 
	ACOPTBL.ACOP_TRX_DATE AS TRX_DATE, 
	Sum(([ACOPTBL]![ACOP_TRX_AMT])*IIf([TRXTYPE]="Cash Receipt",-1,1)) AS TRX_AMOUNT, 	ACOPTBL.ACOP_TRX_TYPE AS TRXTYPE, 
	ACOPTBL.ACOP_REF AS REFERENCE, 
	IIf(Nz([ACMF_TERMS],0)=0,[SYSCTBL]![SYSC_DEFAULT_TERMS],[ACMFTBL]![ACMF_TERMS]) AS TERMS, 	ACMFTBL.ACMF_EMAIL, 
	qryACCBFORWARD.AMOUNT AS BFORWARD, 
	Sum(qryACCPAYMENTS.TOTALS) AS TOTALS, 
	ACOPTBL.ACOP_NARR, 
	ACMFTBL.ACMF_SYSC_NUMBER
FROM SYSCTBL, 
	ACOPTBL, 
	(qryACCBFORWARD INNER JOIN ACMFTBL ON qryACCBFORWARD.ACOP_ACCT_NO = ACMFTBL.ACMF_NUMBER) 
	INNER JOIN qryACCPAYMENTS ON ACMFTBL.ACMF_NUMBER = qryACCPAYMENTS.ACMF_NUMBER
WHERE (((SYSCTBL.SYSC_NUMBER)=[Forms]![frmACCSTATEMENTS]![comboSTORE]) 
AND ((ACMFTBL.ACMF_NUMBER)=[ACOPTBL].[ACOP_ACCT_NO]) 
AND ((ACOPTBL.ACOP_TRX_DATE) 
	Between [Forms]![frmACCSTATEMENTS]![txtDATEFROM] And [Forms]![frmACCSTATEMENTS]![txtDATETO]) 
AND ((ACMFTBL.ACMF_SYSC_NUMBER)=
		IIf([Forms]![frmACCSTATEMENTS]![chkSTORE]=0,[SYSCTBL].[SYSC_NUMBER],			(ACMFTBL.ACMF_SYSC_NUMBER)=[SYSCTBL].[SYSC_NUMBER] 
			Or (ACMFTBL.ACMF_SYSC_NUMBER) Is Null))) 
OR (((ACMFTBL.ACMF_NUMBER)=[ACOPTBL].[ACOP_ACCT_NO]) 
AND ((ACOPTBL.ACOP_TRX_DATE) 
	Between [Forms]![frmACCSTATEMENTS]![txtDATEFROM] 
	And [Forms]![frmACCSTATEMENTS]![txtDATETO]) 
AND (([Forms]![frmACCSTATEMENTS]![comboSTORE]) Is Null))
GROUP BY ACMFTBL.ACMF_NUMBER, 
	ACMFTBL.ACMF_NAME, 
	ACMFTBL.ACMF_ADDRESS_1, 
	ACMFTBL.ACMF_ADDRESS_2, 
	ACMFTBL.ACMF_ADDRESS_3, 
	ACMFTBL.ACMF_POST_CODE, 
	ACMFTBL.ACMF_PHONE_NO, 
	ACMFTBL.ACMF_FAX_NO, 
	SYSCTBL.SYSC_COMPANY, 
	SYSCTBL.SYSC_ADDR_1, 
	SYSCTBL.SYSC_ADDR_2, 
	SYSCTBL.SYSC_ADDR_3, 
	SYSCTBL.SYSC_POST_CODE, 
	SYSCTBL.SYSC_ABN_NO, 
	ACOPTBL.ACOP_TRX_DATE, 
	ACOPTBL.ACOP_TRX_TYPE, 
	ACOPTBL.ACOP_REF, 
	IIf(Nz([ACMF_TERMS],0)=0,[SYSCTBL]![SYSC_DEFAULT_TERMS],[ACMFTBL]![ACMF_TERMS]), 
	ACMFTBL.ACMF_EMAIL, 
	qryACCBFORWARD.AMOUNT, 
	ACOPTBL.ACOP_NARR, 
	ACMFTBL.ACMF_CURR_BALANCE, 
	ACMFTBL.ACMF_SYSC_NUMBER
HAVING (((ACMFTBL.ACMF_NUMBER) 
	Between [Forms]![frmACCSTATEMENTS]![txtACCFROM] 
	And [Forms]![frmACCSTATEMENTS]![txtACCTO])) 
OR ((([Forms]![frmACCSTATEMENTS]![txtACCFROM]) Is Null));

so what the query does is creates an account statement for a range of accounts the sysctbl is the table that stores the company or "store" information and the ACMFTBL is the "account masterfile" table so what I am trying to do is if the account has a store selected which means the account can only be used for that store use the details of that store otherwise if no store is chosen, which means the account can be used across multiple store to use the headings from the store selected in a combo box on the form.