Search results

Hi June7, sorry I didn't see it, I wish I had, certainly a cleaner way to fix the SQL Access makes

Hi Guys, thought I would finish this thread with what I've found out was going wrong. I don't know if this is obvious to seasoned users but it certainly caught me out. Basically if you create an IIF function within the Criteria Field (see pic) then Access will generate SQL which wont work...

Hi JHB, yes, that was the end result I was hoping to get, thank you Hypothetically if there were another 10 fields which may have null values I would need to do that for each one :D

Hi jdraw, I wasn't sure of the best way to explain the issue but the basic question is Why is Like "*" valid in criteria but invalid when inside an iif statement

Hi,I've attached the DB If you open the Form you will see 3 listboxes, the top two are getting filtered by the text in the 3 Textboxes, the bottom one is basically the raw table the data is coming from [Simple Filter] shows the qPartsSimpleFilter query but if you look at the 3rd column you...

hi guys, no it's still not working. I'll get a sample of what I'm trying to do. It's basically filtering a query depending on whats in some text boxes on a form

Hi isladogs,thanks for the feedback. The SQL I posted was the simplest way I could recreate the strange result I was getting. The actual SQL is going to be something like this SELECT SimpleTable.ShortText FROM SimpleTable WHERE IIf(ConditionIwillTest,(([SimpleTable].[ShortText]) Like "EN" &...

hi, thanks for looking, whole string:- SELECT SimpleTable.ShortText FROM SimpleTable WHERE (((SimpleTable.ShortText)=IIf(3=3,(([SimpleTable].[ShortText]) Like "*"),(([SimpleTable].[ShortText]) Like "*"))));

Hi Guys, new here, thanks for any help offered. I'm self taught with Access so have limited knowledge and have hit an issue I can't find an answer to. If I use Like "*" in the criteria for a ShortText field, I get the results I expect. However if I use the following SQL I get no results...