VB6.0 Datagrid Question

[Velocis]

I have 2 separate tables that need to be joined together. I'm trying to display certain fields from both tables by joining them with a matching field(same values but different column name) The user enters their employee id, then I need to return the other field values associated with that specific id# in a datagrid.

So far I'm able to return the other field values from one of my tables according to the id# entered. This is version1 of my query, which works

'my connection stuff is here
rs.open "SELECT swpNum, swpInfo, swpType FROM tblSwipes WHERE (swpNum='"& sNum &"') and swpType=1 order by etc...
set datagrid.datasource=rs

The datagrid displays the 3 fields associated with the #(sNum) entered by the user. But I'm having trouble when I try to rewrite the query to join tblSwipes with tblEmp to get fields returned from that table as well. Here's what I have for the new query

'connection stuff goes here
rs.open "SELECT tblEmp.lastName,tblEmp.firstName,  tblEmp.payNum, tblSwipes.swpNum, tblSwipes.swpType  
FROM tblEmp INNER JOIN tblSwipes 
ON tblEmp.payNum=tblSwipes.swpNum 
WHERE (swpNum='"& sNum &"') and swpType=1 
order by etc...

set datagrid.datasource =rs

Nothing is shown in the datagrid using this query so I'm guessing the problem is with my sql statement. any tips or suggestions on another way of doing this would be greatly appreciated.

[vb5prgrmr]

Use the query design tool in M$ Access to design this query. Then, once you have it returning the information you want, goto sql view and copy it from there to paste into your vb6 program, and from what I can tell, you are almost there but I do have a question... Why are you selecting paynum and swpnum if you are using these to join on? I ask because the way you have your field selection set up, you will have two columns that equal each other. Don't you need only one?

Okay, I have more questions. If swpNum is a number, then why are you enclosing it in single ticks (') to denote that it is a string?

Good Luck

[Velocis]

Good question, I do only need to join one from each table, so it doesn't make sense for me to include them in the selection. Could that have been my problem?

As for the other question, sNum is my string variable that the user input is being assigned to:

Dim sNum as String
sNum=txtBox.text

Is there another/better way to do that?

[vb5prgrmr]

No I don't think that is your problem. Have another question. So then swpType is a number? Oh yeah, one more. Did you create the query in the M$ Access Query Designer?

Good Luck

[Velocis]

Yes sypType is a number. And I won't be able to try creating a query in access until tomorrow unfortunately, since I don't have it on my home computer.