Creating new table with user specified name

[waxdart23]

I have a form which has "Start_Date", "End_Date" and "TableName" text fields and I am trying to create a new table using these parameters.

I have a command button with the following code -

Dim strSQL As String
strSQL = "SELECT StewardDetails.First_Name, StewardDetails.Last_Name, Performances.Performance, "
strSQL = strSQL & "Performances.Perf_Date, Performances.Duration INTO "
strSQL = strSQL & "INTO " & Chr(34) & TableName & Chr(34) & " "
strSQL = strSQL & "FROM StewardDetails INNER JOIN (Performances INNER JOIN StewardAvailability "
strSQL = strSQL & "ON Performances.ID = StewardAvailability.Perf_ID) ON StewardDetails.ID "
strSQL = strSQL & "= StewardAvailability.Stewards_ID WHERE (((Performances.Perf_Date) Between "
strSQL = strSQL & "[Forms]![frmTestCreateHours]![StartDate] And [Forms]![frmTestCreateHours]![EndDate]) "
strSQL = strSQL & "AND ((StewardAvailability.Availability)=True)) ORDER BY StewardDetails.Last_Name, "
strSQL = strSQL & "Performances.Perf_Date;"
DoCmd.RunSQL strSQL

When I try to run it I get a Run-Time error (see attached jpg) and it highights the DoCmd.RunSQL strSQL part of the code.

I have also tried running it in a seperate module passing the table name, but it was unable to find the subroutine when I ran it.

Any ideas?

 

[Dave Eyley]

I would guess that the syntax of the completed SELECT statement is incorrect.

Try looking at the BETWEEN date1 AND date2 bit.
It should look like BETWEEN #date1# AND #date2#
You should insert these # in the code.

If in doubt, try to create the query using the Query builder, then switch to SQL view and see how it compares with your code.

It works for me.........most times


Dave Eyley

 

[VBAhole22]

You have the keyword INTO listed twice consecutively.
Insert :

debug.Print strSQL

and then check in the Immediate window to see the SQL statement you are sending. You can also cut and paste this statement form the immediate window into the query grid Access provides to see if it is really what you want. Conversely, you can have access generate the SQL for you from the grid and then just cut and paste that into your code and insert the variables.