How to pass group key value to a SQL statement

[jrcai]

Anyone knows how to pass getGroupKey() value to a SQL statement under the group. I tried this in Function ObtainSelectStatement() As String:

ObtainSelectStatement = "SELECT order from orders where order_date = '" & getGroupKey() & "'"

where getGroupKey() is date type. I got illegal use of variable error.

Any one can help me? Thanks!!!

 

[WAndrewW]

I am not sure what you are trying to do. If I want to add a where clause to the Query I generally add a parameter of the appropriate datatype in you case probably string or date. Then when prompted for the parameter value I put in the condition I want met. So I am assuming that the ultimate query you want generated looks somehting like SELECT order from orders where order_date = '2002-08-16'. I also think with your approach there is a scoping issue, as well as getGroupKey() will probably not return a specific date so the query will be incorrect.

 

[jrcai]

Andrew, using parameter is not a solution. There is a master SQL statement that drives a number of grouped reports, say reports by months, such as Jan, Feb and so on. So month here is the group key. Within one month group I added a section with its own data source. The "where" clause for this data source is associated with the group key information. For example the where clause should be like: "where orderDate ='" & groupKey & "'". The groupKey is a variable that I need to pass the group key value to it. In text control, I can use getGroupKey() function to get the key value, but I don't know how to get the value in my SQl statement.

Thanks for your help.

Jeff