CODEWITHSUNDEEP
sqlsql-servert-sqlms-access
BIBD
BIBD

Reputation: 15384

How can I match against a special character in an MS Access query against an MS SQL DB?

I'm trying to run a match against a funny string in an MS SQL database.

The funny string looks like "AA-9999", but I suspect that the '-' is not a standard '-'.

Sub mySub()

Dim rsExample As Recordset
Dim rsExample2 As Recordset
Dim db As Database
Dim SearchCriteria As String

    Set db = CurrentDb

    Set rsExample2 = db.OpenRecordset( _
                                    "select * from exTable2 order by dumbID", _
                                    dbOpenDynaset, _
                                    dbSeeChanges)


    Set rsExample = db.OpenRecordset( _
                                    "select * from exTable order by dumbID", _
                                    dbOpenDynaset, _
                                    dbSeeChanges)


    rsExample.MoveFirst

    while not rsExample2.eof       

        SearchCriteria = rsExample2("dumbID")

''        SearchCriteria = "dumbID = ""AA-9999"""

        rsExample.FindNext (SearchCriteria)

        If rsExample.NoMatch Then
            MsgBox ("Missing Record")
            rsExample.MoveFirst
        Else
            '' do stuff to record
        End If
    wend
End Sub

I've queried against it with the following SQL, and it seems to indicate that it's a standard '-'

SELECT ASCII (substring ([dumbID ],3,1)) as dumbIDascii
FROM exTable
where goodID like '1234'

gives me

dumbIDascii
-----------
45

How can I tell what that '-' really is, or have I missed something else?

Upvotes: 0

Views: 489

Answers (2)

RBarryYoung
RBarryYoung

Reputation: 56725

If your "-" is non-standard then its in UNICODE, not ASCII. Try using the UNICODE() function in your SELECT statement.

Upvotes: 1

BIBD
BIBD

Reputation: 15384

Further playing has shown that it is finding the record, without the outer loop. When the outer loop is added, it again stops finding the record.

Modifying the sub by moving where rsExample.MoveFirst is called as follows:

Sub mySub()

Dim rsExample As Recordset
Dim rsExample2 As Recordset
Dim db As Database
Dim SearchCriteria As String

    Set db = CurrentDb

    Set rsExample2 = db.OpenRecordset( _
                                    "select * from exTable2 order by dumbID", _
                                    dbOpenDynaset, _
                                    dbSeeChanges)


    Set rsExample = db.OpenRecordset( _
                                    "select * from exTable order by dumbID", _
                                    dbOpenDynaset, _
                                    dbSeeChanges)

''  <--- from here 

    while not rsExample2.eof       

        rsExample.MoveFirst   '' <--- to here

        SearchCriteria = rsExample2("dumbID")

''        SearchCriteria = "dumbID = ""AA-9999"""

        rsExample.FindNext (SearchCriteria)

        If rsExample.NoMatch Then
            MsgBox ("Missing Record")
            rsExample.MoveFirst
        Else
            '' do stuff to record
        End If
    wend
End Sub

has solved the problem.

I suspect that either rsExample is not sorting as I expect it to, or that earlier rsExample.FindNext calls are jumping to a point further on in the RecordSet than the value(s) I'm looking for. New rule to add to my collection Always do a MoveFirst, before you do a FindNext (unless you are looking for that exact same thing that you just use FindNext to search for).

Upvotes: 0

Related Questions