Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    83

    Unanswered: help needed with SQL

    I am having a problem with the below syntax.


    mstrSQL = "SELECT tblMain.PatientName " _
    & "FROM tblPatients INNER JOIN tblMain ON tblPatients.PatientID = tblMain.PatientName " _
    & "WHERE (tblPatients.Lastname & ", " & tblPatients.Firstname) = '" & Me![cboSelect] & "'" & ""

    The final query should be similar to this:

    SELECT tblMain.PatientName
    FROM tblPatients INNER JOIN tblMain ON tblPatients.PatientID = tblMain.PatientName
    where tblPatients.lastname &", " & tblPatients.firstname = 'Plotkin, Jona';

    The syntax error is in the Where clause (tblPatients.Lastname & ", " & tblPatients.Firstname) .
    Any help is greatly appreciated. Thanks in advance.

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    When I evaluate your SQL statement using the immediate window and replacing your variable with a string, I get the following:

    Statement:
    ------------
    ?"SELECT tblMain.PatientName " _
    & "FROM tblPatients INNER JOIN tblMain ON tblPatients.PatientID = tblMain.PatientName " _
    & "WHERE (tblPatients.Lastname & ", " & tblPatients.Firstname) = '" & "LN, mike" & "'" & ""

    Result:
    SELECT tblMain.PatientName FROM tblPatients INNER JOIN tblMain ON tblPatients.PatientID = tblMain.PatientName WHERE (tblPatients.Lastname & & tblPatients.Firstname) = 'LN, mike'

    Have you tried not separating your comma between lastname and first name and / or using Chr(44)?
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Okay, I see the problem you are experiencing (or at least I recreated a similar one).

    I'm sure Rudy (r937) has the answer in a slicker fashion, but I would separate out the last name from the first name then create the where clause differently:

    dim strName
    strName = Me![cboSelect]

    mstrSQL = "SELECT * FROM tblPatients WHERE ((LastName = '" & Left(strName, InStr(strName, ",") - 1) & _
    "') and (FirstName = '" & Trim(Mid(strName, InStr(strName, ",") + 1, Len(strName))) & "'));"
    All code ADO/ADOX unless otherwise specified.
    Mike.

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Another way to do it is:

    mstrSQL = "SELECT tblMain.PatientName, FullName as (tblPatients.Lastname & "", "" & tblPatients.Firstname)" _
    & "FROM tblPatients INNER JOIN tblMain ON tblPatients.PatientID = tblMain.PatientName " _
    & "WHERE Fullname = '" & Me![cboSelect] & "'"

    Notice the extra double quotes are around the comma between the last name and the first name.

Posting Permissions

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