malformed GUID in query expression

[irethedo]

Not sure what I am doing wrong here...

The following code gives a malformed GUID in query expression message:

    Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)     
    strSql = "Select * from PC_SP_Line_tbl order by SP,[Line Number]"

But have no problems when I place this in a query SQL as:

Select * from PC_SP_Line_tbl order by SP,[Line Number]

What am I doing wrong?

Thanks

 

[dhookom]

Are you setting the value of strSQL after you set rs as your code suggests?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[irethedo]

Thanks Duane-

I am not setting the value of strSQL after I set rs...

I was playing around with this and noticed that if I change the code from

 Select * from PC_SP_Line_tbl order by SP,[Line Number]

to

Select * from PC_SP_Line_tbl order by SP"

then I no longer get the error message, but I want this to be sorted by SP and then Line Number....

So then, I replaced [Line Number] with 'Line Number and this does not give an error message but this is not sorting the table by the fields SP and Line Number like a query does.

The Query with this SQL:

SELECT *
FROM PC_SP_Line_tbl
ORDER BY SP, [Line Number];

Creates this sorted order:

[pre]PC SP Qty MySum PreUsed Line Number
019933 8 8 5
019933 4 3 6
019935 4 3 20
019933 8 7 27
019935 2 1 35
008117 -6 8 27
008117 8 8 3[/pre]1

But after the following lines of code:

      strSql = "Select * from SameOrd_tbl order by SPNote"
    Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)     '  --- match up records that do not share ine numbers
    strSql = "Select * from PC_SP_Line_tbl order by SP, 'Line Number'"
    Set rsx = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
    If rs.RecordCount > 0 Then      ' if this is an empty table then don't bother...
       If rsx.RecordCount > 0 Then      ' if this is an empty table then don't bother...
          rs.MoveFirst
          Do While Not rs.EOF
             rsx.MoveFirst
             Do While Not rsx.EOF

the Line Number of the record after the rsx.MoveFirst is Line "6" instead of '5"

Something is not right with this...

thanks

 

[Andrzejek]

You have a table [tt]SameOrd_tbl[/tt], and another table [tt]PC_SP_Line_tbl[/tt], and looks like you want to "match up records that do not share line numbers" and 'do something' with/to those records.. You want to "match up records" on what field?

Looks to me that you do not need 2 record sets, you just need one record set with those 2 tables joined.

Have fun.

---- Andy

There is a great need for a sarcasm font.