VB Issue pulling data with date specific

[gmazza76]

Good afternoon

I can pull data from access into a tab on excel currently without using a date specific period. But I now need to add the date part in and I seem to be pulling no data through and I am unsure why.
The only issue I can see is that the "table1.[Survey Completion Date & Time]" date format is "General Date - dd/mm/yyyy hh:mm:ss" and I have chaned the date1 type to "DD/MM/YYYY HH:MM:SS" and it doesnt pull data through. If I remove the date part I get data dropping into my A10 area.

Any ideas as I feel as though I am going round in circles.

NType = ThisWorkbook.Sheets("Loader").Range("B10").Value
date1 = Format(ThisWorkbook.Sheets("sheet1").Range("C5").Value, "DD/MM/YYYY")
date2 = Format(ThisWorkbook.Sheets("sheet1").Range("C6").Value, "DD/MM/YYYY")

If NType = "NPS" Then
    mysqlst = "SELECT table1.CaseID, table1.[Case Status], " & _
    "table1.[Assigned To], table1.[Date Assigned],  " & _
    "table1[Call Date 1] FROM table1 WHERE (table1.NPS='" & NType & "') And ((table1.[Survey Completion Date & Time]) between " & Chr(35) & date1 & Chr(35) & " AND " & Chr(35) & date2 & Chr(35) & ");"
End If

Set cn = New ADODB.Connection
With cn
 .ConnectionString = con1
 .Open
End With

Set rs = New ADODB.Recordset
rs.Open mysqlst, cn, adOpenDynamic, adLockPessimistic
    ThisWorkbook.Sheets("Sheet1").Range("A10").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

thanks in advance
Gavin

 

[Trevor G]

Hi Gavin can you add a query in Access and then import it via vba, might be an easier solution.

 

[gmazza76]

Hi Gavin can you add a query in Access and then import it via vba, might be an easier solution.

I was thinking this , but I have created a separate date field and would like to pick up the date range or if its blank.
I have used the [Date Assigned] = is blank is there an easier way?

If NType = "NPS" Then
    mysqlst = "SELECT CustomerSurveyInsightAllTable.CaseID, CustomerSurveyInsightAllTable.[Case Status], " & _
    "Table1.[Assigned To], Table1.[Date Assigned],  " & _
    "Table1.[Call Date 1] FROM Table1 WHERE (Table1.NPS='" & NType & "') And ((Table1.[Date Assigned]) between " & Chr(35) & date1 & Chr(35) & " AND " & Chr(35) & date2 & Chr(35) & " ) Or (Table1.[Date Assigned] = """")) ;"
End If

 

[gmazza76]

@Trevor G I have worked some code and instead of using the date I have managed to use days old of query but I have an issue now on the final "and" request.
If I remove the "and (Table1.[Case Status]" clause the code runs, but I currently an error "No value given for one or more required parameters".

Can you point me in the right direction as the "Status" is being recognised?

Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim mysqlst, mysearch1 As String

Status = ThisWorkbook.Sheets("Loader").Range("B15").Value
mysearch1 = ThisWorkbook.Sheets("Loader").Range("B10").Value

If ThisWorkbook.Sheets("Loader").Range("B10").Value = "NPS" Then
    mysqlst = "SELECT Table1.CaseID, Table1.[Case Status], Table1.[Assigned To], Table1.[Date Assigned], " & _
    "Table1.[Root Cause], Table1.Outcome, Table1.[Outcome 2], Table1.[Survey Completion Date & Time], " & _
    "Table1.[Date & Time of Call], Table1.NPS, Table1.[Call Date 1],  " & _
    "Table1.[Call Date 2], Table1.[CallID], Table1.[Unique Staff Identifier], Table1.Sort_Name, " & _
    "Table1.[Team Leader], Table1.Lead, Table1.[Brand Score], Table1, " & _
    "Table1.[Brand Verbatim], Table1.[Agent Score], Table1.[Agent], Table1.[Agent Verbatim]," & _
    "Table1.[Parent Organisation Name], Table1.[Open to discussion], Table1.[Contact Method], Table1.[Outstanding Issues]," & _
    "Table1.[Email Address], Table1.[Contact Phone Number], Table1.[ALERT:], Table1.[Contact Attempt]," & _
    "Table1.[Contact Type], Table1.[CRM Updated], Table1.[Attribute], Table1.[TOFU]," & _
    "Table1.[Call Findings], Table1.[Conclusion] FROM Table1 WHERE (Table1.NPS='" & mysearch1 & "') and ((Table1.Age<= 21) and (Table1.[Case Status]<> " & Status & ")) ;"
End If

Set cn = New ADODB.Connection
With cn
 .ConnectionString = con1
 .Open
End With

Set rs = New ADODB.Recordset
rs.Open mysqlst, cn, adOpenDynamic, adLockPessimistic
    ThisWorkbook.Sheets("CurrentInFile").Range("B9").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

thanks in advance

 

[Trevor G]

we don't have any example of your database or example of the table data so difficult to work on a solution for you.

Can you show some example data and adjust anything if its sensitive info.

Have you tried a docmd.transferspreadsheet method?

I would still suggest creating a query and then add your date parameters and look to use that as the import to excel.