Error 3075 Syntax Error

H

HRE

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
 
H

HRE

I also tried the following code:

strSQL = "SELECT T0100_CMRMain.*" _
& "WHERE (((T0100_CMRMain.DateCompleted)<=#11/1/2006#));" _
& "INSERT INTO T0100_CMRMain IN 'C:\BackupDB.mdb'"

And I receive the same error.
 
D

Douglas J. Steele

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)


HRE said:
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
 
H

HRE

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

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)


HRE said:
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
 
J

John Vinson

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))'.

The problem is apparently that you're mixing the positions of clauses.
The order is significant! Try putting the INSERT INTO clause first,
the SELECT clause second, and the WHERE clause last.

John W. Vinson[MVP]
 
D

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 MVP

(no private e-mails, please)


HRE said:
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

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)


HRE said:
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
 
H

HRE

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.

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)



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
Thanks for any further help you can provide.

Douglas said:
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 -
 
D

Douglas J. Steele

For now, comment out the line

mydb.QueryDefs.Delete qdf.Name

Then, once the code runs, open the query in Design mode, switch to the SQL
view, copy the SQL that's there and paste it into your reply.

Also, try running the query. Is the error message any more explicit then?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


HRE said:
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.

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

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
Thanks for any further help you can provide.

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.
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))'.
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 -
 
D

Douglas J. Steele

Of course there should be a FROM clause!

Sorry, the SQL has been undergoing many mutations, and I didn't look at it
as closely as I should have.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


SteveS said:
PMFJI,

In the sub select, shouldn't there be a FROM clause?

This is what HRE posted:

---<snip>---
" IN 'C:\BackupDB.mdb' " & _
"SELECT T0100_CMRMain.* " & _
"WHERE T0100_CMRMain.DateCompleted<= " & _
Format(ArchiveDte, "\#mm\/dd\/yyyy\#")


I would expect it to look like:


---<snip>---
" IN 'C:\BackupDB.mdb' " & _
" SELECT T0100_CMRMain.* " & _

" FROM T0100_CMRMain" & _ '<<<<<<

" WHERE T0100_CMRMain.DateCompleted<= " & _
Format(ArchiveDte, "\#mm\/dd\/yyyy\#")


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Douglas J. Steele said:
For now, comment out the line

mydb.QueryDefs.Delete qdf.Name

Then, once the code runs, open the query in Design mode, switch to the
SQL
view, copy the SQL that's there and paste it into your reply.

Also, try running the query. Is the error message any more explicit then?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


HRE said:
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 -
 
H

HRE

OK I think we've made some progress. However, I am now getting error
3129 Invalid SQL statement; expected "DELETE", "INSERT", "PROCEDURE",
"SELECT", or "UPDATE".

