Linking Parameter to Where Clause 1

[MaumeeScott]

I would like to take a parameter input and use it to select records, but the parameter will reference a data set based on a different criteria. Essentially the user will type in their location and the report will then use this to determine which records to return. I need to build an if/then statment using the parameter like the following:

if location = 'CA' then select from account_list where gl_acct like "66*"

I have tried overrideing the ObtainSelectStatment with the following but the report is still returning all the records not just the ones that begin with "66*"

Function ObtainSelectStatement( ) As String
If ("Location"="CA") Then
WhereClause = WhereClause & "glacct like" & "66" & "*"
End if

'Comment out this line for SQLServer reports
'FromClause = parseSchema(FromClause)

ObtainSelectStatement = Super::ObtainSelectStatement( )
' Insert your code here
End Function

Thanks in advance for any and all suggestions.

Scott

 

[johnberchmans]

If Location is the name of the parameter then the statement
If ("Location"="CA") Then
should be
If (Location="CA") Then

without the double quotes surrounding Location.

I hope this helps.

-- JB

 

[MaumeeScott]

John,
For some reason when I remove the quotes around the parameter I get a database error.

Thanks for the help.

Scott

 

[johnberchmans]

Sorry I did not locate it earlier.

When the statement
WhereClause = WhereClause & "glacct like" & "66" & "*"
is processed, the line will look like
glacct like66*

I think it should look like
and glacct like '66*'

i.e. 'and', some blank spaces and single quotes are missing.

-- JB

 

[MaumeeScott]

That did the trick! Thanks a million!

Scott