Using IIf in a query

[dmkennard2]

Hi,
I have a form with a subform on it that is filtered by selecting entries in the dropdown boxes.
I would also like to be able to put a checkbox on the form that allows me only to show the records that have differences between two values.

This is the code i have built, but it does not work and it hides all data. I have put it in the Criteria of WCS_Qty in the Query.

IIf(([Forms]![frmData]![check27])=True,([WCS_Qty])<>([System_Qty]),[WCS_Qty])

Any ideas?

Dazz

 

[SgtJarrow]

The format for IIF is:

IIF(evaluationstatement,the return if evalstatement is true, the return if evalstatement is false)

Your IIF seems to read:

If frmData!check27 is true then return true is wcs_qty is not equal to system_qty or false if wcs is equal to system_qty
if frmdata!check27 is false then return wcs_qty

I think you may have a logic issue here...What are you trying to say?

If frmData!check27 is true then ????
If frmData!chec27 is false the ????

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[GhostWolf]

IIf(([Forms]![frmData]![check27])=True,
([WCS_Qty])<>([System_Qty]),
[WCS_Qty])

Assuming you want nothing displayed if the two values match, I think you need something like:

iif([Forms].[frmData]![Check27]=True and WCS_Qty<>System_Qty,WCS_Qty,"")

Basically: if (the box is checked and the quantities don't match, then print the WCS_Qty, else print a space).

HTH

 

[dmkennard2]

Hey, thanks for the quick response.

What i need the statement to say is:
If the checkbox has tick in it then only show the records where the values System_Qty and WCS_Qty are different else show all records regardless of the difference.

Dazz

 

[SgtJarrow]

Can you post your whole SQL statement from your query? this can be done but will probably be a bit for than a simple Criteria and it would be easier to work with your field names.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[SgtJarrow]

Open your query in design mode.
Put the following in the first blank field in the query designer:

Field: [WCS_Qty])<>([System_Qty]
Table: (Leave blank)
Sort: (Leave blank(
Show: Unchecked
Criteria: Like IIF(Forms![frmData]![Check27]=True, -1, "*")


That should do it. If not, let me know.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[GhostWolf]

Oh!! Sounds like you're needing a Where clause instead of If.

Try changing your Criteria to something like:
([Forms].[frmData]![Check27] = True and WCS_Qty<>System_Qty) or ([Forms].[frmData]![Check27] = False)

 

[dmkennard2]

Thanks very much.
mstrmage your version works a treat. The SQL i ended up with is:

SELECT tblMainData.Handling_Unit, tblMainData.Material, tblMainData.SUT, tblMainData.WCS_Qty, tblMainData.SAP_Qty, tblAisle.Aisle, tblSide.Side, tblLocation.Location, tblLevel.Level, tblBack_Front.[Back/Front], tblStorage_Bin.Bin
FROM tblStorage_Bin RIGHT JOIN (tblSide RIGHT JOIN (tblLocation RIGHT JOIN (tblLevel RIGHT JOIN (tblBack_Front RIGHT JOIN (tblAisle RIGHT JOIN tblMainData ON tblAisle.Aisle = tblMainData.Aisle) ON tblBack_Front.[Back/Front] = tblMainData.[Front/Back]) ON tblLevel.Level = tblMainData.Level) ON tblLocation.Location = tblMainData.Location) ON tblSide.Side = tblMainData.Side) ON tblStorage_Bin.Bin = tblMainData.Bin
WHERE (((tblAisle.Aisle) Like fCboSearch([Forms]![frmData]![cmbAisle])) AND ((tblSide.Side) Like fCboSearch([Forms]![frmData]![cmbSide])) AND ((tblLocation.Location) Like fCboSearch([Forms]![frmData]![cmbLocation])) AND ((tblLevel.Level) Like fCboSearch([Forms]![frmData]![cmbLevel])) AND ((tblBack_Front.[Back/Front]) Like fCboSearch([Forms]![frmData]![cmbFrontBack])) AND (([WCS_Qty]<>[SAP_Qty]) Like IIf([Forms]![frmData]![Check27]=True,-1,"*")))
ORDER BY tblAisle.Aisle, tblLocation.Location, tblLevel.Level;

If you can understand it!!

Ghost, your example added two columns and added 5 extra or statements, but seemed to be doing the same thing.

Thanks again

Dazz

 

[PHV]

A simpler way for this:
AND (([WCS_Qty]<>[SAP_Qty]) Like IIf([Forms]![frmData]![Check27]=True,-1,"*")))
is:
AND (WCS_Qty<>SAP_Qty OR [Forms]![frmData]![Check27]=False) )

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886