none
Problem in built in function of MS Access 2013 RRS feed

  • Question

  • I had worked with MS Access in 2002, now I come back to work with MS Access 2013. However, I have a problem with very simple instruction in built in function. I am using left() function in one column of query, but it does not work. I have tried:

    y: left( "abc", 1 )

    and

    y: left( "abc". 1 )

    and

    y: left( "abc"; 1 )

    Those three above always have the same responses:

    "The expression you entered contains invalid syntax" "You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it  in quotation marks"

    Anybody please to help my problem .....

    Regards

    ASasongko

    Wednesday, July 8, 2015 4:45 AM

Answers

  • Hi Ta Cen,

    As far as I test, the Access support using Left function in an query. I created a table and here is the SQL for the test:

    SELECT Left(Field1,1) AS Expr1, Left('abc',1) AS Expr2, *
    FROM table1;
    

    Would you mind sharing with the detail step to help us to narrow this issue?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 9, 2015 7:45 AM
    Moderator
  • When you change the region, there is an item called "list separator" which also may change (click on the button in the lower right of the region dialog to see this).

    You must use the List Separator in the query. Often this is a semicolon ";". Thus you might try

    y:  Left("abc"; 1)

    in the query.

    Matthias Kläy, Kläy Computing AG

    Thursday, July 9, 2015 11:46 PM

All replies

  • The first example is the correct answer.

    y: Left("abc",1)

    To test any function, you can open the VBA editor and type the function in the immediate window preceded by ?.  With the cursor at the end of function, hitting [Return] will execute the procedure and the return result will be printed on the following line.  You can do the same with sub routines, just omit the ?.

    As for your error, you might go to the SQL editor and attempt to run your query.  The error is not in your Left expression.  The SQL editor may point you to the offending expression.

    Wednesday, July 8, 2015 5:06 AM
  • Yes, I have tried in immediate window, it works :-) .... But it still does not work in query's built-in function... :-(
    Wednesday, July 8, 2015 8:34 AM
  • Furthermore, when I open the "SQL View", the response is: "The expression you entered contains invalid syntax, " so I can not observe what is wrong inside ....
    Wednesday, July 8, 2015 8:37 AM
  • Oh ya .... I must tell you the cause, may be here is the cause....  At first I change the home location of region setting in control panel into my country, Indonesia, since I have .csv data with comma. It should affect the numeric format, but as I remember, the query's built in function starts to have problem. However, the change of home location should not affect the separation of parameters in function.
    Wednesday, July 8, 2015 8:45 AM
  • I do a test. I make the above simple function into VBA as follow

    Option Compare Database
    Option Explicit
    Function shortname(secid As String) As String
        shortname = Left(secid, 1)
    End Function

    And I call the function from the query:

    x: shortname([Table1]![Field1])

    It works!

    But this MS Access have a serious potential problem that, it can not accept multi parameter function as above. Because it rejects any parameter separator, either comma or dot or semicolon, etc.

    I think the problem is there is a bug in MS Access 2013.

    Wednesday, July 8, 2015 9:03 AM
  • Hi Ta Cen,

    As far as I test, the Access support using Left function in an query. I created a table and here is the SQL for the test:

    SELECT Left(Field1,1) AS Expr1, Left('abc',1) AS Expr2, *
    FROM table1;
    

    Would you mind sharing with the detail step to help us to narrow this issue?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 9, 2015 7:45 AM
    Moderator
  • When you change the region, there is an item called "list separator" which also may change (click on the button in the lower right of the region dialog to see this).

    You must use the List Separator in the query. Often this is a semicolon ";". Thus you might try

    y:  Left("abc"; 1)

    in the query.

    Matthias Kläy, Kläy Computing AG

    Thursday, July 9, 2015 11:46 PM