Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What's wrong with this SQL (VB6/Access 2K)

    I am passing the following SQL statement from Visual Basic to an Access 2000 database and it is erroring. If I take out the WHERE clause it works fine:

    SELECT tblPatient.UID, tblPatient.MRNumber, tblPatient.COG_ID, tblPatient.DOB, tblPatient.Gender, tblPatient.PMD, tblPatient.CRA, tblPatient.DID, tblPatient.DXDate, tblPatient.FirstName, tblpatient.lastname FROM tblPatient WHERE cboGender='M' And TDBDateDX=#4/13/1965# And TDBDOB=#4/13/1965# And TDBFname='Barry' And TDBLname='White'

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: What's wrong with this SQL (VB6/Access 2K)

    What is the connection between the fields listed on the first line and the fields listed in the WHERE clause? Maybe you need to use the dotted notation and connect them to one of the tables?

    When stumped, I try to get the query to work interactively in Access. Recently, I have started saving the query in the database, setting up parameters for the variables in the query, and then calling it as a "stored procedure." I'm not sure this is any faster to develop or run, but I'm learning something new. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What's wrong with this SQL (VB6/Access 2K)

    As Jefferson pointed out, your WHERE clause doesn't appear to have anything to do with the SELECT portion of the SQL. The WHERE clause has to refer to fields in the fields list of the SELECT. What it looks like here is a bunch of references to controls. If you were doing that, you would say something like "WHERE tblPatient.Gender = Me.cboGender".
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What's wrong with this SQL (VB6/Access 2K)

    Okay, paint me totally embarrassed. Jefferson you were right, Charlotte, you hit it on the head. To make my embarrassment complete I'd tell you how long I spent trying to make that work yesterday, but I don't think I'm ready to make that leap yet.

    I actually built that WHERE clause from a control loop, taking the control.name and using that as the field to query. I'm trying to build a search feature for all the fields in my form. I'll start looking at a control array or enumerated constants - and force myself to take a break when I code for 12 hours.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What's wrong with this SQL (VB6/Access 2K)

    <hr>force myself to take a break when I code for 12 hours<hr>
    I see. You're going to insert a Pause in your code? <img src=/S/grin.gif border=0 alt=grin width=15 height=15> <img src=/S/duck.gif border=0 alt=duck width=23 height=23>
    Charlotte

Posting Permissions

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