The query gets created and here's the SQL when I display it in design
mode:

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'
SELECT T0100_CMRMain.CMRRecordID, T0100_CMRMain.Type,
T0100_CMRMain.DateCompleted, T0100_CMRMain.[Cty#],
T0100_CMRMain.WorkerID, T0100_CMRMain.[CaseRecoup#],
T0100_CMRMain.Order, T0100_CMRMain.PHASTransDate,
T0100_CMRMain.PHCDCollDate, T0100_CMRMain.[CNLCheck#],
T0100_CMRMain.TotCollAmt, T0100_CMRMain.From, T0100_CMRMain.To,
T0100_CMRMain.[F/CRsnCde], T0100_CMRMain.MisappliedReasonCode,
T0100_CMRMain.OriginalPaySource, T0100_CMRMain.TPN,
T0100_CMRMain.RTIPPostSETS, T0100_CMRMain.[Type of F/C],
T0100_CMRMain.[RTIP Batch Number], T0100_CMRMain.[Rtip Amount],
T0100_CMRMain.DidSetsUpdateCorrectly, T0100_CMRMain.[Second Day RTIP
Batch Number], T0100_CMRMain.[Second Day Rtip Batch Amount],
T0100_CMRMain.[Second Day Approved By], T0100_CMRMain.[Check Pull
Needed], T0100_CMRMain.[Check Number that Was Pulled],
T0100_CMRMain.[Day Two Comments], T0100_CMRMain.FeeAdjustment,
T0100_CMRMain.[SetsReceipt#], T0100_CMRMain.[M/CReasonCode],
T0100_CMRMain.[M/C Payee], T0100_CMRMain.ManualCheckAmountofCheck,
T0100_CMRMain.Rejected, T0100_CMRMain.[Reason For Rejection],
T0100_CMRMain.[Recoupment Needed], T0100_CMRMain.Comments,
T0100_CMRMain.[Pending Approval], T0100_CMRMain.Approvedby,
T0100_CMRMain.[Approved Yes Or No], T0100_CMRMain.[Date Approved]
FROM T0100_CMRMain
WHERE (((T0100_CMRMain.DateCompleted)<=#11/1/2006#));

I can run the query manually and it works fine. Not sure why it has
problems executing in code.

Any help is greatly appreciated.

HRE


For now, comment out the line

mydb.QueryDefs.Delete qdf.Name

Then, once the code runs, open the query in Design mode, switch to the SQL
view, copy the SQL that's there and paste it into your reply.

Also, try running the query. Is the error message any more explicit then?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)



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.
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 -- Hide quoted text -- Show quoted text -
 
D

Douglas J. Steele

It's supposed to be

INSERT INTO T0100_CMRMain IN 'C:\BackupDB.mdb'
( 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] )

Sorry for not noticing that earlier.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


HRE said:
OK I think we've made some progress. However, I am now getting error
3129 Invalid SQL statement; expected "DELETE", "INSERT", "PROCEDURE",
"SELECT", or "UPDATE".

The query gets created and here's the SQL when I display it in design
mode:

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'
SELECT T0100_CMRMain.CMRRecordID, T0100_CMRMain.Type,
T0100_CMRMain.DateCompleted, T0100_CMRMain.[Cty#],
T0100_CMRMain.WorkerID, T0100_CMRMain.[CaseRecoup#],
T0100_CMRMain.Order, T0100_CMRMain.PHASTransDate,
T0100_CMRMain.PHCDCollDate, T0100_CMRMain.[CNLCheck#],
T0100_CMRMain.TotCollAmt, T0100_CMRMain.From, T0100_CMRMain.To,
T0100_CMRMain.[F/CRsnCde], T0100_CMRMain.MisappliedReasonCode,
T0100_CMRMain.OriginalPaySource, T0100_CMRMain.TPN,
T0100_CMRMain.RTIPPostSETS, T0100_CMRMain.[Type of F/C],
T0100_CMRMain.[RTIP Batch Number], T0100_CMRMain.[Rtip Amount],
T0100_CMRMain.DidSetsUpdateCorrectly, T0100_CMRMain.[Second Day RTIP
Batch Number], T0100_CMRMain.[Second Day Rtip Batch Amount],
T0100_CMRMain.[Second Day Approved By], T0100_CMRMain.[Check Pull
Needed], T0100_CMRMain.[Check Number that Was Pulled],
T0100_CMRMain.[Day Two Comments], T0100_CMRMain.FeeAdjustment,
T0100_CMRMain.[SetsReceipt#], T0100_CMRMain.[M/CReasonCode],
T0100_CMRMain.[M/C Payee], T0100_CMRMain.ManualCheckAmountofCheck,
T0100_CMRMain.Rejected, T0100_CMRMain.[Reason For Rejection],
T0100_CMRMain.[Recoupment Needed], T0100_CMRMain.Comments,
T0100_CMRMain.[Pending Approval], T0100_CMRMain.Approvedby,
T0100_CMRMain.[Approved Yes Or No], T0100_CMRMain.[Date Approved]
FROM T0100_CMRMain
WHERE (((T0100_CMRMain.DateCompleted)<=#11/1/2006#));

I can run the query manually and it works fine. Not sure why it has
problems executing in code.

Any help is greatly appreciated.

HRE


For now, comment out the line

mydb.QueryDefs.Delete qdf.Name

Then, once the code runs, open the query in Design mode, switch to the
SQL
view, copy the SQL that's there and paste it into your reply.

Also, try running the query. Is the error message any more explicit then?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

message

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 *!)
messagenews:[email protected]...

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
Thanks for any further help you can provide.

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.
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))'.
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 -- Hide quoted
text -- Show quoted text -
 
H

HRE

I changed the SQL to what you have and now I'm getting Run-time error
3134 Syntax error in INSERT INTO statement.

Here's the SQL from the VBA Editor

strSQL = "INSERT INTO T0100_CMRMain IN 'C:\BackupDB.mdb' (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]) " & _
"SELECT T0100_CMRMain.CMRRecordID, T0100_CMRMain.Type,
T0100_CMRMain.DateCompleted, T0100_CMRMain.[Cty#],
T0100_CMRMain.WorkerID," & _
"T0100_CMRMain.[CaseRecoup#], T0100_CMRMain.Order,
T0100_CMRMain.PHASTransDate, T0100_CMRMain.PHCDCollDate,
T0100_CMRMain.[CNLCheck#]," & _
"T0100_CMRMain.TotCollAmt, T0100_CMRMain.From, T0100_CMRMain.To,
T0100_CMRMain.[F/CRsnCde], T0100_CMRMain.MisappliedReasonCode, " & _
"T0100_CMRMain.OriginalPaySource, T0100_CMRMain.TPN,
T0100_CMRMain.RTIPPostSETS, T0100_CMRMain.[Type of F/C],
T0100_CMRMain.[RTIP Batch Number], " & _
"T0100_CMRMain.[Rtip Amount], T0100_CMRMain.DidSetsUpdateCorrectly,
T0100_CMRMain.[Second Day RTIP Batch Number], T0100_CMRMain.[Second Day
Rtip Batch Amount], " & _
"T0100_CMRMain.[Second Day Approved By], T0100_CMRMain.[Check Pull
Needed], T0100_CMRMain.[Check Number that Was Pulled],
T0100_CMRMain.[Day Two Comments], " & _
"T0100_CMRMain.FeeAdjustment, T0100_CMRMain.[SetsReceipt#],
T0100_CMRMain.[M/CReasonCode], T0100_CMRMain.[M/C Payee], " & _
"T0100_CMRMain.ManualCheckAmountofCheck, T0100_CMRMain.Rejected,
T0100_CMRMain.[Reason For Rejection], T0100_CMRMain.[Recoupment
Needed], " & _
"T0100_CMRMain.Comments, T0100_CMRMain.[Pending Approval],
T0100_CMRMain.Approvedby, T0100_CMRMain.[Approved Yes Or No], " & _
"T0100_CMRMain.[Date Approved] " & _
"FROM T0100_CMRMain" & _
" WHERE T0100_CMRMain.DateCompleted<= " & _
Format(ArchiveDte, "\#mm\/dd\/yy\#")

It's supposed to be

INSERT INTO T0100_CMRMain IN 'C:\BackupDB.mdb'
( 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] )

Sorry for not noticing that earlier.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)



OK I think we've made some progress. However, I am now getting error
3129 Invalid SQL statement; expected "DELETE", "INSERT", "PROCEDURE",
"SELECT", or "UPDATE".
The query gets created and here's the SQL when I display it in design
mode:
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'
SELECT T0100_CMRMain.CMRRecordID, T0100_CMRMain.Type,
T0100_CMRMain.DateCompleted, T0100_CMRMain.[Cty#],
T0100_CMRMain.WorkerID, T0100_CMRMain.[CaseRecoup#],
T0100_CMRMain.Order, T0100_CMRMain.PHASTransDate,
T0100_CMRMain.PHCDCollDate, T0100_CMRMain.[CNLCheck#],
T0100_CMRMain.TotCollAmt, T0100_CMRMain.From, T0100_CMRMain.To,
T0100_CMRMain.[F/CRsnCde], T0100_CMRMain.MisappliedReasonCode,
T0100_CMRMain.OriginalPaySource, T0100_CMRMain.TPN,
T0100_CMRMain.RTIPPostSETS, T0100_CMRMain.[Type of F/C],
T0100_CMRMain.[RTIP Batch Number], T0100_CMRMain.[Rtip Amount],
T0100_CMRMain.DidSetsUpdateCorrectly, T0100_CMRMain.[Second Day RTIP
Batch Number], T0100_CMRMain.[Second Day Rtip Batch Amount],
T0100_CMRMain.[Second Day Approved By], T0100_CMRMain.[Check Pull
Needed], T0100_CMRMain.[Check Number that Was Pulled],
T0100_CMRMain.[Day Two Comments], T0100_CMRMain.FeeAdjustment,
T0100_CMRMain.[SetsReceipt#], T0100_CMRMain.[M/CReasonCode],
T0100_CMRMain.[M/C Payee], T0100_CMRMain.ManualCheckAmountofCheck,
T0100_CMRMain.Rejected, T0100_CMRMain.[Reason For Rejection],
T0100_CMRMain.[Recoupment Needed], T0100_CMRMain.Comments,
T0100_CMRMain.[Pending Approval], T0100_CMRMain.Approvedby,
T0100_CMRMain.[Approved Yes Or No], T0100_CMRMain.[Date Approved]
FROM T0100_CMRMain
WHERE (((T0100_CMRMain.DateCompleted)<=#11/1/2006#));
I can run the query manually and it works fine. Not sure why it has
problems executing in code.
Any help is greatly appreciated.
For now, comment out the line
mydb.QueryDefs.Delete qdf.Name
Then, once the code runs, open the query in Design mode, switch to the
SQL
view, copy the SQL that's there and paste it into your reply.
Also, try running the query. Is the error message any more explicit then?
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)
message
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...

read more »- Hide quoted text -- Show quoted text -
 
D

Douglas J. Steele

Type is a reserved word, and so should have square brackets around it. Check
what Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html to
see whether any other of your names are reserved.

You really should be posting the contents of strSQL, not the VBA code that
generates strSQL. Seeing the SQL string as Access sees it when it tries to
run it is the only way you're going to be able to get to the bottom of this.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I changed the SQL to what you have and now I'm getting Run-time error
3134 Syntax error in INSERT INTO statement.

Here's the SQL from the VBA Editor

strSQL = "INSERT INTO T0100_CMRMain IN 'C:\BackupDB.mdb' (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]) " & _
"SELECT T0100_CMRMain.CMRRecordID, T0100_CMRMain.Type,
T0100_CMRMain.DateCompleted, T0100_CMRMain.[Cty#],
T0100_CMRMain.WorkerID," & _
"T0100_CMRMain.[CaseRecoup#], T0100_CMRMain.Order,
T0100_CMRMain.PHASTransDate, T0100_CMRMain.PHCDCollDate,
T0100_CMRMain.[CNLCheck#]," & _
"T0100_CMRMain.TotCollAmt, T0100_CMRMain.From, T0100_CMRMain.To,
T0100_CMRMain.[F/CRsnCde], T0100_CMRMain.MisappliedReasonCode, " & _
"T0100_CMRMain.OriginalPaySource, T0100_CMRMain.TPN,
T0100_CMRMain.RTIPPostSETS, T0100_CMRMain.[Type of F/C],
T0100_CMRMain.[RTIP Batch Number], " & _
"T0100_CMRMain.[Rtip Amount], T0100_CMRMain.DidSetsUpdateCorrectly,
T0100_CMRMain.[Second Day RTIP Batch Number], T0100_CMRMain.[Second Day
Rtip Batch Amount], " & _
"T0100_CMRMain.[Second Day Approved By], T0100_CMRMain.[Check Pull
Needed], T0100_CMRMain.[Check Number that Was Pulled],
T0100_CMRMain.[Day Two Comments], " & _
"T0100_CMRMain.FeeAdjustment, T0100_CMRMain.[SetsReceipt#],
T0100_CMRMain.[M/CReasonCode], T0100_CMRMain.[M/C Payee], " & _
"T0100_CMRMain.ManualCheckAmountofCheck, T0100_CMRMain.Rejected,
T0100_CMRMain.[Reason For Rejection], T0100_CMRMain.[Recoupment
Needed], " & _
"T0100_CMRMain.Comments, T0100_CMRMain.[Pending Approval],
T0100_CMRMain.Approvedby, T0100_CMRMain.[Approved Yes Or No], " & _
"T0100_CMRMain.[Date Approved] " & _
"FROM T0100_CMRMain" & _
" WHERE T0100_CMRMain.DateCompleted<= " & _
Format(ArchiveDte, "\#mm\/dd\/yy\#")

It's supposed to be

INSERT INTO T0100_CMRMain IN 'C:\BackupDB.mdb'
( 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] )

Sorry for not noticing that earlier.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

message

OK I think we've made some progress. However, I am now getting error
3129 Invalid SQL statement; expected "DELETE", "INSERT", "PROCEDURE",
"SELECT", or "UPDATE".
The query gets created and here's the SQL when I display it in design
mode:
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'
SELECT T0100_CMRMain.CMRRecordID, T0100_CMRMain.Type,
T0100_CMRMain.DateCompleted, T0100_CMRMain.[Cty#],
T0100_CMRMain.WorkerID, T0100_CMRMain.[CaseRecoup#],
T0100_CMRMain.Order, T0100_CMRMain.PHASTransDate,
T0100_CMRMain.PHCDCollDate, T0100_CMRMain.[CNLCheck#],
T0100_CMRMain.TotCollAmt, T0100_CMRMain.From, T0100_CMRMain.To,
T0100_CMRMain.[F/CRsnCde], T0100_CMRMain.MisappliedReasonCode,
T0100_CMRMain.OriginalPaySource, T0100_CMRMain.TPN,
T0100_CMRMain.RTIPPostSETS, T0100_CMRMain.[Type of F/C],
T0100_CMRMain.[RTIP Batch Number], T0100_CMRMain.[Rtip Amount],
T0100_CMRMain.DidSetsUpdateCorrectly, T0100_CMRMain.[Second Day RTIP
Batch Number], T0100_CMRMain.[Second Day Rtip Batch Amount],
T0100_CMRMain.[Second Day Approved By], T0100_CMRMain.[Check Pull
Needed], T0100_CMRMain.[Check Number that Was Pulled],
T0100_CMRMain.[Day Two Comments], T0100_CMRMain.FeeAdjustment,
T0100_CMRMain.[SetsReceipt#], T0100_CMRMain.[M/CReasonCode],
T0100_CMRMain.[M/C Payee], T0100_CMRMain.ManualCheckAmountofCheck,
T0100_CMRMain.Rejected, T0100_CMRMain.[Reason For Rejection],
T0100_CMRMain.[Recoupment Needed], T0100_CMRMain.Comments,
T0100_CMRMain.[Pending Approval], T0100_CMRMain.Approvedby,
T0100_CMRMain.[Approved Yes Or No], T0100_CMRMain.[Date Approved]
FROM T0100_CMRMain
WHERE (((T0100_CMRMain.DateCompleted)<=#11/1/2006#));
I can run the query manually and it works fine. Not sure why it has
problems executing in code.
Any help is greatly appreciated.
For now, comment out the line
mydb.QueryDefs.Delete qdf.Name
Then, once the code runs, open the query in Design mode, switch to the
SQL
view, copy the SQL that's there and paste it into your reply.
Also, try running the query. Is the error message any more explicit
then?
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)
message
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...

read more »- Hide quoted text -- Show quoted text -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top