sql statement and vb

[James Noel]

I configure a sql statement in access to get the data that I need and everything is exactly like i want it. However, when I copy that sql string to visual basic and use the cnn.execute sql command I dont get that same data back as I did when I ran it in access? I do not understand why ( I did another command and copied that one and it returns the same data as it does in access but all others I try doesnt) Any help or suggestions would be great. Below are what each return

in access
id name open closed
1 tom 2 1
2 bill 1 0


in visaul basic
id name open closed
1 tom 3 1

Sorry for reposting but hit wrong key and post too early.

[Memnoch1207]

what is the sql statement?

[James Noel]

SELECT tblReportData.DepartmentID AS DEPARTMENT, tblDepartment.Name AS NAME, Count(tblReportData.DepartmentID) AS REPORTS, Count(tblReportData.DateClosed) AS CLOSED INTO tbltmpReport
FROM tblDepartment INNER JOIN tblReportData ON tblDepartment.DepartmentID = tblReportData.DepartmentID
WHERE (((tblReportData.DateOpened) Between #1/1/2002# AND #10/31/2002#) AND ((tblReportData.ParentID)=1) AND ((tblReportData.DivisionID)=1) AND ((tblReportData.InputReportType)=7))
GROUP BY tblReportData.DepartmentID, tblDepartment.Name;

and actually in access it reports this

Department Name Reports Closed
1 Receiving 2 0
2 Shipping 1 0

but in visual basic using with sql=above string

cnn.execute sql
sql = "SELECT Name, Reports, Closed FROM tbltmpReport"
rs.open sql, cnn, adOpenKeyset, adLockReadOnly, adCmdText

that returns

Name Reports Closed
Receiving 3 0

Any help would be greatly appreciated!

[James Stanich]

sql = "SELECT Name, Sum(Reports) as RPTS, Sum(Closed) as CSD FROM tbltmpReport"

[James Noel]

That is not going to fix the problem. When I put the code into vb for the sql string it only returns the one recordset and puts that into tbltmpReport when in access it returns 2 records as it should. I am not sure why the string works in access just fine but the exact string in vb does not return the correct stuff. Any help is greatly appreciated.

[James Stanich]

Try this :

sql = "SELECT Name, Sum(Reports) as RPTS, Sum(Closed) as CSD FROM tbltmpReport Group By Name"

Not sure if it will work though....