snaged46 0 Newbie Poster

Hello everyone,

This is my first post on this board, I have a problem that I cant find any answers for on the web. I have a Private sub on a form, and I have sql code that draws the information that I want, but does not work when I try to set up a definition because I get the run Time error 3075. How Come? It gives me back the line of SQL code that is in the WHERE clause, [TableStudyHallHoursDetail].[Name] In (SELECT [Name] FROM [TableStudyHallHoursDetail] As Tmp GROUP BY [Name],[Date],[InTime] HAVING Count(*)>1 [Name]='" & Me!TextName & "' And [Date] ='" & Format(Me.TextDate, "\#mm\/dd\/yy\#") & "' And [InTime] ='" & Me!TextInTime & "')

Here is my code in VBA:

Private Sub TextInTime_AfterUpdate()
Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Dim qdf As QueryDef


Set db = CurrentDb()
strSQL = "SELECT TableStudyHallHoursDetail.[Name], TableStudyHallHoursDetail.[Date], TableStudyHallHoursDetail.[InTime], TableStudyHallHoursDetail.[HourID], TableStudyHallHoursDetail.[OutTime], TableStudyHallHoursDetail.[AuditTime]" & _
       "FROM [TableStudyHallHoursDetail]" & _
       "WHERE [TableStudyHallHoursDetail].[Name] In (SELECT [Name] FROM [TableStudyHallHoursDetail] As Tmp GROUP BY [Name],[Date],[InTime] HAVING Count(*)>1  [Name]='" & Me!TextName & "' And [Date] ='" & Format(Me.TextDate, "\#mm\/dd\/yy\#") & "' And [InTime] ='" & Me!TextInTime & "')" & _
       "ORDER BY TableStudyHallHoursDetail.Name, TableStudyHallHoursDetail.Date, TableStudyHallHoursDetail.InTime;"


Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
With db
Set qdf = .CreateQueryDef("tmpDuplicates", sSQL)
DoCmd.OpenQuery "tmpDuplicates"
.QueryDefs.Delete "tmpDuplicates"
End With
db.Close
qdf.Close


End Sub

Once, I ran a debug.printSQL statement and it came back as
SELECT TableStudyHallHoursDetail.[Name], TableStudyHallHoursDetail.[Date], TableStudyHallHoursDetail.[InTime], TableStudyHallHoursDetail.[HourID], TableStudyHallHoursDetail.[OutTime], TableStudyHallHoursDetail.[AuditTime]FROM TableStudyHallHoursDetailWHERE (((TableStudyHallHoursDetail.Name) In (SELECT [Name] FROM [TableStudyHallHoursDetail] As Tmp GROUP BY [Name],[Date],[InTime] HAVING Count(*)>1 [Name]='Smith, Bill' And [Date] =#9/27/2006# And [InTime] =6:00:00 PM)))ORDER BY TableStudyHallHoursDetail.Name, TableStudyHallHoursDetail.Date, TableStudyHallHoursDetail.InTime;

So it seems like the information is being drawn from the table! ARRRGH

Any help would be greatly appreciated!
Thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.