February 10th, 2005, 07:28 AM
|
|
Contributing User
|
|
Join Date: Feb 2005
Posts: 56
Time spent in forums: 19 h 50 m 7 sec
Reputation Power: 4
|
|
|
Help with SQL statement..
I am using this SQL statement to get results form my access database..
Code:
Function BuildSQLString(strSQL As String) As Boolean
Dim strSELECT As String, strFROM As String, strWHERE As String, strCUSTOMER As String
strSELECT = "*"
strFROM = "[mainstatic]"
strWHERE = ""
If Not IsNull(Shopnumbertxt) Then strWHERE = strWHERE & " And [mainstatic]![Job number] = [Forms]![view job]![shopnumbertxt]"
If Not IsNull(jobstatusfilter) Then strWHERE = strWHERE & " And [mainstatic]![Job status] = [Forms]![view job]![jobstatusfilter]"
If Not IsNull(priorityfilter) Then strWHERE = strWHERE & " And [mainstatic]![priority] = [Forms]![view job]![priorityfilter]"
If Not IsNull(datestarttxt) Then strWHERE = strWHERE & " And [mainstatic]![Date Issued] >= [Forms]![view job]![datestarttxt]"
If Not IsNull(enddatetxt) Then strWHERE = strWHERE & " And [mainstatic]![Date Issued] <= [Forms]![view job]![enddatetxt]"
strCUSTOMER = ""
If aramiskacheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "aramiska" & Chr(34) & " Or [mainstatic]![Customer] ="
If bespokecheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "bespoke" & Chr(34) & " Or [mainstatic]![Customer] ="
If btcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "bt" & Chr(34) & " Or [mainstatic]![Customer] ="
If coralcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "coral" & Chr(34) & " Or [mainstatic]![Customer] ="
If kingstoncheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "Kingston" & Chr(34) & " Or [mainstatic]![Customer] ="
If mducheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "mdu" & Chr(34) & " Or [mainstatic]![Customer] ="
If pipexcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "pipex" & Chr(34) & " Or [mainstatic]![Customer] ="
If patientlinecheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "patientline" & Chr(34) & " Or [mainstatic]![Customer] ="
If residentialcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "residential" & Chr(34) & " Or [mainstatic]![Customer] ="
If sportstvcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "sports tv" & Chr(34) & " Or [mainstatic]![Customer] ="
If strCUSTOMER <> "" Then
strCUSTOMER = Left$(strCUSTOMER, Len(strCUSTOMER) - 28)
strWHERE = strWHERE & " And [mainstatic]![Customer]=" & strCUSTOMER
Else
strWHERE = strWHERE
End If
strSQL = "SELECT" & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
BuildSQLString = True
End Function
The problem I am having now is that when there are more than one customers selected, say coral and aramiska the query pulls ALL the results with the companies equalling coral or aramiska instead of taking into account the shopnumber, priority, status and date parts of the query..
Any help would be greatly appreciated.
ps - BIG THANKYOU to lwells who has helped me no end with my access work.
|
Dev Articles Community Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
