Left Function not working - syntax problem? 2

[billschu]

Greetings,

I am using Access 2000 and have been using the expression builder to create a filter on my serial number field, as follows:

Left ([SER_CODE] , 4) = "0103"

It gives me an error message: "the expression contains invalid syntax", etc.., or it says "the expression you entered contains an invalid . (dot) or ! operator or invalid parenthesis". I've tried it every which way:

Left ([FSREPORT]![SER_CODE] , 4) = "0103"
Left ([FSREPORT]![SER_CODE] . 4) = "0103"

Nothing seems to work. Any ideas?

Thanks, Bill

 

[ghubbell]

Hi Bill,
Please try:

Left(Me.SER_CODE,4) = "0103"

I'm assuming you're doing this on one form...

Then in any module: Tools-References: Do you have Microsoft DAO 3.6 Object library checked? If not, please do. See if the Db will compile. No? Back again and move 3.6 up a notch or two till it will.

might go. Gord
ghubbell@total.net

 

[billschu]

Thanks for you help Ghubbell:

I tried both the "me.ser_code" syntax as well as adding DAO 3.6 object library and above, and I'm still having the same problem.

Please note that I'm ctrying to create a new query, in design view, and in the expression builder for the "Field:" textbox - not using code in a module. I can use the Left function just fine in code, just not in the expression builder. I tried it with both Access 97 and Access 2000. Its weird because the expression builder will add:

Left («stringexpr». «n»)

using the &quot;.&quot; instead of a &quot;,&quot; as you would normally. I try it with both though, neither work. For the <<stringexpr>>, I also tried it all kinds of different ways:

[Table1]![Ser_Code]
[Table1].[Ser_Code]
Table1.Ser_Code
([Table1].[Ser_Code])

again, nothing seems to work. This is a problem also with the mid and right funtions in the expression builder.

Thanks again, Bill

 

[ghubbell]

Hi Bill,
Somewhere in your computer you might~should have the sample Db &quot;Northwinds&quot;. See if you can find it and if you do, open a new empty query in it, select no table, and switch to &quot;SQL&quot; view (top left). Paste in this:

SELECT Orders.ShipCity, Left([ShipCity],4) AS SCLeftFour, Orders.OrderID, Left([OrderID],2) AS OLeftTwo
FROM Orders;

I didn't use the builder to do this...just typed it in, let Access put the square brackets around the field, and away it went. Take a look at it in datasheet view and let's know if this is ...sucessful and, if it is sort of what you're doing in yours.
Gord
ghubbell@total.net

 

[billschu]

Ok, it works now - if I just stick to the &quot;SQL View&quot; and type it in manually, vs. using the expression builder in the &quot;Design View&quot;.

This is most likely an Access bug though - trying to use the left, mid, or right functions in the expression builder just doesn't work.

Thanks for your help!

Bill

 

[ghubbell]

Gremlins Bill! Full of Gremlins! ;-) Anytime...you're welcome! Gord
ghubbell@total.net