Ms Access 2007 queries returns Boolean values false when it should be true

Question

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:

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:

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

Any ideas what could be happening?

What do you see in the strQuery variable just before executing the query? I think you will see some difference when comparing it with the original query, and that difference will reveal you the problem. — Caffé
strQuery stores the sql code, the value is exactly what i posted above — Eduardo Rivera
Are you sure? In your VBA code, #" & Format(STOP_DATE + 1, "m/dd/yyyy") & "#));" would result one day more in STOP_DATE. In your original query the second date (supposing it's also in the m/dd/yyyy format) is one month more than the first date. It could explain the outcome differences. — Caffé
Yeah that's how its meant to be, i need to add one more day to stop_date cause this variable store the last date of the month that i want but due to the nature of the data in ContactDate(date+time) i need to add that day so i can capture all dates(in this example september). This part only affects the dates that are selected it doesnt explain why im getting different values in columns COPCFCR and FCRPossible. — Eduardo Rivera
Like i said before if i run the query using Access within the same database i will get the result that i want, but if i run it in excel vba i will get different values for the same row. Im exploring the posibility of corruption in database i'll let you know my findings. — Eduardo Rivera

Eduardo Rivera Eduardo Rivera · Accepted Answer · 2014-10-06T20:50:59

It seems the database file was corrupted and this caused the data to be exported in a different way that it was call for. The query is working fine as it is.

Ms Access 2007 queries returns Boolean values false when it should be true

1 Answers