Post reply

User!UserID syntax error in restricting query source

  • October 22, 2004 at 11:45 am

    #109155

    Hi!

    I am working on a student report for a school district and need to only display students for the staff person's school. I created a Report_Security table that maps Schools IDs with User IDs. In the Where clause of my query I have the following:

    WHERE Report_Security.UserID = User!UserID

    I get this syntax error  "Error in WHERE clause near '!'.

    Unable to parse query text." Does anyone know why that is, or how else I could accomplish this?

    Your help is greatly appreciated!

     

  • October 22, 2004 at 1:50 pm

    #527710

    To solve this either go to layout and create a parameter called user and set the default value to User!UserID and then create your query as

    WHERE Report_Security.UserID = @user

    or to use the USER!USERID global variable directly in the query you need create the query as dynamic sql e.g.

    ="SELECT * FROM TABLE " + "WHERE Report_Security.UserID = User!UserID"

    or something similar to the above (my memory for dynamic sql is a bit shady as i try to steer clear of it) but to the example above you must be in the genric query designer not the graphical one.

    hth dave

  • October 22, 2004 at 3:14 pm

    #527723

    David,

    Thanks a bunch. Your solution worked.

    Oksana

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply