I'm still receiving the error 3075. Here's the code again. I added
spaces where I thought they were needed. Also, I wasn't ignoring your
advice of the SELECT *. I just wanted to get this working and then work
through it. I initially tried it with all fields listed but then scaled
it down to the SELECT * statement to better understand the syntax. Also
I think the order is correct as suggested by John Vinson.
Private Sub cmdArchiveData_Click()
Dim mydb As Database
Dim qdf As QueryDef
Dim strQryName As String
Dim strArchiveToDB As String
Dim ArchiveDte As String
Dim strSQL As String
Set mydb = CurrentDb
ArchiveDte = Me.UFArchiveDate.Value
strArchiveToDB = UFArchiveTo.Value
strSQL = "INSERT INTO T0100_CMRMain ( CMRRecordID, Type, " & _
"DateCompleted, [Cty#], WorkerID, [CaseRecoup#], [Order], " & _
"PHASTransDate, PHCDCollDate, [CNLCheck#], TotCollAmt, " & _
"[From], [To], [F/CRsnCde], MisappliedReasonCode, OriginalPaySource,
" & _
"TPN, RTIPPostSETS, [Type of F/C], [RTIP Batch Number]," & _
"[Rtip Amount], DidSetsUpdateCorrectly, [Second Day RTIP Batch
Number], " & _
"[Second Day Rtip Batch Amount], [Second Day Approved By], " & _
"[Check Pull Needed], [Check Number that Was Pulled], " & _
"[Day Two Comments], FeeAdjustment, [SetsReceipt#], "
strSQL = strSQL & "[M/CReasonCode], [M/C Payee],
ManualCheckAmountofCheck, " & _
"Rejected, [Reason For Rejection], [Recoupment Needed], Comments, " &
_
"[Pending Approval], Approvedby, [Approved Yes Or No], [Date
Approved]) " & _
" IN 'C:\BackupDB.mdb' " & _
"SELECT T0100_CMRMain.* " & _
"WHERE T0100_CMRMain.DateCompleted<= " & _
Format(ArchiveDte, "\#mm\/dd\/yyyy\#")
Set qdf = mydb.CreateQueryDef("qryBackupData", strSQL)
strQryName = "qryBackupData"
DoCmd.RunSQL strQryName
mydb.QueryDefs.Delete qdf.Name
Thanks for your help.
On Dec 18, 6:36 pm, "Douglas J. Steele"
You're not putting spaces between your concatenated lines.
" IN 'C:\BackupDB.mdb' " & _
"SELECT T0100_CMRMain.*" & _
"WHERE T0100_CMRMain.DateCompleted<=" & _
Format(ArchiveDte, "\#mm\/dd\/yyyy\#")
There's no space between the asterisk and the keyword WHERE.
(I see you chose to ignore my advice about not using SELECT *!)
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)
message
Doug:
Thanks for the response. I used the syntax you provided and I get
the
following error:
Syntax error in query expression
'T0100_CMDMain.*WHERE T0100_CMRMain.DateCompleted<=#11/1/2006#'
When I Debug it I go to:
Set qdf = mydb.CreateQueryDef("qryBackupData", strSQL)
Here's the updated code for the button Click event:
Private Sub cmdArchiveData_Click()
Dim mydb As Database
Dim qdf As QueryDef
Dim strQryName As String
Dim strArchiveToDB As String
Dim ArchiveDte As String
Dim strSQL As String
Set mydb = CurrentDb
ArchiveDte = Me.UFArchiveDate.Value
strArchiveToDB = UFArchiveTo.Value
strSQL = "INSERT INTO T0100_CMRMain ( CMRRecordID, Type, " & _
"DateCompleted, [Cty#], WorkerID, [CaseRecoup#], [Order], " & _
"PHASTransDate, PHCDCollDate, [CNLCheck#], TotCollAmt, " & _
"[From], [To], [F/CRsnCde], MisappliedReasonCode,
OriginalPaySource,
" & _
"TPN, RTIPPostSETS, [Type of F/C], [RTIP Batch Number]," & _
"[Rtip Amount], DidSetsUpdateCorrectly, [Second Day RTIP Batch
Number], " & _
"[Second Day Rtip Batch Amount], [Second Day Approved By], " & _
"[Check Pull Needed], [Check Number that Was Pulled], " & _
"[Day Two Comments], FeeAdjustment, [SetsReceipt#], "
strSQL = strSQL & "[M/CReasonCode], [M/C Payee],
ManualCheckAmountofCheck, " & _
"Rejected, [Reason For Rejection], [Recoupment Needed], Comments, "
&
_
"[Pending Approval], Approvedby, [Approved Yes Or No], [Date
Approved]) " & _
" IN 'C:\BackupDB.mdb' " & _
"SELECT T0100_CMRMain.*" & _
"WHERE T0100_CMRMain.DateCompleted<=" & _
Format(ArchiveDte, "\#mm\/dd\/yyyy\#")
Set qdf = mydb.CreateQueryDef("qryBackupData", strSQL)
strQryName = "qryBackupData"
DoCmd.RunSQL strQryName
mydb.QueryDefs.Delete qdf.Name
End Sub
Thanks for any further help you can provide.
HRE
Douglas J. Steele wrote:
It needs to be #11/1/2006#, but your SQL is incorrect.
The syntax for INSERT INTO is
INSERT INTO target [IN externaldatabase] [(field1[, field2[,
...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression
so you probably want:
strSQL = "INSERT INTO T0100_CMRMain ( CMRRecordID, Type, " & _
"DateCompleted, [Cty#], WorkerID, [CaseRecoup#], [Order], " & _
"PHASTransDate, PHCDCollDate, [CNLCheck#], TotCollAmt, " & _
"[From], [To], [F/CRsnCde], MisappliedReasonCode,
OriginalPaySource,
"
& _
"TPN, RTIPPostSETS, [Type of F/C], [RTIP Batch Number]," & _
"[Rtip Amount], DidSetsUpdateCorrectly, [Second Day RTIP Batch
Number],
"
& _
"[Second Day Rtip Batch Amount], [Second Day Approved By], " & _
"[Check Pull Needed], [Check Number that Was Pulled], " & _
"[Day Two Comments], FeeAdjustment, [SetsReceipt#], "
strSQL = strSQL & "[M/CReasonCode], [M/C Payee],
ManualCheckAmountofCheck,
" & _
"Rejected, [Reason For Rejection], [Recoupment Needed], Comments,
"
& _
"[Pending Approval], Approvedby, [Approved Yes Or No], [Date
Approved])
"
& _
" IN 'C:\BackupDB.mdb' " & _
"SELECT T0100_CMRMain.*" _
"WHERE T0100_CMRMain.DateCompleted<=" & _
Format(ArchiveDte, "\#mm\/dd\/yyyy\#")
although I'd list out the fields you're selected, rather than
SELECT
T0100_CMRMain.*. As far as I'm concerned, you should never use
SELECT
*
in a
production application. Note that there's a limit of 10 line
continuations,
so I split the concatenation.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
I am trying to run an Append query via code. I want to allow the
user
to input the date into a text box and use that textbox as the
value
for
the Where clause. The specific error message I receive is this:
Syntax error in query expression
'T0100_CMDMain.*WHERE(((T0100_CMRMain.DateCompleted)<=11/1/2006))'.
My code:
Private Sub cmdArchiveData_Click()
Dim mydb As Database
Dim qdf As QueryDef
Dim strQryName As String
Dim strArchiveToDB As String
Dim ArchiveDte As String
Dim strSQL As String
Set mydb = CurrentDb
ArchiveDte = Me.UFArchiveDate.Value
strArchiveToDB = UFArchiveTo.Value
MsgBox ArchiveDte
MsgBox strArchiveToDB
strSQL = "SELECT T0100_CMRMain.*" _
& "WHERE (((T0100_CMRMain.DateCompleted)<=" & ArchiveDte & "));"
_
& "INSERT INTO T0100_CMRMain ( CMRRecordID, Type, DateCompleted,
[Cty#], WorkerID, [CaseRecoup#], [Order], PHASTransDate,
PHCDCollDate,"
_
& "[CNLCheck#], TotCollAmt, [From], [To], [F/CRsnCde],
MisappliedReasonCode, OriginalPaySource, TPN, RTIPPostSETS, [Type
of
F/C], [RTIP Batch Number]," _
& "[Rtip Amount], DidSetsUpdateCorrectly, [Second Day RTIP Batch
Number], [Second Day Rtip Batch Amount], [Second Day Approved
By],
[Check Pull Needed], " _
& "[Check Number that Was Pulled], [Day Two Comments],
FeeAdjustment,
[SetsReceipt#], [M/CReasonCode], [M/C Payee],
ManualCheckAmountofCheck," _
& "Rejected, [Reason For Rejection], [Recoupment Needed],
Comments,
[Pending Approval], Approvedby, [Approved Yes Or No], [Date
Approved] )
IN 'C:\BackupDB.mdb'"
Set qdf = mydb.CreateQueryDef("qryBackupData", strSQL)
strQryName = "qryBackupData"
DoCmd.RunSQL strQryName
mydb.QueryDefs.Delete qdf.Name
Any help would be appreciated.
Thanks- Hide quoted text -- Show quoted text -
