Hi all,
This kind of hard to explain specially since english is not my first language, let's see if can make myself understand, im trying to perform a Select query to get the records within a time frame(column ContactDate contains the date/time values) the query is as follow:
Code:
SELECT Val([ACD_ID] & Format(DateValue([ContactDate]),'0')) AS SEARCH_CODE, CFinal, 1 AS Expr1, COPCFCR, FCRPossible, RecordName
FROM [YTD-Daily_Report]
WHERE ((([YTD-Daily_Report].[ContactDate])>=#9/01/2014# And ([YTD-Daily_Report].[ContactDate])<=#10/01/2014#));
In the source Table(YTD-Daily_Report) COPCFCR and FCRPossible are define as Checkboxes(true or false), when i run this query using Access within the database it all works well i get a column with the concatenated value of the ID + Date, a column containing the score, a column with a 1 in it, a column with the COPCFCR value(true or false), a column with the FCRPossible value(true or false), and column with the RecordName, at this point if i compared with the values on the table they match 100%.
Now, i took this query and put it on an Excel workbook but when it runs it returns the values for the columns COPCFCR and FCRPossible wrong, sometimes a false is returned as true or viceversa and other times the values match 100% with the corresponding values on the source table, for example in some rows COPCFCR is returned as true when it should be false according to the source table.
Here is the Excel VBA code I'm using:
Code:
Dim rsSource As New Recordset
Dim m_Connection As New Connection
Dim rngTarget as range
dim result as long
m_Connection.Provider = "Microsoft.ACE.OLEDB.12.0"
m_Connection.Open "Path and name of the Database"
strQuery = "SELECT Val([ACD_ID] & Format(DateValue([ContactDate]),'0')) AS SEARCH_CODE, CFinal, 1 AS Expr1, COPCFCR, FCRPossible, RecordName" & Chr(13) & _
"FROM [YTD-Daily_Report]" & Chr(13) & _
"WHERE ((([YTD-Daily_Report].[ContactDate])>=#" & Format(START_DATE, "m/dd/yyyy") & "# And ([YTD-Daily_Report].[ContactDate])<=#" & Format(STOP_DATE + 1, "m/dd/yyyy") & "#));"
rsSource.Open strQuery, m_Connection, adOpenForwardOnly, adLockReadOnly
Set rngTarget = Range("A2")
result = rngTarget.CopyFromRecordset(rsSource)
If rsSource.State Then rsSource.Close
Set rsSource = Nothing
If m_Connection.State Then m_Connection.Close
Set m_Connection = Nothing