What is wrong with my SQL statement??? 1

[PROXI]

StrSql = "INSERT INTO ForfTest ( CASE_SEQ_ID, PROCESSED_DATE, TRNMSTR_SEQ_ID, AMOUNT ) " & _
         "SELECT PUBLIC_PPAK_ACCT_ACTIVITIES.CASE_SEQ_ID, PUBLIC_PPAK_ACCT_ACTIVITIES.PROCESSED_DATE, PUBLIC_PPAK_ACCT_ACTIVITIES.TRNMSTR_SEQ_ID, PUBLIC_PPAK_ACCT_ACTIVITIES.DOLLAR_AMT AS Amount " & _
         "FROM PUBLIC_PPAK_ACCT_ACTIVITIES " & _
         "WHERE PUBLIC_PPAK_ACCT_ACTIVITIES.PROCESSED_DATE Between " & _
          Format([Enter Start Date:], "\#mm\/dd\/yyyy\#") And Format([Enter End Date:], "\#mm\/dd\/yyyy\#") & _
         "And PUBLIC_PPAK_ACCT_ACTIVITIES.TRNMSTR_SEQ_ID = 83 Or PUBLIC_PPAK_ACCT_ACTIVITIES.TRNMSTR_SEQ_ID = 36 " & _
         "ORDER BY PUBLIC_PPAK_ACCT_ACTIVITIES.PROCESSED_DATE;"

Thanks,

PROXI

 

[Andrzejek]

Do the:

Debug.Print StrSql

and show what's in Immidiate Window.

And what kind of error are you hetting?

Have fun.

---- Andy

 

[PHV]

What about this ?

StrSql = "INSERT INTO ForfTest (CASE_SEQ_ID,PROCESSED_DATE,TRNMSTR_SEQ_ID,AMOUNT) " & _
         "SELECT CASE_SEQ_ID,PROCESSED_DATE,TRNMSTR_SEQ_ID,DOLLAR_AMT AS Amount " & _
         "FROM PUBLIC_PPAK_ACCT_ACTIVITIES " & _
         "WHERE PROCESSED_DATE Between #" & _
          Format([Enter Start Date:], "yyyy-mm-dd") & "# And #" & _
          Format([Enter End Date:], "yyyy-mm-dd") & "# " & _
         "AND TRNMSTR_SEQ_ID IN (83,36) " & _
         "ORDER BY PROCESSED_DATE;"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PROXI]

Thanks PHV... I modified it a bit, but your suggestions made it work perfectly

Here is what I ended up with. I added a textbox to the my form that pulls in the DMax of the table on form load to give me a starting point and just have it run through today.

Thanks again

Private Sub CmdIMTUpdate_Click()
On Error GoTo Err_CmdIMTUpdate_Click

Dim StrSql As String

StrSql = "INSERT INTO Forfeiture_Activity (CASE_SEQ_ID, PROCESSED_DATE, TRNMSTR_SEQ_ID, AMOUNT) " & _
         "SELECT a.CASE_SEQ_ID, a.PROCESSED_DATE, a.TRNMSTR_SEQ_ID, a.DOLLAR_AMT AS Amount " & _
         "FROM PUBLIC_PPAK_REG_ACCT_ACTIVITIES_VW a " & _
         "WHERE a.PROCESSED_DATE Between #" & _
          Format([Forms]![Main]![TxtMaxDate] + 1, "yyyy-mm-dd") & "# And #" & _
          Format(Date, "yyyy-mm-dd") & "# " & _
         "AND a.TRNMSTR_SEQ_ID IN (83,36) " & _
         "ORDER BY a.PROCESSED_DATE;"
         
         
DoCmd.RunSQL StrSql

TxtMaxDate = DMax("[Processed_date]", "Forfeiture_Activity")

MsgBox "Database has been updated through " & MaxDate & ".", vbOKOnly

Exit_CmdIMTUpdate_Click:
    Exit Sub

Err_CmdIMTUpdate_Click:
    MsgBox Err.Description
    Resume Exit_CmdIMTUpdate_Click
    
End Sub

Thanks,

PROXI

 

[PROXI]

I have a similar statement that I can't get to work either

any help on this one PHV? :-D

        StrSQL = "INSERT INTO 222 ( Field2, Field3, Field4 ) " & _
                 "SELECT Sum(a.DOLLAR_AMT) AS Field2, b.TRUST, a.PROCESSED_DATE " & _
                 "FROM PUBLIC_PPAK_ACCT_ACTIVITIES a, " & _
                 "PPAK_CASES b " & _
                 "WHERE (a.CASE_SEQ_ID) = (b.SEQ_ID) " & _
                 "AND a.PROCESSED_DATE = #" & _
                    Format([Forms]![Form_910]![TxtHistDate], "yyyy-mm-dd") & "# & _
                 "AND a.TRNMSTR_SEQ_ID IN (11,20,21,22,25,26,28,29,31,32,33,88) " & _
                 "HAVING ((b.TRUST)=[Trust: Y or N]))" & _
                 "GROUP BY b.TRUST, a.PROCESSED_DATE, b.PPA_NBR, b.NBR; "

Thanks,

PROXI

 

[dhookom]

I believe the "HAVING" clause should follow the "GROUP BY". It actually looks like you could include the condition to the WHERE clause:

      StrSQL = "INSERT INTO 222 ( Field2, Field3, Field4 ) " & _
                 "SELECT Sum(a.DOLLAR_AMT), b.TRUST, a.PROCESSED_DATE " & _
                 "FROM PUBLIC_PPAK_ACCT_ACTIVITIES a, " & _
                 "PPAK_CASES b " & _
                 "WHERE (a.CASE_SEQ_ID) = (b.SEQ_ID) " & _
                 "AND a.PROCESSED_DATE = #" & _
                    Format([Forms]![Form_910]![TxtHistDate], "yyyy-mm-dd") & "# & _
                 "AND a.TRNMSTR_SEQ_ID IN (11,20,21,22,25,26,28,29,31,32,33,88) " & _
                 "AND b.TRUST=[Trust: Y or N] " & _
                 "GROUP BY b.TRUST, a.PROCESSED_DATE, b.PPA_NBR, b.NBR; "

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Anyway, replace this:
Format([Forms]![Form_910]![TxtHistDate], "yyyy-mm-dd") & "# & _
with this:
Format([Forms]![Form_910]![TxtHistDate], "yyyy-mm-dd") & "#[!] "[/!] & _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PROXI]

Alright... Sorry for the delay in responding. I had other projects that pushed this one to the side. Thanks PHV for finding the missing ". The code worked, but then I found that I didn't have one of the fields in the code. This is what I have now, but it is giving me a "Type Mismatch" Error. I know that it has something to do with the info in Field 1. Any ideas?

      StrSQL = "INSERT INTO 222 (Field1, Field2, Field3, Field4 ) " & _
                 "SELECT [PPA_NBR] & " - " & [NBR] AS Field1, Sum(a.DOLLAR_AMT) AS Field2, b.TRUST AS Field3, a.PROCESSED_DATE AS Field4 " & _
                 "FROM PUBLIC_PPAK_ACCT_ACTIVITIES a, " & _
                 "PPAK_CASES b " & _
                 "WHERE (a.CASE_SEQ_ID) = (b.SEQ_ID) " & _
                 "AND a.PROCESSED_DATE = #" & _
                    Format([Forms]![910]![TxtHistDate], "yyyy-mm-dd") & "# " & _
                 "AND a.TRNMSTR_SEQ_ID IN (11,20,21,22,25,26,28,29,31,32,33,88) " & _
                 "AND b.TRUST=[Trust: Y or N] " & _
                 "GROUP BY b.TRUST, a.PROCESSED_DATE, b.PPA_NBR, b.NBR; "

The field that I am trying to insert into (Field1) is a Text field. The fields PPA_NBR and NBR are both Text fields as well.

Thanks,

PROXI

 

[Andrzejek]

Again, do the:

[tt]Debug.Print StrSql[/tt]

Copy your SQL, paste it into SQL Editor and see what it will give you....

Help yourself.

Have fun.

---- Andy