Getting Syntax error (missing operator) in query expression

[nissan240zx]

Hello All,
I am running a VB code, which was working great till Monday.
The scenario is this Vb code help an Access DB feeds its data from couple of mainframe tables.
Suddenly since monday I am getting this error below.

Syntax error (missing operator) in query expression 'cat_group_id =9 and ebcdic_name > = and ebdic_name < = 199229218'.

Run-time error '-2147217900 (80040e14)'

Here is the portion of the code in question (or atleast that what I think).

Private Sub AssignAccounts()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This function assigns the accounts based on the alpha ranges
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim rstBlocks As ADODB.Recordset
    Dim strQuery As String
    Dim strUpdateQry As String
    
    
   
    strQuery = "select BlockStartEBCDIC, BlockEndEBCDIC, CollectorId, CatGroupId from blocks order by catgroupId, CollectorId"
    Set rstBlocks = New ADODB.Recordset
    rstBlocks.Open strQuery, conn, adOpenDynamic
    If rstBlocks.EOF = False Then
        rstBlocks.MoveFirst
    End If
    Do While rstBlocks.EOF = False
       
        strQuery = "update  PaysysTag set COLLID = '" & rstBlocks.Fields("CollectorId") & _
        "' where cat_group_id = " & rstBlocks.Fields("CatGroupId") & _
        " and ebcdic_name >= " & rstBlocks.Fields("BlockStartEBCDIC") & _
        " and ebcdic_name <= " & rstBlocks.Fields("BlockEndEBCDIC")
        conn.Execute strQuery
        
        rstBlocks.MoveNext
    Loop
    rstBlocks.Close
    Set rstBlocks = Nothing
End Sub

I am not sure where to start trouble shooting as the code was wrking with no issue for over 2 years..
Description of the tables used in the query.
Thsi is a table that stores information of collections team.
Blocks - Is a setup table in Access which defines the collector's name, ids collector code and their starting and ending collecting teams code.
For example collector john doe, has collecotr code JDO and his start collecting teams code (EBDCIC_NAME) could be AAA and ending could be ASF and so on.
PaysysTag is the table that gets the imported data from mainframe.

My doubt it that some thing to do with data coming in not matching with informaiton on the Blocks table.

Let me know if any other information is required to help me out.
Thanks in advance.
Nissan



A good programmer is someone who looks both ways before crossing a one-way street. - Doug Linder

 

[Sheco]

[tt]'cat_group_id =9 and ebcdic_name > [highlight][red]Something Is Missing Here[/red][/highlight] = and ebdic_name < = 199229218'[/tt]

 

[Sheco]

strQuery = "update  PaysysTag set COLLID = '" & rstBlocks.Fields("CollectorId") & _
        "' where cat_group_id = " & rstBlocks.Fields("CatGroupId") & _
        " and ebcdic_name >= " & [highlight][red]rstBlocks.Fields("BlockStartEBCDIC")[/red][/highlight] & _
        " and ebcdic_name <= " & rstBlocks.Fields("BlockEndEBCDIC")

Looks like the recordset field named BlockStartEBCDIC is empty.

 

[Golom]

It looks like rstBlocks.Fields("BlockStartEBCDIC") and/or rstBlocks.Fields("BlockEndEBCDIC") may contain NULLs. See if it goes away with

With rstBlocks.Fields
Do While rstBlocks.EOF = False
   [COLOR=blue]If NOT IsNull(![BlockStartEBCDIC]) AND _
      NOT IsNull(![BlockEndEBCDIC]) Then[/color]

        strQuery = "update  PaysysTag set COLLID = '" & ![CollectorId] & _
        "' where cat_group_id = " & ![CatGroupId] & _
        " and ebcdic_name >= " & ![BlockStartEBCDIC] & _
        " and ebcdic_name <= " & ![BlockEndEBCDIC]
        conn.Execute strQuery

    [COLOR=blue]End If[/color]
    rstBlocks.MoveNext
Loop
End With

 

[nissan240zx]

Let me try what Golom said and give an update

A good programmer is someone who looks both ways before crossing a one-way street. - Doug Linder

 

[nissan240zx]

I just tried the IF statements and I am getting a message " Compile error: Invalid or unqualified reference" and the word (![BlockStartEBCDIC]) is highlighted.
Am I missing any steps. Do I need to declare anything.


A good programmer is someone who looks both ways before crossing a one-way street. - Doug Linder

 

[Sheco]

You could also check the database by running a SQL command to count the number of rows with Null or empty values for the BlockStartEBCDIC field.

Something like this:
[tt]SELECT Count(*)
FROM blocks
WHERE BlockStartEBCDIC Is Null
OR BlockStartEBCDIC = ''[/tt]

If it comes back with anything other than 0 then the problem is bad data...