Another Syntax Question

[Tomba]

I Get "Syntax error in query. Incomplete query clause." (3450) while running this code:

       strSQL = "SELECT Calendar.MyDate, Calendar.MyTime, Calendar.Currency, Calendar.[Data Released]," & _
            " Calendar.Forecast, Calendar.Previous, Calendar.Impact, [" & tblname & "].F2," & _
            " [" & tblname & "].F3, [" & tblname & "].F4, [" & tblname & "].F5," & _
            " [" & tblname & "].F6, [" & tblname & "].F7" & _
            " INTO " & varData(i) & " " & _
            " FROM Calendar INNER JOIN [" & tblname & "] ON Calendar.MyDate = [" & tblname & "].F1" & _
            " WHERE (((Calendar.Currency)='" & varData(i) & "') AND (([" & tblname & "].F2)" & _
            " Between [Calendar].[MyTime] And [Calendar].[Limit]));"
        DoCmd.RunSQL strSQL

Can anyone spot the problem?
Thanks a bunch in advanced!
Tomba.

[Tomba]

Ok, I got the syntax right, but now I have another problem - sorry for bothering:\
For some reason, after running the code below, the records on the table I've created gets deleted (access prmopts me a dialogue box notifying that the existing table I created is going to be deleted before the query is executed, and asking me whether I want to continue or not).
I'll really appreciate any further help!

Do
    If varData(i) <> "EUR" Then
        strSQL = "SELECT Calendar.MyDate, Calendar.MyTime, Calendar.Currency, Calendar.[Data Released]," & _
            " Calendar.Forecast, Calendar.Previous, Calendar.Impact, [" & tblname & "].F2," & _
            " [" & tblname & "].F3, [" & tblname & "].F4, [" & tblname & "].F5," & _
            " [" & tblname & "].F6, [" & tblname & "].F7" & _
            " INTO [" & varData(i) & "-" & tblname & "]" & _
            " FROM Calendar INNER JOIN [" & tblname & "] ON Calendar.MyDate = [" & tblname & "].F1" & _
            " WHERE (((Calendar.Currency)='" & varData(i) & "')" & _
            " AND (([" & tblname & "].F2) Between [Calendar].MyTime And [Calendar].Limit));"
    Else
        strSQL = "SELECT Calendar.MyDate, Calendar.MyTime, Calendar.Currency, Calendar.[Data Released]," & _
            " Calendar.Forecast, Calendar.Previous, Calendar.Impact, [" & tblname & "].F2," & _
            " [" & tblname & "].F3, [" & tblname & "].F4, [" & tblname & "].F5," & _
            " [" & tblname & "].F6, [" & tblname & "].F7" & _
            " INTO [" & varData(i) & "-" & tblname & "]" & _
            " FROM Calendar INNER JOIN [" & tblname & "] ON Calendar.MyDate = [" & tblname & "].F1" & _
            " WHERE (((Calendar.Currency)='EUR' OR (Calendar.Currency)='E-12' OR (Calendar.Currency)='GER')" & _
            " AND (([" & tblname & "].F2) Between [Calendar].MyTime And [Calendar].Limit));"
    End If
    i = i + 1
    DoCmd.RunSQL strSQL
Loop Until i = 2

Thanx alot!
Tomba.

[NeoPa]

SELECT INTO (What you're using) is a Make Table query.
That's what they do if they find the destination table already exists.
You probably want an INSERT INTO (Append) query instead.
Remember to clear the existing records first when you run it - assuming that's what you need it to do.

[MMcCarthy]

SELECT INTO (What you're using) is a Make Table query.
That's what they do if they find the destination table already exists.
You probably want an INSERT INTO (Append) query instead.
Remember to clear the existing records first when you run it - assuming that's what you need it to do.

Append query syntx ...

INSERT INTO Tablename1 (Field1, Field2)
SELECT Field1, Field2
FROM TableName2;

Mary