Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Variable instead of Where clause

Status
Not open for further replies.

Gnana

Technical User
May 29, 2003
12
CA
Can someone please help me.

Can a WHERE clause be put into a variable. I need to change the WHERE clause based on a slection user makes on the website. For example, can I do something like this:

Dim Temp
Temp = [Clinical] = '1234'

[Clinical] here is a field name from a table.
 
I need to change the WHERE clause
of what ? a query ? a form ? a report ? a recordset ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
This is an Access table on an IIS Website. When someone chooses from a list, I have to change the Where clause. The sql statement I tried is:

Temp = "SELECT * FROM [Members for Web] WHERE [Last Name] like '" + _
StrQuoteReplace(LastName) + _
"' AND [Preferred Given] like '" + StrQuoteReplace(FirstName)+ _
"' AND [Postal Code] like '" + StrQuoteReplace(ZipCode) + _
"%' AND [Clinical] = '" & [Temp1] & "' "

This is working but I need to put [Clinical] also into a variable.
 
And where is the problem ?
I'm missing something ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Thank you for responding so quickly. I had to rush home early yesterday.

The problem I have is that for what I am doing, I need to change the WHERE clause depending on what the client chose from the list. If they chose "Clinical Psychology", I need the WHERE clause to be:

[Clinical] = '1234'

If someone chose, "Counselling Psychology", I need the WHERE clause to be:

[Counselling] = '1234'

[Clinical] and [Counselling] are two different fields in the table I am using. Hope I explained my problem.

 
How do you know what between Counselling and Clinical is choosen ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Here is the code to find the client choice:

AreaList = Request.QueryString("lstArea")
select Case AreaList
Case ClinicalPsychology
Temp1 = "Clinical Psychology"
Case CounsellingPsychology
Temp1 = "Counselling Psychology"
Case else
Temp1 = "Health Psychology"
end select
 
Something like this ?
Select Case AreaList
Case ClinicalPsychology
Temp1 = "[Clinical]='" & [Clinical Psychology] & "'"
Case CounsellingPsychology
Temp1 = "[Counselling]='" & [Counselling Psychology] & "'"
Case Else
Temp1 = "[Health]='" & [Health Psychology] & "'"
End Select
and then:
Temp = "SELECT * FROM [Members for Web] WHERE [Last Name] Like '" & _
StrQuoteReplace(LastName) & _
"' AND [Preferred Given] Like '" & StrQuoteReplace(FirstName) & _
"' AND [Postal Code] Like '" & StrQuoteReplace(ZipCode) & _
"%' AND " & Temp1 & " "

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
The following line gives me an error (missing operator)
Temp1 = "[Clinical]='" & [Clinical Psychology] & "'"

**************************

THE FOLLOWING CODE IS WORKING BUT DOES NOT PULL ANY RECORDS

Temp1 = '"Clinical Psychology"
Temp2 = "[Clinical]"

The query will be:

Temp = "SELECT * FROM [Members for Web] WHERE [Last Name] like '" + _
StrQuoteReplace(LastName) + _
"' AND [Preferred Given] like '" + StrQuoteReplace(FirstName)+ _
"' AND [Postal Code] like '" + StrQuoteReplace(ZipCode) + _
"%' AND" & [Temp2] & "' " = '" & [Temp1] & "' "

*****************
THE FOLLOWING CODE IS WORKING BUT PULLS ALL THE RECORDS WITHOUT FILTERING FOR "Clinical Psychology"

Temp1 = "[Clinical]="+"ClinicalPsychology]"

The query will be:

Temp = "SELECT * FROM [Members for Web] WHERE [Last Name] like '" + _
StrQuoteReplace(LastName) + _
"' AND [Preferred Given] like '" + StrQuoteReplace(FirstName)+ _
"' AND [Postal Code] like '" + StrQuoteReplace(ZipCode) + _
"%' AND '" & [Temp1] & "' "
 
So, what are the table field names and the script variables or controls ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
If I hardcode the field name [Clinical] into the query, it works fine. Please see code:

THIS CODE WORKS FINE - BUT FIELD NAME HARDCODED

Temp = "SELECT * FROM [Members for Web] WHERE [Last Name] like '" + _
StrQuoteReplace(LastName) + _
"' AND [Preferred Given] like '" + StrQuoteReplace(FirstName)+ _
"' AND [Postal Code] like '" + StrQuoteReplace(ZipCode) + _
"%' AND [Clinical] = '" & [Temp1] & "' "

****************
[Clinical] is a field name in [Members for Web] table. As you see above, my problem is to put a field name into a variable.
 
And what about something like this ?
Temp2 = "[Clinical] = '" & [Temp1] & "' "
...
Temp = "SELECT * FROM [Members for Web] WHERE [Last Name] like '" & _
StrQuoteReplace(LastName) & _
"' AND [Preferred Given] like '" & StrQuoteReplace(FirstName) & _
"' AND [Postal Code] like '" & StrQuoteReplace(ZipCode) & _
"%' AND " & Temp2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
This worked. Thank you so much. I had been struggling with this for two days - trying so many different syntax possibilities.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top