Strange RecordSet Field Value RRS feed

  • Question

  • Hi, I have a problem with SQL server and VBA.

    I have a particular query that I report here in part:

    select '  ' + rtrim([MachineName])
    from [SAFETY_MACHINES] as x2
    where [GroupNr] = x.[GroupNr]
    ORDER BY [GroupNr]
    FOR XML PATH('')
    ), 1, 1, '') as MachineList,

    There are a lot of other field in my select, but for this field I do in VBA:

    Machines_List = RecordSet.Field("MachineList").value

    Using the SQL Manager Studio at the query I get the correct value " DJ.P.LC02-01  DJ.P.LC02-02  DJ.P.LC02-03  DJ.P.LC02-04", but in VBA I get something like :

    String(2,0) & "d" & vbNullChar & "GEN_OP_OUT_DB_SFTY_GrpN8Sts                    "

    What is wrong in my query or VBA script ?

    Thank you


    Monday, February 26, 2018 9:31 AM

All replies

  • This is hard to say, except that it should be

       Machines_List = RecordSet.Fields("MachineList").value

    notice the plural-s in Fields. But I assume that this is a typo in your post, because this would give you a compilation error.

    Questions: Have you set this to be a pass-through query? Do you get correct results in other fields of the query? Do you get the correct number of records returned?

    Matthias Kläy, Kläy Computing AG

    Monday, February 26, 2018 1:46 PM