Run-time error ‘3075’

[HowardHelp]

My database is based and dissected from the old Northwind database which works well enough for me BUT the database does not support searching for inverted commas e.g. its is fine but search it’s and it produces a pop up error message as follows



Run-time error ‘3075’
Syntax error (missing operation) in query expression ‘[Title] like ‘it’s*”.



If I click on debug it highlights this code

Me![Find Sub form].Form.RecordSource = MyRecordSource



The full code below with the highlighted code in it.



Private Sub Show_Tracks_Button_Click()



' Create a WHERE clause using search criteria entered by user and

' set RecordSource property of Find Sounds Subform.



Dim MySQL As String, Mycriteria As String, MyRecordSource As String

Dim ArgCount As Integer

Dim Tmp As Variant



' Initialize argument count.

ArgCount = 0



' Initialize SELECT statement.

MySQL = "SELECT * FROM Title WHERE "

Mycriteria = ""



' Use values entered in text boxes in form header to create criteria for WHERE clause.

AddToWhere [Look For Title], "[Title]", Mycriteria, ArgCount

AddToWhere [Look For Album], "[Album]", Mycriteria, ArgCount

AddToWhere [Look For Artist], "[Artist]", Mycriteria, ArgCount

AddToWhere [Look For Year], "[Year]", Mycriteria, ArgCount

AddToWhere [Look For Category], "[Category]", Mycriteria, ArgCount

AddToWhere [Look For Notes], "[Notes]", Mycriteria, ArgCount

AddToWhere [Look For Copy 1], "[Copy 1]", Mycriteria, ArgCount

AddToWhere [Look For Copy 2], "[Copy 2]", Mycriteria, ArgCount

AddToWhere [Look For Rating], "[Rating]", Mycriteria, ArgCount



' If no criterion specifed, return all records.

If Mycriteria = "" Then

Mycriteria = "True"

End If



' Create SELECT statement.

MyRecordSource = MySQL & Mycriteria



' Set RecordSource property of Find Sub form.

Me![Find Sub form].Form.RecordSource = MyRecordSource (highlighted code when error occurs)


' If no records match criteria, display message.

' Move focus to Clear button.

If Me![Find Sub form].Form.RecordsetClone.RecordCount = 0 Then

MsgBox "No records match the criteria you entered.", 48, "No Records Found"

Me![Clear Button].SetFocus

Else

' Enable control in detail section.

Tmp = EnableControls("Detail", True)

' Move insertion point to Find Sounds Subform.

Me![Find Sub form].SetFocus

End If



End Sub



The question can this be fixed, search inverted comers in the database TABLE direct are fine, any help will be much appreciated.

 

[MajP]

Use the CSQL function

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

You cannot search for an apostrophe in a word
like "*it's*" will fail
these need to be replaced by double ''

 

[HowardHelp]

I thing I forgot to mention if you use a double inverted comma like it’’s works fine

 

[MajP]

I said that already. Read the thread. Use the code.

 

[Isaac]

every programming language has ways to escape syntax symbols in literal strings. in sql, it's to double-up. it means change one ' to two ''

 

[HowardHelp]

Well I must admit I am totally stuck I assume this is the bit of code I need

Select Case Sql_Type

Case sdt_text

Sql = Replace(Trim(Value), "'", "''")

If Sql = "" Then

Sql = SqlNull

Else

Sql = " '" & Sql & "'"

End If

But exactly where to put it in my SQL I'm afraid I have no know idea, sorry.

 

[Isaac]

Honestly it sounds like you are in a bit over your head and using a lot of code you don't understand. I don't say that to be rude, just to point out that I think it seems like maybe you have put yourself in a place you should never be--using too much code you don't understand. Let your combination of learning + applying be fully incremental ....that will put you in the best possible place, experience and capability wise. That's just some personal advice.

As to your immediate problem, what's AddToWhere ?

 

[HowardHelp]

Well I did hope it was an easy fix for someone who understood code, obviously I don’t but can struggle through sometimes, I can fault find to component level on transmitters radio’s etc. but not code.

I joined the group because it welcomed beginners, but not to be rude either but it obviously is more for intermediate and above, so sorry to of wasted you're time.

Best wishes

 

[Isaac]

Sometimes we have to hear hard things sometimes. When I was earlier in my Access & SQL skill levels, a seasoned Systems guy once told me I was a danger to the company because I'd build thing beyond my understanding and capacity to maintain. I didn't enjoy him saying that, but it did get me thinking about what level of complexity was appropriate for me to deploy, and when, versus my learning level at that time.

We are very welcoming to beginners. But letting someone know that they're trying to use and deploy something far beyond their current learning level is a legit warning, and I wouldn't be the first to articulate it.

Anyway - I was willing to continue with the troubleshooting process, & asked a follow up question. But it is up to you.

 

[WayneRyan]

Hi Howard,
On an iPad so very hard to type

Change to --> = replace(myrecordsource, "'", "''")

Relax, no ones demeaning you, welcome.


Hth,
Wayne

 

[WayneRyan]

Howard,

I have a real computer now

You have a code like this:

AddToWhere [Look For Title], "[Title]", Mycriteria, ArgCount

