[RESOLVED] selecting data from mdb

Hi there,

i have a problen selecting data from an Access Database.

i am using Microsoft DAO 3.6 Object Library

i have the followin tables:

testA (id number, vctext text)
1,"a"
4,"b"

testB(id number, vctext text)
1,"a"
2,"b"
1,"a "
2,"b "


my code is:

Private Sub Form_Load()
Dim sSql As String
Dim gdbloc As Database

DBEngine.SetOption dbImplicitCommitSync, "YES"
Set gdbloc = OpenDatabase("c:\test.mdb", False, False, "")

sSql = "select testa.id as item_no, testb.vctext as DESC_1TB from "
sSql = sSql & "testA left join testB on (testa.vctext = testb.vctext) "
sSql = sSql & " order by testa.id "

Set rs = gdbloc.OpenRecordset(sSql, dbOpenSnapshot)
While Not rs.EOF
MsgBox (rs!item_no & " " & rs!DESC_1TB)
rs.MoveNext
Wend
rs.Close
End Sub


and i get 4 Messageboxes saying:
"1 a"
"1 a "
"4 b"
"4 b "

I expected only 2!

Can someone help?

Best Regards

Lars

I think it's the Left Join that may be the problem. How about just using a Where clause instead of the Join?

Thanks doogle,

that might be, but i have tested the Query direct in my MDB and it works fine.

The code i privides just explains the problem. The original code is in a ERP-System i have the Source-code from (and the problem might there be a several times.....)

Lars

Hi Doogle,

i tested with :

sSql = "select testa.id as item_no, testb.vctext as DESC_1TB from "
sSql = sSql & "testA , testB "
sSql = sSql & "where testa.vctext = testb.vctext "
sSql = sSql & "order by testa.id "

but i get the same 4 message-Boxes.

it seems that the trailing spaces in table testb are ignored.....

Lars

Ah, I suggest you put a breakpoint on, just before you execute the query and then when that triggers, check that the Tables actually contain what you are expecting.

I've seen this sort of problem happen (works in Access but doesn't via SQL) when one or more of the variables / column names are reserved words in VB, which is another thing to look out for.

.... so i tested again what i tested yesterday - the query in the mdb -
i get the wrong result ...... (shi....)

sorry i have to test further and will post again when i have fixed the problem or identified it to have only to be in VB6.

Thank you

Lars

Probably related to ANSI 92 SQL compliance which requires compares to pad items to the same length.

INF: How SQL Server Compares Strings with Trailing Spaces

And yes I realize we're talking about a Jet database here.

Try:

Code:

---

testa.vctext = testb.vctext AND Len(testa.vctext) = Len(testb.vctext)

---

Thank you dilettante for that WorkAround!

I think i have to prevent users from entering data with trailing spaces.

Lars