Help with IF clause not working properly 1

[davyre]

Hi,
I have this code

Dim ctr As Long
Dim oldRowSource As String
Dim newRowSource As String
Dim listValue As Long
Dim unitID As Long


oldRowSource = "SELECT TblCustOrderUnit.OrderUnitID, TblCustOrderUnit.OrderID, TblCustOrderUnit.UnitID, TblCustOrderUnit.SerialNumber FROM TblCustOrderUnit;"

MsgBox (oldRowSource & vbCrLf & "=====================================================" & vbCrLf & Me.List0.RowSource)
listValue = Me.List0.Value
unitID = Me.List0.Column(2)
newRowSource = "SELECT TblCustOrderUnit.OrderUnitID, TblCustOrderUnit.OrderID, TblCustOrderUnit.UnitID, TblCustOrderUnit.SerialNumber " _
             & "FROM TblCustOrderUnit " _
             & "WHERE (((TblCustOrderUnit.UnitID)=" & unitID & "));"

[highlight #FCE94F]If Me.List0.RowSource = oldRowSource Then[/highlight]
    ctr = 1
    MsgBox (ctr & vbCrLf & unitID)
    Me.List0.RowSource = newRowSource
    Me.List0.Requery
ElseIf Me.List0.RowSource = newRowSource Then
    ctr = 2
    MsgBox (ctr & vbCrLf & unitID)
Else
    Msgbox ("go to else")
End If
ctr = 2
End Sub

What I want to ask is the highlighted line, it compares the RowSource with the oldRowSource variable that I created earlier. While I have the msgbox to see the value between List0.RowSource and the oldRowSource, it seems exactly the same. But why it wont go to IF clause? It always goes to ELSE as I get msgbox go to else..

**note:The first clause in msgbox is the oldRowSource variable, and the second(bottom) is the List0.RowSource..It is exactly the same, so it should go to IF clause. Any help? Thanks

 

[MajP]

Comparing two strings may be a little iffy.

http://www.codevba.com/visual-basic-source-code/vb-string/checking_if_two_strings_are_equal.htm

The following two strings would probably provide the same results, but are not equal. Sql is not very particular about spaces and capitalization.

" SELECT * FROM someTable "
"Select * from SOMETABLE"

Public Function IsSameSQL(sql1 As Variant, sql2 As Variant) As Boolean
  If Not IsNull(sql1) And Not IsNull(sql2) Then
    sql1 = Replace(sql1, " ", "")
    sql2 = Replace(sql2, " ", "")
    sql1 = UCase(sql1)
    sql2 = UCase(sql2)
    If StrComp(sql1, sql2, vbTextCompare) = 0 Then
      IsSameSQL = True
    End If
  End If
End Function

Public Sub testcompare()
  Dim sql1 As String
  Dim sql2 As String
  sql1 = "select *  from tbl"
  sql2 = " Select * FROM TBL "
  MsgBox sql1 = sql2
  MsgBox IsSameSQL(sql1, sql2)
End Sub

so try
isSameSQL(Me.List0.RowSource, oldRowSource) then...

 

[davyre]

ah, didn't think comparing two strings would be this complicated. Thanks for the help!

 

[MajP]

If that did work, then it may simply be that you have a space before or after one of the strings. Often a simple solution when comparing strings is something like
if trim(ucase(string1)) = trim(ucase(string2)) then