Inserting a variable data source into SELECT Statement

[ram_rtg]

Hi all,

I have a ListBox that uses two different queries based on a text box value. This is for security purposes. One query shows all records and the other query hides some records when there is a certain flag.
When the user accesses the form the text box is given a value and the appropriate query populates the ListBox based on the access level in the textbox.

What I want to accomplish is use a SELECT statement to use a string variable within a string for those two queries.

e.g.
Dim strOrd as String
Dim strQuery as String
strSql = "SELECT * FROM strQuery ORDER BY "

Then use the full string with the appropriate query further down in the code
strSql = strSql & "ID " & strOrd

Is this possible? If yes what would be the correct syntax?

Thank you in advance

 

[James Deckert]

Trying to figure out what you mean. Are you asking how to reference the textbox in your query?
strSql="select..." & me.txtbox & "more sql"
or maybe you mean
strSql="select..." & stringVar & "more sql"
I hope one of these is correct, if not try reexplaining and give an example.

 

[ram_rtg]

Trying to figure out what you mean. Are you asking how to reference the textbox in your query?
strSql="select..." & me.txtbox & "more sql"
or maybe you mean
strSql="select..." & stringVar & "more sql"
I hope one of these is correct, if not try reexplaining and give an example.

The textbox is merely to identify what query to use. Here's all of the code:

Dim strOrd As String, strSql As String, strQuery As String

If Me.Text1.Value = "Boss" Or Me.Text1.Value = "Admin" Then
strQuery = qrySearch
Else
strQuery = qryRestrictedSearch
End If

Once the query is determined, the following sorting code can take place:

If Button = 1 Then
If Y <= 225 Then
If Right$(ListBox.RowSource, 5) = " Asc;" Then
strOrd = " Desc;"
Else
strOrd = " Asc;"
End If
strSql = "SELECT * FROM strQuery ORDER BY "
Select Case X
'Determine point region
Case 0 To 675 'ID Heading
strSql = strSql & "ID " & strOrd
Case 676 To 2625 'date heading
strSql = strSql & "[date] " & strOrd
Case Else ' time heading
strSql = strSql & "[time] " & strOrd
End Select
ListBox.RowSource = strSql
ListBox.Requery

I was trying to implement the code found here and I got it working well if there is only one RowSource (from a query):
http://www.access-programmers.co.uk/forums/showthread.php?t=109525

But I can't figure out how to incorporate multiple RowSources (from querys) based on a textboxes value.

 

[Minty]

The code should be

End If
strSql = "SELECT * FROM [COLOR="Red"]" & strQuery & "[/COLOR] ORDER BY "
Select Case X

You should avoid using date and time as field names as they are reserved words and will cause you no end of grief going forwards.

Also if your [Date] field is a date you will need to format it in american format (mm/dd/yyyy) and ensure it is surrounded by #08/28/2015# in the SQL expression. I suspect you'll also have similar issues with the [time] field.

 

[ram_rtg]

The code should be

End If
strSql = "SELECT * FROM [COLOR="Red"]" & strQuery & "[/COLOR] ORDER BY "
Select Case X

You should avoid using date and time as field names as they are reserved words and will cause you no end of grief going forwards.

Also if your [Date] field is a date you will need to format it in american format (mm/dd/yyyy) and ensure it is surrounded by #08/28/2015# in the SQL expression. I suspect you'll also have similar issues with the [time] field.

The code I posted above was just as an example but thanks for the info on the date and time. I will remember that they are reserved words

James code above gave me the idea of using the " & stringVar & " but it didn't work. Your post reassured me that it was the right direction to head in so I tried it again but still got blanks in my ListBox.

Then I tried adding quotation marks to the strQuery string and PRESTO! it worked:

If Me.Text1.Value = "Boss" Or Me.Text1.Value = "Admin" Then
strQuery = "qrySearch"
Else
strQuery = "qryRestrictedSearch"
End If

A BIG thank you to all

 

[Minty]

Ah - I missed that - you should make sure you have Option Explicit at the top of your code modules, that would have highlighted that it thought those were variables, not strings...

 

[ram_rtg]

Will do. Thank you.