A different Like IIf Issue

[tjcinnamon]

I have a query that the user can customize. It has a number of condtions to sort by, Vendor, FileNumber, InvoiceNumber, Person, Allocation, ect...

In the conditions of the query I use a like iif statement for all of the combo boxes:

Like IIf(IsNull([Forms]![QuerySwitchboard]![InvoiceCombo]),"*",[Forms]![QuerySwitchboard]![InvoiceCombo]) all of which work fine except for the FileNumber (which is actually a string). Here is the statement: Like IIf(IsNull([Forms]![QuerySwitchboard]![FileNumberCombo]),"*",[Forms]![QuerySwitchboard]![FileNumberCombo])

It's exactly the same as the Invoice number Like IIf, so I'm not sure why this statement is not working.Could it be because FileNumber is a foreign key in a table.

Ideas appreciated,
JOe K.

 

[KeithG]

what error are your receive?

 

[boblarson]

all of which work fine except for the FileNumber (which is actually a string). Here is the statement: Like IIf(IsNull([Forms]![QuerySwitchboard]![FileNumberCombo]),"*",[Forms]![QuerySwitchboard]![FileNumberCombo])

Like IIf(IsNull([Forms]![QuerySwitchboard]![FileNumberCombo]) [B][COLOR="red"]OR [Forms]![QuerySwitchboard]![FileNumberCombo]=""[/COLOR][/B],"*",[B][COLOR="red"]"'" &[/COLOR][/B] [Forms]![QuerySwitchboard]![FileNumberCombo] [B][COLOR="Red"]& "'"[/COLOR][/B])

 

[tjcinnamon]

It's not interpreting the IIf statement as true or false because I just giving me jobs with out a FileNumber, It will not carry out the Then or the Else portion.

in fact even if I manually type in the criteria where I know there is a FileNumber to be filtered by it still returns all blank FileNumbers!

I have no idea what is going on

 

[boblarson]

Personally, I never let users work with queries directly. I give them forms to select the items they want and then open the queries based on the input. If I were you, I would get away from doing direct entry in queries and move to forms where you can have more control over things, including events for coding things like this.

 

[boblarson]

I'll have to wait to get home to look as I don't have A2K3 at work. I have 2000 at work and 2000, 2002 (not currently installed), 2003, and 2007 at home.

 

[tjcinnamon]

This is in Access 2000 I had to convert it for a friend. I deleted my previous 2k3 post to save server space.

View attachment Copy of PO Database.zip

I have it controlled through a form. I have attached the DB, I inherited a good portion of it and it needs alot of Normalization, but I have the Queryswitchboard auto open. You can press F9 or F10 to get the query, you set the criteria in the combo boxes. The one that doesn't work is the file number.

 

[neileg]

When I look at the query I see that under Our Number has the criterion

Like IIf(IsNull([Forms]![Query Switchboard]![File Number Combo]),"*",[Forms]![Query Switchboard]![File Number Combo])

on the first criteria row and blank in the second. If you duplicate this criterion in the second row it works. It's to do with the differing criteria at the right hand end of the query.

Messy design, though.

 

[Brianwarnock]

It was a bit confusing until I discovered that file number is actually our number in the purchase order table. Your code works as exopected if run on that table by itself ie

Damn just looked down to check on something and see that neileg has beaten me to it

Brian

 

[tjcinnamon]

It only returns all the records with an Our Number not null, if the criteria is blank I want it to return all Our Numbers (equaling null, and not null)

 

[tjcinnamon]

SELECT   [Purchase Orders].[PO Number], [Purchase Orders].Date, [Vendor Details Table].Vendor, [Purchase Orders].[Invoice Number],   [Purchase Orders].[Our Number], [Purchase Orders].[Okay to Pay], [PO Details Table].Item, [PO Details Table].Allocation,   [PO Details Table].[Unit Price], [PO Details Table].Quantity, [Unit Price]*[Quantity] AS SubTotal FROM   [Vendor Details Table]     INNER JOIN      ([Purchase Orders]        INNER JOIN [PO Details Table] ON [Purchase Orders].[PO Number] = [PO Details Table].[PO Number])     ON [Vendor Details Table].[Vendor Details ID] = [Purchase Orders].[Vendor Number] WHERE   (([Purchase Orders].Date Between [Forms]![Query Switchboard]![DateCombo] And [Forms]![Query Switchboard]![DateRange Combo]) OR  [Forms]![Query Switchboard]![DateCombo] Is Null)AND   ([Vendor Details Table].Vendor Like [Forms]![Query Switchboard]![Vendor Search Combo Box] OR [Forms]![Query Switchboard]![Vendor Search Combo Box] Is Null) AND   ([Purchase Orders].[Invoice Number] Like [Forms]![Query Switchboard]![Invoice Combo] OR [Forms]![Query Switchboard]![Invoice Combo] Is Null) AND   ([Purchase Orders].[Our Number] Like [Forms]![Query Switchboard]![File Number Combo] OR [Forms]![Query Switchboard]![File Number Combo] Is Null) AND    ([Purchase Orders].[Okay to Pay] Like [Forms]![Query Switchboard]![Person Combo] OR [Forms]![Query Switchboard]![Person Combo] Is Null) AND    ([PO Details Table].[Item] Like [Forms]![Query Switchboard]![Item Combo] OR [Forms]![Query Switchboard]![Item Combo] Is Null) AND    ([PO Details Table].[Allocation] Like [Forms]![Query Switchboard]![Allocation Combo] OR [Forms]![Query Switchboard]![Allocation Combo] Is Null) AND   [Purchase Orders].Locked=No ORDER BY    [Purchase Orders].[PO Number]

I used this SQL Statement and it Works but in query view, I have about 200 criteria, it doesn't seem that elegant (niether is my DB yet). Is there a way to consolidate the criteria??

 

[Brianwarnock]

Like will not return null values, I suggest you read the thread by JON K in the code samples, I think it is called Query by Form.

Brian

 

[Brianwarnock]

Turns out it is in sample databases

http://www.access-programmers.co.uk/forums/showthread.php?t=103312

Brian