Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Balidek
Starting Member
1 Post |
 Posted - 2002-09-04 : 19:22:25
|
| Hi, I created the following query in MSAccess 2000:SELECT Incident.IncidentID, Incident.SeverityID, IncidentLog.IncidentLogDateFROM Incident, IncidentLogWHERE (((Incident.IncidentID)=[IncidentLog].[IncidentID]) AND (IncidentLog.IncidentLogDate)=#9/3/2002#))ORDER BY Incident.IncidentID;Copied into a VB6 application as:strSQL = "SELECT Incident.IncidentID, Incident.SeverityID, IncidentLog.IncidentLogDate "strSQL = strSQL & "FROM Incident, IncidentLog "strSQL = strSQL & "WHERE (((Incident.IncidentID)=[IncidentLog].[IncidentID]) AND((IncidentLog.IncidentLogDate)=" & "#" & datDay & "#" & ") "strSQL = strSQL & "ORDER BY Incident.IncidentID"where datDay is =3D to 9/3/2002.Running the SQL in a query in Access gives me 29 records.Running the SQL in VB gives me 6 records. The first record returned is the same 5th record returned in Access, and the last record returned has a duplicate IncidentID number as the record previous to it.Can anyone tell me why this is. I've been trying to figure this out for a few days now.Thanks, Darren.darren@stemcell.com |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-09-05 : 12:29:29
|
| don't know for definite....but selecting records based on dates....is (in SQL Server) problematic in thatthe date format matters....ie....is "03/09/2002" the 3rd of Sept or the 9th of March....and VB/ADO calling Access2000 may have a different default dateformat compared to raw Access2000....(a forum search for DATEFORMAT will bring up a few hits explaining more)....(note too, that how a date is displayed is seperate from how it is saved....all dates get saved one way, but can be display many different ways)that might explain the difference in the number of records....the duplicate record....may be legitimate in that the join may generate 2 records....using the output data....and working backwards....break the SQL statement into smaller parts and check what each part returns....you may be surprised. |
 |
|
|
ksw
Starting Member
24 Posts |
Posted - 2002-09-09 : 18:59:57
|
| Access (97 at least) is notorious for throwing in extra characters into it's sql statements. Try this instead and see if it makes a difference.strSQL = "SELECT Incident.IncidentID, Incident.SeverityID, IncidentLog.IncidentLogDate " strSQL = strSQL & "FROM Incident, IncidentLog " strSQL = strSQL & "WHERE Incident.IncidentID = IncidentLog.IncidentID AND IncidentLog.IncidentLogDate = #" & datDay & "# " strSQL = strSQL & "ORDER BY Incident.IncidentID" I think you were missing a ) right before the ORDER BY clause.--KSW |
|
|
|
|
|
|
|
|
SQL records returned error.