|
| |
|
|
xterradane
Posts: 143
From: Mobile, AL USA
Status: offline
 |
SQL query and ANY - 12/13/2001 12:39:29
I am trying to create a query (Acess db) that will do the following:
1. Say you have two drop down boxes, one is state the other job type.
2. On either of them the person can choose ANY/ALL rather than picking just one.
I know how to do a query if they just pick a single item, but I was thinking something of using If Thens for the different scenerios, can this be done? I tried it last night, but did not have any luck.. Don't have the code with me know, but if you want to look at it I can post it tongiht.
|
|
|
|
xterradane
Posts: 143
From: Mobile, AL USA
Status: offline
|
RE: SQL query and ANY - 12/13/2001 12:46:20
PS, How would I do it if I set it up that they could select multiple state or jobs with a limit of course to 3 or 4 selections.
Gail
| |
|
|
|
rdouglass
Posts: 9183
From: Biddeford, ME USA
Status: online
|
RE: SQL query and ANY - 12/13/2001 13:27:50
I'm not positive, but I believe you use the value of "%" as a wildcard in Access queries. Try setting the value of the "ANY/ALL" menu selection to "%" (without the quotes).
Edited by - rdouglass on 12/13/2001 13:42:12
| |
|
|
|
rdouglass
Posts: 9183
From: Biddeford, ME USA
Status: online
|
RE: SQL query and ANY - 12/13/2001 13:40:14
As to your second question, I'm not as good as others so I resort to the 'brute force' method. I build my queries into a variable (using If-Then's to create my query AND's) then call the variable when I'm ready for my query. Something like:
mySQL = ""
If Request("myField1") > "" then
mySQL = mySQL & "(myField1 = '::myField1::')"
end if
If Request("myField2") > "" then
'Check for previous criteria in mySQL
'If there is and need to add more criteria
'to mySQL, add " AND "
If mySQL > " " then
mySQL = mySQL & " AND "
End If
mySQL = mySQL & "(myField2 = '::myField2::')"
end if
etc.
Then use something like:
"SELECT * FROM Main WHERE (" & mySQL & ")"
for your query.
If no one has given you any better pointers, let me know and I'll show you an example of one I've done before.
| |
|
|
|
xterradane
Posts: 143
From: Mobile, AL USA
Status: offline
|
RE: SQL query and ANY - 12/13/2001 17:33:03
Here is the code I am using, it is returning either the wrong records or says there are no records to return if I have any chosen:
Dim strJobType, strLocation
strJobType = Request("JobType")
strState = Request("State")
Dim rsJob
If Request("JobType") <> "Any" Then
strSQL ="SELECT * FROM Job WHERE DateExpires>=Date() AND State='" & strState & "' ORDER BY DatePosted DESC;"
End If
If Request("strState") <> "Any" Then
strSQL= "SELECT * FROM Job WHERE DateExpires>=Date() AND JobType='" & strJobType & "' ORDER BY DatePosted DESC;"
Else
strSQL= "SELECT * FROM Job WHERE DateExpires>=Date() AND JobType='" & strJobType & "' AND State='" & strState & "' ORDER BY DatePosted DESC;"
End If
Set rsJob = Server.CreateObject("ADODB.Recordset")
rsJob.Open strSQL, objConn
| |
|
|
|
Spooky
Posts: 26599
Joined: 11/11/1998
From: Middle Earth
Status: offline
|
RE: SQL query and ANY - 12/13/2001 22:24:19
Try strSQL= "SELECT * FROM Job WHERE DateExpires>=#"&Date()&"# AND JobType....
Also, to check your string, do this :
End If
response.write strSQL
response.end
Set rsJob = Server.CreateObject("ADODB.Recordset")
§þððk¥
Database / DRW Q & A
VP-ASP Shopping cart
| |
|
|
|
davebukouricz
Posts: 300
From: Ma None
Status: offline
|
RE: SQL query and ANY - 12/13/2001 22:35:39
or
strSQL= "SELECT * FROM Job WHERE DateExpires>=Date() AND JobType LIKE '" & strJobType & "' ORDER BY DatePosted DESC;"
passing % as the variable from the dropdown "all" value
May just work
***********************
ASP Rocks!
Dave
| |
|
|
|
xterradane
Posts: 143
From: Mobile, AL USA
Status: offline
|
RE: SQL query and ANY - 12/14/2001 12:34:46
I am an idiot, I figured this out I needed to use an equals sign instead of <>. I just was half asleep when I was doing this one... Thanks for all your help and input...
Dim strJobType, strLocation
strJobType = Request("JobType")
strState = Request("State")
Dim rsJob
If Request("JobType") = "Any" Then
strSQL ="SELECT * FROM Job WHERE DateExpires>=Date() AND State='" & strState & "' ORDER BY DatePosted DESC;"
End If
If Request("strState") = "Any" Then
strSQL= "SELECT * FROM Job WHERE DateExpires>=Date() AND JobType='" & strJobType & "' ORDER BY DatePosted DESC;"
Else
strSQL= "SELECT * FROM Job WHERE DateExpires>=Date() AND JobType='" & strJobType & "' AND State='" & strState & "' ORDER BY DatePosted DESC;"
End If
Set rsJob = Server.CreateObject("ADODB.Recordset")
rsJob.Open strSQL, objConn
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
