User!UserID syntax error in restricting query source

• Oksana Petrichenko

  SSC Rookie

  Points: 49

  More actions

  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!

   

• David Scotland-132255

  SSCertifiable

  Points: 5599

  More actions

  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

• Oksana Petrichenko

  SSC Rookie

  Points: 49

  More actions

  October 22, 2004 at 3:14 pm

  #527723

  David,

  Thanks a bunch. Your solution worked.

  Oksana