Excel SQL query not working with dates

[JasonEnsor]

Hi Guys,

I am using Excel VBA with ADO to build an SQL query to pull data from another Workbook...everything is working great except when I am getting data from multiple dates showing up and not just the date I am trying to query.
The query is built from values that are captured from a userform using comboboxes and they seem to be putting the correct values in. I think the issue is in my SQL which is listed below.

[Code SQL]
SELECT * FROM [SupportBookingsS] WHERE [PK] IS NOT NULL AND [StudyDate] = '15/10/2013' AND [SupportWorker] = 'Not Required' OR [Interpreter] = 'Not Required' OR [ENotetaker] = 'Not Required' ORDER BY [StudyDate] DESC,
[LastName], [StartTime] DESC;
[/Code]

Any thoughts on why the above isn't working would be appreciated.

The report should retrieve all records that have the correct date if any of the SupportWorker, Interpreter or ENotetaker = 'Not Required'

I am using the following code to build my SQL, I know it is long and could possibly be re written but due to the lack of time I have on this project and my SQL knowledge it has just been thrown together.

[Code vba]
Public Sub BuildSearchSQL(tableName As String, columnHeader As String, searchDate As String)

Dim basicSQL As String
Dim SQLEnd As String
Dim SQLSearchCol As String

basicSQL = "SELECT * FROM [" & tableName & "$]"
SQLSearchCol = " WHERE [" & columnHeader & "] = '" & searchTxt & "'"
SQLEnd = ";"

If columnHeader = "PK" Then
If reqStatus = "All" Then
If searchDate = "All" Then
sSearchSQL = "SELECT * FROM [" & tableName & "$] WHERE [PK] IS NOT NULL ORDER BY [StudyDate] DESC, [LastName], [StartTime] DESC;"
Else
sSearchSQL = "SELECT * FROM [" & tableName & "$] WHERE [PK] IS NOT NULL AND [StudyDate] = '" & searchDate & "'ORDER BY [StudyDate] DESC, [LastName], [StartTime] DESC;"
End If
Else
If searchDate = "All" Then
sSearchSQL = "SELECT * FROM [" & tableName & "$] WHERE [PK] IS NOT NULL AND [SupportWorker] = '" & reqStatus & "' OR [Interpreter] = '" & reqStatus & _
"' OR [ENotetaker] = '" & reqStatus & "'ORDER BY [StudyDate] DESC, [LastName], [StartTime] DESC;"
Else
sSearchSQL = "SELECT * FROM [" & tableName & "$] WHERE [PK] IS NOT NULL AND [StudyDate] = '" & CStr(searchDate) & "' AND [SupportWorker] = '" & reqStatus & "' OR [Interpreter] = '" & reqStatus & _
"' OR [ENotetaker] = '" & reqStatus & "'ORDER BY [StudyDate] DESC, [LastName], [StartTime] DESC;"
End If

End If
Else

Select Case reqStatus
Case "All"

If searchDate = "All" Then
sSearchSQL = basicSQL & SQLSearchCol & "ORDER BY StudyDate, LastName, StartTime" & SQLEnd
Else
sSearchSQL = basicSQL & SQLSearchCol & " AND [StudyDate] = '" & searchDate & "'ORDER BY [StudyDate] DESC, [LastName], [StartTime] DESC" & SQLEnd
End If

Case "Required"
If searchDate = "All" Then
sSearchSQL = basicSQL & SQLSearchCol & _
" AND [SupportWorker] = '" & reqStatus & "' OR [Interpreter] = '" & reqStatus & _
"' OR [ENotetaker] = '" & reqStatus & "'ORDER BY [StudyDate] DESC, [LastName], [StartTime] DESC" & SQLEnd
Else
sSearchSQL = basicSQL & " WHERE [" & columnHeader & "] = '" & searchTxt & "' AND [SupportWorker] = '" & reqStatus & "' AND [StudyDate] = '" & searchDate & _
"'" & " OR [Interpreter] = '" & reqStatus & _
"' OR [ENotetaker] = '" & reqStatus & "'ORDER BY [StudyDate] DESC, [LastName], [StartTime] DESC" & SQLEnd
End If
Case "Not Required"
If searchDate = "All" Then
sSearchSQL = basicSQL & SQLSearchCol & _
" AND [SupportWorker] = '" & reqStatus & "' OR [Interpreter] = '" & reqStatus & _
"' OR [ENotetaker] = '" & reqStatus & "'ORDER BY [StudyDate] DESC, [LastName], [StartTime] DESC" & SQLEnd
Else
sSearchSQL = basicSQL & SQLSearchCol & _
" AND [SupportWorker] = '" & reqStatus & "' AND [StudyDate] = '" & searchDate & _
"'" & " OR [Interpreter] = '" & reqStatus & _
"' OR [ENotetaker] = '" & reqStatus & "'ORDER BY [StudyDate] DESC, [LastName], [StartTime] DESC" & SQLEnd
End If
End Select
End If
End Sub
[/code]

All help is appreciated


Regards

J.

 

[SkipVought]

Hi,
You must convert TEXT to DATE (NUMERIC)

# delimiter will convert, however i would itge you to use a unambiguous yyyy/mm/dd structure.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PHV]

What about this SQL ?

SELECT * FROM [SupportBookingsS] WHERE [PK] IS NOT NULL AND [StudyDate] = [!]#2013-10-15#[/!] 
AND [!]([/!][SupportWorker] = 'Not Required' OR [Interpreter] = 'Not Required' OR [ENotetaker] = 'Not Required'[!])[/!]
ORDER BY [StudyDate] DESC, [LastName], [StartTime] DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xlbo]

Phv has it but please note that you should never use select * as if the table changes at all it could wreck whatever is relying on it. For the sake of a fee seconds to script out the field names, it is well worth it to abou future issues

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question