SQL with function mid ?

[patweb]

SQL syntaxs error :

The value for CodeName is "abb"
In the field [PAG] the value is "ggg_suppl_abb_"
If the part in this text on position eleven for 3 positions is equal to the value of Codename take the ID of this record.

Is it possible to use the mid function in the SQL string, if yes what is wrong, if not, how to ?


SQL = "SELECT [T-LOOKUP-PAG].[PAG-ID], [T-LOOKUP-PAG].[PAG] " _
& "FROM [T-LOOKUP-PAG] " _
& "WHERE ((([T-LOOKUP-PAG].mid([PAG],11,3)= CodeName); "

many txs in advance, pat

 

[KarMac21]

Hi Pat,

You just have your mid in the wrong place. Try this instead:

SQL = "SELECT [T-LOOKUP-PAG].[PAG-ID], [T-LOOKUP-PAG].[PAG] " _
& "FROM [T-LOOKUP-PAG] " _
& "WHERE [COLOR=#ff0000]Mid([T-LOOKUP-PAG].[PAG],11,3)[/color] =  CodeName;"

Shane

 

[lupins46]

& " WHERE mid([PAG],11,3)='" & CodeName & "'; "

 

[cLFlaVA]

Also, if your variable "CodeName" is set within VBA, you'll have to do something like the following:

SQL = "SELECT [T-LOOKUP-PAG].[PAG-ID], [T-LOOKUP-PAG].[PAG] " _
& "FROM [T-LOOKUP-PAG] " _
& "WHERE Mid([T-LOOKUP-PAG].[PAG],11,3) =  '" & CodeName & "';"

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[KarMac21]

Also if CODENAME is a variable you will want to code it like this:

SQL = "SELECT [T-LOOKUP-PAG].[PAG-ID], [T-LOOKUP-PAG].[PAG] " _
& "FROM [T-LOOKUP-PAG] " _
& "WHERE [COLOR=#ff0000]Mid([T-LOOKUP-PAG].[PAG],11,3) = '" & CodeName & "';"[/color]

 

[marina9]

Hi Pat!

You may want to try this:

SQL = "SELECT [T-LOOKUP-PAG].[PAG-ID], [T-LOOKUP-PAG].[PAG] " _
& "FROM [T-LOOKUP-PAG] " _
& "WHERE SUBSTRING([T-LOOKUP-PAG].[PAG], 11, 3) = CodeName); "

Hope this helps!

 

[marina9]

Sorry, I was thinking in SQL Server terms. Need more coffee...

 

[patweb]

Many txs to all, codename was indeed also a variable and so syntaxis need also be adapted.

& "WHERE Mid([T-LOOKUP-PAG].[PAG],11,3) = '" & CodeName & "';"