Access Dynamic Query problem

[jaabaar]

Dear All
I hope you can Help me. I have problem I am trying to solve but to no avail.
I need to display all result based on dates from three different fields(Fish Date Start, Fish Date End and NoneDate). I need to be able to dynamically build the SQL to get results as it is based on 3 Ors. Steps as follows:
1. Generate a make tables with everything.
2. Selects All results if Standard and Catching Vessel vessels found on criteria found is Fish start date and Fish end date.
3. If in question 2 one or two criteria found then add to returned results Non Catching if exist on date NoneDate also based of fish start and end date.
4. The SQL need to have 1 or up to 3 criteria.
I hope that makes sense Code as follow so far as you can see if only need to see if those 3 criteria exist and if the do built the three it only builds one.

Private Sub lstQuery_DblClick(Cancel As Integer)
Dim cnnDB As ADODB.Connection
Dim myRecordSet, myFilteredRecordSet As ADODB.Recordset
'Dim tblname As String
Dim qry_Issues As String
'Dim TEMP As Long

Dim strChar As String, strHoldString As String
Dim i As Integer
Dim dtDateStart, dtDateEnd As Date

Dim strNoneDate As Date
Dim strWhereSQL As String
Dim strFullSQL As String
Dim lngNumber As Long


' Get connection to current database.
Set cnnDB = CurrentProject.Connection
Set myRecordSet = New ADODB.Recordset

dtDateStart = Format(CDate(Me.txtFishingStart.Value), "yyyy/mm/dd")
dtDateEnd = Format(CDate(Me.txtFishingEnd.Value), "yyyy/mm/dd")

DoCmd.Hourglass (1)
strChar = lstQuery.Column(1)
Select Case strChar

Case "qry_fishing_Operation_Catch_By_Fishing_Operation_By_Week"
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_fishing_Operation_Catch_By_Fishing_Operation_By_Week"

qry_Issues = "Select * from qry_fishing_Operation_Catch_By_Fishing_Operation_By_Week_Layout"
Set myRecordSet = cnnDB.Execute(qry_Issues)

While Not myRecordSet.EOF

Debug.Print myRecordSet.Fields("Operation Type (Standard/JFO/OFO)")
Debug.Print myRecordSet.Fields("Catching (C) / Non-catching (N)")
Debug.Print myRecordSet.Fields("Fish Date Start")
Debug.Print myRecordSet.Fields("Fish Date End")
Debug.Print myRecordSet.Fields("Non Date")


'-- Store the initial Where statement with whatever is from
'-- the Category criteria.
If myRecordSet.Fields("Operation Type (Standard/JFO/OFO)") = "Standard" Or myRecordSet.Fields("Catching (C) / Non-catching (N)") = "Catching Vessel" Then

strWhereSQL = " Where " & "[Fish Date Start]" & ">=" & "#" & dtDateStart & "#" & " OR " & "[Fish Date End]" & "<=" & "# " & dtDateEnd & "#"
End If

'-- If a Section Code was passed back,
'-- then add it to the Where clause.
If myRecordSet.Fields("Catching (C) / Non-catching (N)") = "Non-Catching Vessel" Then

If strWhereSQL <> "Where " Then
strWhereSQL = strWhereSQL & " WHERE "
End If
strWhereSQL = strWhereSQL & "[Non Date]" & ">=" & "#" & dtDateStart & "#" & " OR " & "[Non Date]" & "<=" & "# " & dtDateEnd & "#"
End If

' '-- If a Section Code was passed back,
' '-- then add it to the Where clause.
' If myRecordSet.Fields("Catching (C) / Non-catching (N)") = "'" & "Catching Vessel" & "'" Then
'
'
' If strWhereSQL <> "Where " Then
' strWhereSQL = strWhereSQL & " WHERE "
' End If
' strWhereSQL = strWhereSQL & "[Fish Date Start]" & ">=" & "#" & dtDateStart & "#" & " AND " & "[Fish Date End]" & "<=" & "# " & dtDateEnd & "#"
' End If

'-- If no criteria was chosen, make it
'-- so the subform will be blank.

If strWhereSQL = "Where " Then
strWhereSQL = "Where False;"
End If

'-- Create the new SQL String and Store it to the Recordsource.
'strFullSQL = "Select * From SearchResult " & strWhereSQL
strFullSQL = "SELECT * FROM qry_fishing_Operation_Catch_By_Fishing_Operation_By_Week_Layout" & strWhereSQL

Set myFilteredRecordSet = cnnDB.Execute(qry_Issues)

myRecordSet.MoveNext
Wend

' Call SendToExcel_CPC_DataChecks("qry_fishing_Operation_Catch_By_Fishing_Operation_Layout", "Fishing Operations (Catch by Fishing Operation)")

DoCmd.SetWarnings True


Case Else
DoCmd.SetWarnings False
DoCmd.SetWarnings True
MsgBox "Unknown query, please repeat selection", vbInformation



End Select

DoCmd.Hourglass (0)

cnnDB.Close
Set myRecordSet = Nothing
Set cnnDB = Nothing
End Sub

Thanks in advance for your help

 

[jedraw]

You should adopt a naming convention that does NOT allow embedded spaces and/or special characters.
You will prevent a lot of headaches if you limit field and object names to alphanumeric and "_".

Also

Dim myRecordSet, myFilteredRecordSet As ADODB.Recordset

does NOT do what you think.
myRecordSet will be dimmed Variant

You must explicitly dim variables, otherwise you get Variants by default

You can say
Dim myRecordSet As ADODB.Recordset, myFilteredRecordSet As ADODB.Recordset or

Dim myRecordSet As ADODB.Recordset
Dim myFilteredRecordSet As ADODB.Recordset

 

[SkipVought]

Also the Format function returnsa STRING not a Date data type. Presumably your db Date fields are REAL DATE TYPE fields.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vbajock]

Like jedraw says, I always declare all variables separately on it's own line. It makes the code clearer and that comma stuff is for scripting hackers, professional database programmers never use it. Skip, I believe Format$() returns a string, and Format() returns a variant.