Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: SQL Statement Not returning expected results

    I have some code that evaluates whether a field for a certain row of data is marked true.

    How ever when the code runs and it is returning the recordset as not EOF - meanining if finds a record meeting that criteria - but the result is wrong - I have no records marked aas true and the resulting recordset should be - No records here is the snippett - syntactically the code is correct it just is returning a recordset when it shouldnot

    strsql2 = "SELECT tblJobCard.jc_closure " _
    & "From tblJobCard " _
    & "WHERE jc_closure = true and tblJobCard.Job_Card_Number=" & "'" & Forms![frmcms]![Job_Card_Number] & "'"
    Debug.Print strsql2


    Set rst1 = CurrentDb.OpenRecordset(strsql2, dbOpenDynaset)
    If Not rst.EOF Then 'meaning if a there is one record meeting the criteria

    CMDsTATUS.BackColor = vbYellow ' strsql2 should return an empty recordset because jc_closure is ion fact False for this job_card_number however the code continues as if there is a resulting recordset - any ideas?

    ' CMDsTATUS.Enabled = False
    Else
    CMDsTATUS.BackColor = vbGreen - 'this should be where my code ends up and executes but it never makes it to here
    End If


    I have pasted the strsql2 into the query sql view and run it and it returns a record - but that record shows that jc_closure is in fact set to false.

    I am going crazy over this.

    I know the work around is to do a dlookup and if the value is 0 then handle it - but that is not a purist way to accomplish this and should we port over to sql server for the back-end then the dlookup will fail anyway.

    thank you
    Dale Houston, TX

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    WHERE   bit_field = true
    won't work in SQL Server either.

    I've just knocked up a table in Access2010 with a single "Yes/No" field and the following queries return the correct results
    Code:
    SELECT id
    FROM  Table1
    WHERE   id = true
    Code:
    SELECT id
    FROM  Table1
    WHERE   id = false
    Code:
    SELECT id
    FROM  Table1
    WHERE   id = 1
    Code:
    SELECT id
    FROM  Table1
    WHERE   id = 0
    I would recommend using the final method (or its inverse: "id<>0" over "id=0").
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In fact, here's an even more illustrative query
    Code:
    SELECT id
         , IIf(id=True,"true")
         , iif(id=false, "false")
         , iif(id=1, "0")
         , iif(id=0, "0")
         , iif(id=-1, "-1")
    FROM Table1
    George
    Home | Blog

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Totally understand the bit_field = true concept
    always <>0 in sql but still had the code jumping to the code for the

    If not rst.eof line when I know for a fact that field was set to NO therby the resulting recordset should have been rst.eof (True)

    any way I did the hack workaround on the Dlookup and it worked as expected - I have a couple of functions actually that simulate the dlookup but are compatible with sql - so it is no big deal - but I really wish I had time to see what the deal was - with that - first time this has ever happened

    Thank you for the swift reply.
    Dale Houston, TX

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    strictly speaking in VBA you should use the intrinsic constants vbTrue and vbFalse which will hide the implementation of the boolean true false in SQl or vba code
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    True true

    I handled this way

    dim blClosure as boolean

    blClosure = DLookup("[jc_closure]", "tbljobcard", "job_card_number = " & "'" & Forms![frmcms]![Job_Card_Number] & "'")
    If blClosure = -1 Then
    CMDsTATUS.BackColor = vbGreen
    CMDsTATUS.Enabled = False
    Else
    CMDsTATUS.BackColor = vbYellow
    End If
    Dale Houston, TX

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •