Variable instead of Where clause

[Gnana]

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.

 

[PHV]

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

 

[Gnana]

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.

 

[PHV]

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

 

[Gnana]

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.

 

[PHV]

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

 

[Gnana]

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

 

[PHV]

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

 

[Gnana]

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] & "' "

 

[PHV]

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

 

[Gnana]

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.

 

[PHV]

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

 

[Gnana]

This worked. Thank you so much. I had been struggling with this for two days - trying so many different syntax possibilities.