Results 1 to 9 of 9
  1. #1
    Ed1138 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2014
    Posts
    9

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

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Ed1138 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2014
    Posts
    9
    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.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That does sound bizarre. Think I will have to work with data to analyse issue.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Ed1138 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2014
    Posts
    9
    Hope you can see this:
    Click image for larger version. 

Name:	true or false issue.jpg 
Views:	5 
Size:	198.2 KB 
ID:	18319

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I already believed you - a picture is not data I can analyse and test.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Ed1138 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2014
    Posts
    9
    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.

  8. #8
    Ed1138 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2014
    Posts
    9
    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.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That is the oddest corruption ever! Did not occur to me but now seems obvious. Glad you resolved issue.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 02-14-2014, 04:41 PM
  2. True And False
    By azhar2006 in forum Forms
    Replies: 4
    Last Post: 12-23-2013, 03:50 AM
  3. Currency fields and if to get true/false
    By Ruegen in forum Programming
    Replies: 1
    Last Post: 09-29-2013, 07:40 PM
  4. Yes/No True/False
    By DSTR3 in forum Access
    Replies: 5
    Last Post: 12-04-2010, 05:56 PM
  5. True or false
    By tleec in forum Queries
    Replies: 1
    Last Post: 02-01-2008, 10:41 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums