select distinct not working [moved]

[nyah_RMC]

i tried to run this statement but i got some errors...can someone show me how to do this correctly? i want to filter my employee records showing only the records with equivalent transactions on a given date...i mean i want to suppress my records displaying only those records with transactions that corresponds to a certain date... ??

Dim strGroupSQL As String
Dim strDetailsSQL As String
Dim strShapeSQL As String
Dim cus As String
Dim d As Date
 
   d = Date
   strGroupSQL = "SELECT ID, Name FROM tblContact WHERE ID IN (SELECT DISTINCT tblTransaction.ID FROM tblTransaction)"
   
   strDetailsSQL = "SELECT tblContact.ID, tblContact.Name, tblTransaction.Total, tblTransaction.TransID, tblTransaction.Paid, tblTransaction.Total, "
   strDetailsSQL = strDetailsSQL & "tblTransaction.Total - tblTransaction.Paid AS Balance ,tblTransaction.DueDate, tblTransaction.Settled "
   strDetailsSQL = strDetailsSQL & "FROM tblContact INNER JOIN tblTransaction ON tblContact.ID = tblTransaction.ID WHERE tblTransaction.Settled LIKE 0 "
   
   strShapeSQL = "SHAPE {" & strGroupSQL & "} "
   strShapeSQL = strShapeSQL & "APPEND ({" & strDetailsSQL & "} AS GrpDetails "
   strShapeSQL = strShapeSQL & "RELATE ID to ID) "

[Ecniv]

Originally posted by nyah_RMC
i tried to run this statement but i got some errors...

What errors?

can someone show me how to do this correctly?
i want to filter my employee records showing only the records with equivalent transactions on a given date...
i mean i want to suppress my records displaying only those records with transactions that corresponds to a certain date... ??

I cannot see any filters in the where clause for dates?
Hint: Use hashes around the date.

Dim strGroupSQL As String
Dim strDetailsSQL As String
Dim strShapeSQL As String
Dim cus As String
Dim d As Date
 
   d = Date
   strGroupSQL = "SELECT ID, [b][Name][/b] FROM tblContact WHERE ID IN (SELECT DISTINCT tblTransaction.ID FROM tblTransaction)"
   
   strDetailsSQL = "SELECT tblContact.ID, tblContact.[Name], tblTransaction.[Total], tblTransaction.TransID, tblTransaction.Paid, [i]tblTransaction.[Total][/i], "
   strDetailsSQL = strDetailsSQL & "(tblTransaction.[Total] - tblTransaction.Paid) AS Balance ,tblTransaction.DueDate, tblTransaction.Settled "
   strDetailsSQL = strDetailsSQL & "FROM tblContact INNER JOIN tblTransaction ON [i]tblContact.ID = tblTransaction.ID[/i] WHERE tblTransaction.Settled LIKE 0 "
   
   strShapeSQL = "SHAPE {" & strGroupSQL & "} "
   strShapeSQL = strShapeSQL & "APPEND ({" & strDetailsSQL & "} AS GrpDetails "
   strShapeSQL = strShapeSQL & "RELATE ID to ID) "

The bit in bold may need square brackets (inserted) as Name is a function.
The bit in italics (middle) looks like its a duplicate field?
The bit in italics (bottom) is joining on the wrong id? Possibly?


Vince

[nyah_RMC]

ey sir i already filtered it...BUT my main problem is, records without transactions are still showing...how can i suppress it., showing only those with transactions on a given date??

strGroupSQL = "SELECT ID, Name FROM tblContact WHERE ID IN (SELECT DISTINCT tblTransaction.ID FROM tblTransaction)"
   
   strDetailsSQL = "SELECT tblContact.ID, tblContact.Name, tblTransaction.Total, tblTransaction.TransID, tblTransaction.Paid, tblTransaction.Total, "
   strDetailsSQL = strDetailsSQL & "tblTransaction.Total - tblTransaction.Paid AS Balance ,tblTransaction.DueDate, tblTransaction.Settled "
   strDetailsSQL = strDetailsSQL & "FROM tblContact INNER JOIN tblTransaction ON tblContact.ID = tblTransaction.ID WHERE tblTransaction.Settled = 0 AND tblTransaction.DueDate = #" & d & "#"