The AddToWhere routine probable has a line something like this:  (you didn't attach it)

mycriteria = micriteria & Column & " Like '" & SearchValue & "*'"   Note: Column/SearchValue are passed to AddToWhere

For your "troubled" call: Column =[Title]      and       your SearchValue = it’s

This produces --> [Title] like ‘it’s*'  <-- The highlighted appostrophe is your problem; SQL thinks its the end of the string.

Your line of code that builds the SQL text should use replace:

mycriteria = micriteria & Column & " Like '" & Replace(SearchValue, "'", "''") & "*'"

This produces --> [Title] like ‘it’’s*'  -- This looks weird to us, but when SQL sees the DOUBLE apostrophe; it knows to use just ONE.

Try it and either use debug.print and print out your SQL and look at (look at as the SQL parser would).

hth,
Wayne

 

[gemma-the-husky]

I am not sure if this will work
Instead of
Mycriteria = "" (which is an empty string, and not a speech mark)

try this
Mycriteria = chr(34)
single quote is chr(39)

 

[Gasman]

Well I did hope it was an easy fix for someone who understood code, obviously I don’t but can struggle through sometimes, I can fault find to component level on transmitters radio’s etc. but not code.

I joined the group because it welcomed beginners, but not to be rude either but it obviously is more for intermediate and above, so sorry to of wasted you're time.

Best wishes

So if I joined a forum for electrical components and was struggling to understand the logic of a diode when inline with a capacitor and not getting the correct impedance, you would not tell me I am going about it in the wrong way?

In your situation I would try a Replace() in the AfterUpdate event or Lost Focus of your search control.
You would need to check that you had not already updated the control, or that the user had entered two ' knowing that they were needed?
Up to you whether you do the Replace() on the control, so that the user will see it or on a variable populated from that control.

 

[HowardHelp]

I am not sure if this will work
Instead of
Mycriteria = "" (which is an empty string, and not a speech mark)

try this
Mycriteria = chr(34)
single quote is chr(39)

Many thanks i did try this but it gave me a debug for any entry or an empty entry which normally brings up all records, it tried both 34 and 39, much appreciated for your help it was something to try

 

[HowardHelp]

So if I joined a forum for electrical components and was struggling to understand the logic of a diode when inline with a capacitor and not getting the correct impedance, you would not tell me I am going about it in the wrong way?

In your situation I would try a Replace() in the AfterUpdate event or Lost Focus of your search control.
You would need to check that you had not already updated the control, or that the user had entered two ' knowing that they were needed?
Up to you whether you do the Replace() on the control, so that the user will see it or on a variable populated from that control.

 

[The_Doc_Man]

@HowardHelp - sometimes things get overwhelming when you are new to a situation. Don't give up.

From the original message, it would appear that you have a data-sensitive error that in most cases would NOT have triggered an error response. You need to allow Access to stop you at a given line after an error. DON'T put an error trap in the code AND don't disable notifications. You WANT Access to stop you and show you which line is in error. SO... set no error traps. This is for debugging only, but you WANT the code to fail. You know the old Elmer Fudd rule about hassenpfeffer, right? First you have to CATCH the rabbit. And that is why you WANT an error trap triggered.

When you get to that point, there is a simple command to let you examine things. From the code window (that opened up to show you the error) you can open the IMMEDIATE window (View>>Immediate Windows from the Menu Bar). Now in that little split-screen area, you can type this:

DEBUG.PRINT MyCriteria (or you could also look at MySQL).

You will see the value of any defined variable that way, the only catch being that the variable has to be "in scope" to the routine where the code stopped. If the variable you named could not have been used in code at that point, you cannot Debug.Print it. But if it was visible from there, you can check its value. That will let you actually SEE the SQL or criteria expression you built, which might help you figure out the error.

 

[HowardHelp]

OK thanks again to gemma-the-husky for getting me started on this road and sorry for the delay but I have been trying quite a few experiments and the code works with inverted comma like it’s

Mycriteria = (Mycriteria & FieldName & " Like " & Chr(34) & FieldValue & Chr(42) & Chr(34))

BUT does anyone know how to include Chr 39 as well which effects a speech mark not two separate inverted commas as if I search “"5.7.0.5." I get the runtime error again unless I change the two 34’s to 39’s but then I lose my inverted comma again

 

[gemma-the-husky]

I think it's tricky when you can have both characters in string fields.

You have a name like O'Reilly
an address like "The Maltings", Back Lane, Christchurch

and if you try to produce a csv, for instance with text separators, you can struggle. I think the best idea is to prevent the "" chr(34) being used at all in a text field.

Note that commas within text fields can also give you a problem when you try to produce a csv. Some users use a different field delimiter to avoid this.

 

[HowardHelp]

I think it's tricky when you can have both characters in string fields.

You have a name like O'Reilly
an address like "The Maltings", Back Lane, Christchurch

and if you try to produce a csv, for instance with text separators, you can struggle. I think the best idea is to prevent the "" chr(34) being used at all in a text field.

Note that commas within text fields can also give you a problem when you try to produce a csv. Some users use a different field delimiter to avoid this.

Many thanks I'm pleased with what I have now the inverted comma is the most important to me so if there isn’t any way round the speech marks “ I will leave well alone I tried many scenarios but all failed in different ways.

So many thanks for your help it was very much appreciated.