Adding criteria to RowSource

[SirDrums]

I have a listbox thats row source is set via dlookup. its copying SQL string from another table.

lstResults.RowSource = DLookup("[SQL_STRING]", "RebisView", "View_Name =" & Chr(34) & selview & Chr(34))

I need to add a creiteria to this row source. Problem is I cannot add it to the original SQL string as it is used in other parts of our application and can't be modified.

The criteria I would like to add is stored in a variable.


How can I add this criteria to the query after the query is set into the rowsource of the listbox?

Thanks!

 

[JANR]

just add an extra string to the expression:

lstResults.RowSource = DLookup("[SQL_STRING]", "RebisView", "View_Name =" & Chr(34) & selview & Chr(34)) & " Where ....."

Note the space before Where.

JR

edit:
If you have a ending semicolon you need to remove it from the string.

Dim x as String
x = dlookup(...)
x = Left(x, len(x)-1)
lstResults.Rowsource = x & " Where..."

 

[SirDrums]

just add an extra string to the expression:

lstResults.RowSource = DLookup("[SQL_STRING]", "RebisView", "View_Name =" & Chr(34) & selview & Chr(34)) & " Where ....."

Note the space before Where.

JR

edit:
If you have a ending semicolon you need to remove it from the string.

Dim x as String
x = dlookup(...)
x = Left(x, len(x)-1)
lstResults.Rowsource = x & " Where..."

Thanks for the reply,

Actually the code I pasted was just to retrieve the SQL that defines the query. It is placed in a listboxes row source.

I am wanting to set criteria for the retrived query after the row souce was set.

For eaxmple:

The code retrieved:

Select tblNames.FirstName

It places this slq string in the rowsource for the listbox.

Now I want to set criteria for that query.

 

[jdraw]

Please describe exactly what you want in the rowsource of the listbox.

It isn't clear, to me, what exactly you want,

 

[SirDrums]

In the simplest form.

The code retrieves:

Select tblNames.FirstName FROM tblNames

I need to places this into the rowsource as

Select tblNames.FirstName FROM tblNames WHERE FirstName = "Bob"


My SQL is much more complex than that but that is basically what I want to do. I was hoping I didnt actually have to edit the SQL string it self but could mabye set the creteria some how after it is set in the row source.... maybe by simple adding [FirstName] = "Bob" some where in VBA or in a control.

I hope I am not being confusing (I think I may be)

 

[VilaRestal]

Yes it should be straightforward (if I understand correctly, and it took a few read-throughs to realise what you were trying to do so I may still be misunderstanding):

I think you need something like this:

Dim strSQL as String
strSQL = lstResults.RowSource
If InStr(strSQL, "where") Then [COLOR=black]'Check whether there is already a where clause[/COLOR]
     strSQL = strSQL & " AND FirstName = 'Bob'"
Else
     strSQL = strSQL & " WHERE FirstName = 'Bob'"
End If
lstResults.RowSource = strSQL

Is that what you wanted to achieve?