Help with VBA Built Query

[pklocke]

I have the following code, that is generating an error 3219, Invalid Operation, at the line that is BOLD. The value of strSQL at that point is:

SELECT [LogTable].* INTO [Current Changes] FROM [LogTable] WHERE ((([LogTable].Timestamp)=#06-25-2010#));

Any idea what this is trying to tell me? I think everything "looks" right to me...but I am kind of a noob...thx!

pklocke

------------------------------------------------------------------
Sub testPrintLog()
Dim returncode As Long
returncode = PrintLog(Now(), "Current Changes")
MsgBox "Total Records: " & returncode

End Sub

------------------------------------------------------------------
Public Function PrintLog(logdate As Date, CurrentLogTable As String) As Long
Dim strSQL, LogQueryName As String
LogQueryName = "LQ01-Todays Log Query"
strSQL = "SELECT [LogTable].* INTO [" & CurrentLogTable & "] FROM [LogTable] WHERE ((([LogTable].Timestamp)=" & Format(logdate, "\#mm-dd-yyyy\#") & "));"
On Error Resume Next

Set dbs = CurrentDb
dbs.QueryDefs.Delete LogQueryName

On Error GoTo ErrorHandler
Set qdf = dbs.CreateQueryDef(Name:=LogQueryName, sqltext:=strSQL)
Debug.Print strSQL
Set rst = dbs.OpenRecordset(Name:=strSQL)

With rst
.MoveFirst
.MoveLast
PrintLog = .RecordCount
End With

ErrorHandlerExit:
Exit Function
ErrorHandler:
If Err.Number = 3021 Then
PrintLog = 0
Resume ErrorHandlerExit
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Function

[OBP]

Dates are normally mm/dd/yyyy, not mm-dd-yyyy, have you tried that?

[pklocke]

I eventually just tried a different approach, and this worked. DOesn't appear teh date format was the issue. I had read it might no be possible to do a maketable using that other approach, but I am not sure why it would make a difference "how" I did it...but guess it does. Here is what I did, instead:

Public Function PrintLog(logdate As Date, CurrentLogTable As String) As Long
Dim strSQL, LogQueryName As String
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
strSQL = "SELECT * INTO [" & CurrentLogTable & "] FROM [LogTable] WHERE ((([LogTable].Timestamp)=" & Format(logdate, "\#mm-dd-yyyy\#") & "));"
On Error Resume Next
Set dbs = CurrentDb

DeleteTable CurrentLogTable

On Error GoTo ErrorHandler
Set qdf = dbs.CreateQueryDef("", strSQL)

qdf.Execute

ExitHere:
Set qdf = Nothing
dbs.Close
Set dbs = Nothing
Application.RefreshDatabaseWindow
Exit Function

ErrorHandler:
If Err.Number = 3021 Then
PrintLog = 0
Debug.Print "3021 err"
Resume ExitHere
Else
MsgBox "Code Section: PrintLog. Error: " & Err.Number & ":" & Err.Description
Resume ExitHere
End If
End Function