check box on search form

[tizwaz]

I have a check box on a search form which if ticked should include all closed contracts when running the query.

In the query I have the field Closed Date and am trying to put as the criteria something like

IIf([forms]![frmSearchfileref]![chkclosed]=0,"is null","")

the idea being that if the check box is ticked then there is no criteria in the Date closed field therefore returning all entries but if the check box is not ticked then the criteria is is null so that all entries with no closed date are excluded but I am getting an error (this expression is too complicated etc..)

 

[PHV]

You may try this criteria:
Is Null OR [Forms]![frmSearchfileref]![chkclosed]=True

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

 

[tizwaz]

that runs the query but it doesn't exclude the closed entries whether the box is ticked or not. Perhaps I should mention I have entered the criteria on 2 lines of the query grid as I already have the entry [forms]![frmsearchfileref]![txtfileref].[value]in both the file ref and the related file ref field

 

[PHV]

I posted a single line criteria !

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

 

[tizwaz]

sorry - is it possible to do it with a 2 line query?

 

[PHV]

Sorry, I don't understand your question.
Why not posting your actual SQL code ?

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

 

[tizwaz]

This is what I started with

SELECT TblContract.ContractID, TblContract.FileRef, TblContract.SupplierID, TblContract.Title, TblContract.StartDate, TblContract.AnticEndDate, TblContract.BusTeam, TblContract.RelatedFileRef, TblContract.ContractClosed
FROM TblContract
WHERE (((TblContract.FileRef)=[forms]![frmsearchfileref]![txtfileref].[value]) AND ((TblContract.ContractClosed)=IIf([forms]![frmsearchfileref]![chkclosed]=0,"is null",""))) OR (((TblContract.RelatedFileRef)=[forms]![frmsearchfileref]![txtfileref].[value]) AND ((TblContract.ContractClosed)=IIf([forms]![frmsearchfileref]![chkclosed]=0,"is null","")));

 

[PHV]

You may try this SQL code:
SELECT ContractID, FileRef, SupplierID, Title, StartDate, AnticEndDate, BusTeam, RelatedFileRef, ContractClosed
FROM TblContract
WHERE [Forms]![frmsearchfileref]![txtfileref] IN (FileRef, RelatedFileRef)
AND (ContractClosed Is Null OR [Forms]![frmSearchfileref]![chkclosed]=True)

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

 

[tizwaz]

Thanks a lot - that works great now for that search form and another like it. However I've hit another problem on the third search form. This one has a combo box in which column 1 is the supplierID and column 2 is the name of the supplier. column 1 is hidden (width set to 0)The supplier ID is the bound column

Before I started trying to add the part about including closedcontracts the query picked up what I was selecting in the combo box but now it won't. If I run the query directly from the grid and type in the supplier ID the new SQL works. How can I make it match the name in the combo box to the supplierID?

 

[tizwaz]

the SQL for the query behind this form is similar to the one you gave me before except it is where [forms]![frmsearchsupplier]![cbosupplier]IN (Tblcontract.SupplierID,TblRelatedSupplier.supplierID)
The query runs but returns nothing. If I run the query from the grid and type in a supplier name I get nothing. if I type in a supplier ID I get results. I thought my combo box was passing the ID to the query as my bound column is the ID one. Do you know what I am doing wrong?