Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Location
    St Louis
    Posts
    14

    Unanswered: Help for the Irish !

    I'm fairly new to VBA and for the life of me I can't get this simple select string to accept a last name of O'Brian or any '. and being Irish this is not good :-}

    strFirstName = Me![tb_firstname]
    strLastName = Me![tb_lastname]

    strSQL = "SELECT * FROM Applicant WHERE (((Applicant.FirstName)= '" & Me.tb_firstname.Value & "' ) AND ((Applicant.LastName)= '" & Me.tb_lastname.Value & "' ));"

    'Find Record
    Set MyRs1 = MyDb.OpenRecordset(strSQL

    Thanks

  2. #2
    Join Date
    Oct 2002
    Location
    UK
    Posts
    21
    You need to escape out the single quote character. In Access you can do this by using the single quote twice.

    Try something like this:

    Code:
    Public Function Quote(varValue)
        If IsNull(varValue) Or IsEmpty(varValue) Then
            Quote = "Null"
        Else
            Quote = "'" & Replace(varValue, "'", "''") & "'"
        End If
    End Function
    This function puts single quotes around the string, and doubles any inside it, so your SQL would now be:

    strSQL = "SELECT * FROM Applicant WHERE (((Applicant.FirstName)=" & Quote(Me.tb_firstname) & ") AND ((Applicant.LastName)=" & Quote(Me.tb_lastname) & "));"

  3. #3
    Join Date
    Jan 2003
    Location
    St Louis
    Posts
    14
    Originally posted by Conchur
    You need to escape out the single quote character. In Access you can do this by using the single quote twice.

    Try something like this:

    Code:
    Public Function Quote(varValue)
        If IsNull(varValue) Or IsEmpty(varValue) Then
            Quote = "Null"
        Else
            Quote = "'" & Replace(varValue, "'", "''") & "'"
        End If
    End Function
    This function puts single quotes around the string, and doubles any inside it, so your SQL would now be:

    strSQL = "SELECT * FROM Applicant WHERE (((Applicant.FirstName)=" & Quote(Me.tb_firstname) & ") AND ((Applicant.LastName)=" & Quote(Me.tb_lastname) & "));"

    That's a good Idea but it still nukes on it ... I check for Null prior to using the so all I have to do is call the Quote..... maybe I should just create a recordSet and use FindFirst... that doesn't seem to blowup..... would seem that it's just a simple thing....

  4. #4
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    why don't you use CHR function?


    strSQL = "SELECT * FROM Applicant WHERE (((Applicant.FirstName)= " & chr(34) & Me.tb_firstname.Value & chr(34) & " ) AND ((Applicant.LastName)= " & chr(34) & Me.tb_lastname.Value & chr(34) & " ));"

    if you want to chatch NULLs, you can do it this way....

    If IsNull(Me.tb_firstname.Value) Then
    strFirstName = " is Null "
    Else
    strFirstName = " = " & Chr(34) & Me.tb_firstname.Value & Chr(34)
    End If

    If IsNull(Me.tb_lastname.Value) Then
    strLastName = " is Null "
    Else
    strLastName = " = " & Chr(34) & Me.tb_lastname.Value & Chr(34)
    End If

    strSQL = "SELECT * FROM Applicant WHERE Applicant.FirstName " & strFirstName & " AND Applicant.LastName " & strLastName & " ;"



    jiri

  5. #5
    Join Date
    Jan 2003
    Location
    St Louis
    Posts
    14
    Originally posted by playernovis
    why don't you use CHR function?


    strSQL = "SELECT * FROM Applicant WHERE (((Applicant.FirstName)= " & chr(34) & Me.tb_firstname.Value & chr(34) & " ) AND ((Applicant.LastName)= " & chr(34) & Me.tb_lastname.Value & chr(34) & " ));"

    if you want to chatch NULLs, you can do it this way....

    If IsNull(Me.tb_firstname.Value) Then
    strFirstName = " is Null "
    Else
    strFirstName = " = " & Chr(34) & Me.tb_firstname.Value & Chr(34)
    End If

    If IsNull(Me.tb_lastname.Value) Then
    strLastName = " is Null "
    Else
    strLastName = " = " & Chr(34) & Me.tb_lastname.Value & Chr(34)
    End If

    strSQL = "SELECT * FROM Applicant WHERE Applicant.FirstName " & strFirstName & " AND Applicant.LastName " & strLastName & " ;"



    jiri

    Thanks that did it.... wasn't sure what the chr function did.... I find it wierd I have to insert Numbers to bracket my variables in order for the select string to work......

  6. #6
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    CHR function returns any character represented by ACSII code

    34 is doublequote


    jiri

  7. #7
    Join Date
    Jan 2003
    Location
    St Louis
    Posts
    14
    Originally posted by playernovis
    CHR function returns any character represented by ACSII code

    34 is doublequote


    jiri

    I guess me confussion steemed from using PowerBuilder's function.... Char(long) converted a Number to a String..... and in the VBA defination of Chr it's
    Function Chr(CharCode As Long)...... apparently I didn't look at the definition closely enough... CharCode hence the 34 as Double Quote


    Thanks for the help...

Posting Permissions

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