Syntax error....

[irisbu]

I have this code:

Set rs = db.OpenRecordset("SELECT * FROM expressions order by heb WHERE '" & TextBox2.Text & "' = InStr(![heb], TextBox1.Text)")

Can someone show me where the syntax error is and what is the correct way to write this line?

I am desperate!!! :-(

[Stewart Ross]

This post appears to be a duplicate of post 25 of this thread, a thread which is now closed to your satisfaction. Please do not double-post your questions as this wastes the time of the experts involved.

You also provide no clue as to what error you are getting or the context of whatever 'this code' is.

A revised version of your code which may have more of a chance of working is shown below.

Set rs = db.OpenRecordset("SELECT * FROM expressions WHERE [" & TextBox2.Text & "] = " & InStr(![heb], TextBox1.Text) & " ORDER BY heb;")

I'd be surprised if just getting the syntax right means it will work straight away, however, as it may be OK for syntax and still not mean anything to Access...

-Stewart

[irisbu]

Prepare to be surprised

The code is now working beautifully.... I hope I won't have to eat my hat later on... but it's working for now.

Thank you for your reply.

Iris

[NeoPa]

= InStr(![heb], ...

is likely to suffer too, as you have an orphaned bang (!) within your SQL string. This syntax is only valid within a With clause of VBA. Not in SQL.

[irisbu]

I have changed the

![heb] - to -
instr(heb, '" & TextBox1.Text & "')

so it's OK.

Thank you for your kindness and for your caring.

Have a great day!

Iris

[NeoPa]

No worries,

and Welcome to Bytes!