MS Access 2007 Query Returns False values as True and True as False

[Ed1138]

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:

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

[June7]

Are you saying the data from the two fields import wrong or the comparison calc result is wrong?
What is doing the comparison - expressions in cells? If you want to provide files for analysis, follow instructions at bottom of my post.

[Ed1138]

What im trying to say is that if i go to the table i will see that for an especific record the values in COPCFCR and FCRPossible are set in one way, but when i run this query it shows different values in this two columns for that same record. For example: In the source table(YTD-Daily_Report) I have the record 20140901_W1_NormaVillarreal and the values for are COPCFCR FCRPossible are both TRUE, but when the query is executed the row in that resulting table shows COPCFCR= FALSE and FCRPossible=TRUE.

[June7]

That does sound bizarre. Think I will have to work with data to analyse issue.

[Ed1138]

Hope you can see this:

[June7]

I already believed you - a picture is not data I can analyse and test.

[Ed1138]

I just made a copy of the table in another database, created a subrutine y in a new workbook so i can send it, but when a execute the code, guess what?, it works, so im suspeting the database has some corruption or something else, hope i won't have to build it again, i'll keep you post.

[Ed1138]

Thank you for your follow up June7 , it is confirmed the database has some corruption issues and now i will have to backup the data to pass it into another file.

[June7]

That is the oddest corruption ever! Did not occur to me but now seems obvious. Glad you resolved issue.