Run-time error 3075

[BonnieG]

I have a SQL search on the main page of my database which gives me run-time error 3075 "Syntax error" whenever my search_text value includes an apostrophe, e.g. "Susan O'Connor"

Here's my code which is triggered when I hit search:

sql_search = "SELECT * from dbo_users 
where job_title not like '*N/A - ACCOUNT RENAMED*' and 
owner_name like '*" & search_text & "*' order by surname asc, forename asc"
 
Forms![frm_main2]![subfrm_main2_search].Form.RecordSource = sql_search

What do I need to add to the syntax to allow apostrophes?

 

[pr2-eugin]

Try,

sql_search = "SELECT * FROM dbo_users " & _
             "WHERE job_title NOT LIKE '*N/A - ACCOUNT RENAMED*' AND " & _
             "owner_name LIKE " [COLOR=Red][B]& Chr(34) &[/B][/COLOR] "*" & search_text & "*" [COLOR=Red][B]& Chr(34) &[/B][/COLOR] _
             "ORDER BY surname asc, forename asc"
 
Forms![frm_main2]![subfrm_main2_search].Form.RecordSource = sql_search

 

[CJ_London]

Personally I use

owner_name like '*" & replace(search_text,"'","''") & "*' order by surname asc, forename asc"

 

[BonnieG]

Thank you both. That's done the trick!

 

[BonnieG]

I used CJ_London's suggestion and have this

sql_search = "SELECT * from dbo_users where job_title not like '*N/A - ACCOUNT RENAMED*' and owner_name like '*" & Replace(search_text, "'", "''") & "*' order by surname asc, forename asc"

Forms![frm_main2]![subfrm_main2_search].Form.RecordSource = sql_search

Although it works and allows apostrophes, it is now not allowing NULL values. Quite often I will search on nothing at all to return all values. What I can't work out is why changing to the above has caused that?

 

[CJ_London]

I presume you mean you have nulls in the jobtitle or onwner name fileds you are searching on

try wrapping them in the nz function such as

nz(jobtitle)

 

[BonnieG]

Sorry, what I meant is sometimes I search for nothing e.g. just hit enter on a blank search box and it used to return all values, until I changed the code to your suggestion, and now it gives me an error saying nulls are not allowed. Not sure why!

 

[CJ_London]

Oh, OK

in that case you need something a bit different

sql_search = "SELECT * from dbo_users where job_title not like '*N/A - ACCOUNT RENAMED*' and owner_name " & iif(isnull(search_text),"is null"," like '*" & Replace(search_text, "'", "''") & "*'")  order by surname asc, forename asc"

 

[BonnieG]

Oh, OK

in that case you need something a bit different

sql_search = "SELECT * from dbo_users where job_title not like '*N/A - ACCOUNT RENAMED*' and owner_name " & iif(isnull(search_text),"is null"," like '*" & Replace(search_text, "'", "''") & "*'")  order by surname asc, forename asc"

Thank you CJ_London. I've tried that but I'm getting compile errors saying "expected end of statement" after this bit: Replace(search_text, "'", "''") & "*'") but I can't see where there's a bracket missing... I've copied your code like for like. Can someone with a more finely turned eye for syntax note anything missing from the above?

 

[pr2-eugin]

Try this,

SQL_Search = "SELECT * FROM dbo_users WHERE job_title Not Like '*N/A - ACCOUNT RENAMED*' And owner_name " & _
             IIf(IsNull(search_text), " Is Null ", " Like '*" & Replace(search_text, "'", "''") & "*'") & _
             " ORDER BY surname asc, forename asc"

 

[BonnieG]

Just tried that too and still getting an "invalid use of null" error...

I've attached a screencap of my code.

I'm completely lost!

 

[KenHigg]

May I suggest a troubleshooting technique - Step through the code and see what the actual sql string looks like that you are trying to build. Then you will be able to get to the root of the issue -

 

[pr2-eugin]

In VBA IIf behaves a bit different. When run, irrespective of the condition both the True and False parts are executed prior to a decision being made. So if for example your search_text is Null, then it will try passing it to the Replace function and Replace being a String manipulation function does not like the concept of Null values, so screams on top of its head when it occurs, to trick it you can use Replace(search_text & "", "'", "''"). This should avoid the invalid use of Null.

 

[BonnieG]

Thank you... your suggestion eliminates the error Paul, though it means that when I search for nothing I get nothing, rather than everything. It's not such a big deal though and I'm happy to just not see the error anymore, and will encourage our users to use * instead of a null to return all values.

I appreciate the help. I'm not a programmer and agreed to take on this database which has grown legs... I'm not very good at spotting issues with it though I have got a grasp of some basic syntax. Thanks.

 

[pr2-eugin]

You're welcome ! If you tried my code in Post #2 are you still facing troubles? IMVHO, that should work as you intend. I am not 100% sure though.

 

[BonnieG]

Oh goodness me. It didn't work originally and threw up all sorts of problems, which is why I tried the second suggestion, and then tried to get that working. I've just tried yours again to remind myself of the errors I was getting, and it works as intended.

:banghead::banghead::banghead::banghead::banghead::banghead:

Sorry folks.