IIF Statement

[dan1dyoung]

What i am trying to do is:

IIf([Me].[Custom1Checkbox]=-1,([tblPart].[Description]) Like "*" & [Forms]![frmParts]![TxtFilter] & "*",([tblPart].[Description]) Like [Forms]![frmParts]![TxtFilter] & "*")

have tried:

(IIf([Me].[Custom1Checkbox]=True,[tblPart].[Description] Like "*" & [Forms]![frmParts]![TxtFilter] & "*",[tblPart].[Description] Like [Forms]![frmParts]![TxtFilter] & "*"))

and

(IIf([Me].[Custom1Checkbox]=-1,[tblPart].[Description] Like "*" & [Forms]![frmParts]![TxtFilter] & "*",[tblPart].[Description] Like [Forms]![frmParts]![TxtFilter] & "*"))

but with no luck

what i have done to get over it for now is:

Link to post

 

[KenHigg]

Maybe:

IIf([Me].[Custom1Checkbox]=-1,("[tblPart].[Description]) Like '*" & [Forms]![frmParts]![TxtFilter] & "*'",("[tblPart].[Description]) Like '" & [Forms]![frmParts]![TxtFilter] & "*'")

???

 

[dan1dyoung]

IIF statement

Ken,

Thank you for a quick reply, when i try to use it is says the syntax is wrong (missing seperator, bracket,e.t.c)??? Looked OK to me

Any ideas

Dan

 

[mresann]

I emulated the db and query.
I used:

IIf([Me].[Custom1Checkbox]=-1,([tblPart].[Description]) Like "*" & [Forms]![frmParts]![TxtFilter] & "*",([tblPart].[Description]) Like [Forms]![frmParts]![TxtFilter] & "*")

It worked, but I had to use an explicit form reference. Have you tried replacing the relative [Me] with the explicit [Forms]![MyForm] where the check box is located?

 

[dan1dyoung]

I tried it quickly but it still doesnt seem to work, i will try it again after work tomorrow

Thanks for your help

 

[Pat Hartman]

If the IIf() is in a stored querydef, it will not work. You are trying to change the structure of the query and that is not possible. You could build the entire SQL string in VBA and use this techinique to build the criteria part.

 

[dan1dyoung]

Pat,

Thanks for the reply, but being an access novice in comparisoon to the members of this forum, i don't know what you mean.

Please explain more or show me an example

Thanks

Dan

 

[mresann]

Dan,

If your MDB file, zipped, is under 100K then post it. The query I emulated was not VBA, but a query that accessed a form and table to your name specifications (with the exception of the relative "Me" assignor)

 

[KenHigg]

Dan, Pat was pointing out that IIF() does not work everywhere. Where exactly are you using it? On a form? In a query criteria?

 

[dan1dyoung]

It is quite large, i will try to compact or put on a local server.

This is what i have in the query noe:

SELECT tblPart.PartNumber, tblPart.Description, tblPart.Notes
FROM tblPart
WHERE (((tblPart.PartNumber)=IIf([Forms]![frmParts]![Custom1Checkbox]=-1,([tblPart].[Description]) Like "*" & [Forms]![frmParts]![TxtFilter] & "*",([tblPart].[Description]) Like [Forms]![frmParts]![TxtFilter] & "*"))) OR (((tblPart.Description)=IIf([Forms]![frmParts]![Custom1Checkbox]=-1,([tblPart].[Description]) Like "*" & [Forms]![frmParts]![TxtFilter] & "*",([tblPart].[Description]) Like [Forms]![frmParts]![TxtFilter] & "*")))
ORDER BY tblPart.PartNumber;

Any ideas from that??

 

[dan1dyoung]

Sorry Ken,

Had a cached page so did not see your post, it is in the criteria of a query

Basicly what i did before was have 2 queries with the different criterias

query 1:

SELECT tblPart.PartNumber, tblPart.Description, tblPart.Notes
FROM tblPart
WHERE (((tblPart.PartNumber) Like [Forms]![frmParts]![TxtFilter] & "*")) OR (((tblPart.Description) Like [Forms]![frmParts]![TxtFilter] & "*"))
ORDER BY tblPart.PartNumber;


Query 2:

SELECT tblPart.PartNumber, tblPart.Description, tblPart.Notes
FROM tblPart
WHERE (((tblPart.PartNumber) Like "*" & [Forms]![frmParts]![TxtFilter] & "*")) OR (((tblPart.Description) Like "*" & [Forms]![frmParts]![TxtFilter] & "*"))
ORDER BY tblPart.PartNumber;


And then use an if statement in the form for the change of the Custom1Checkbox as here

 

[dan1dyoung]

I have removed a lot of tables, forms, e.t.c and posted it here, there are other problems on the form which i am looking at so any ideas on those would be cool aswell, if not i will address them after the query is done (1 step at a time)


Any ideas??????????????????????

 

[dan1dyoung]

Any help??????