[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 597
  • Last Modified:

Make Table with year

I have a Maketable query which I would run each year,

SELECT tblShiptProd.* INTO tblShipt2011
FROM tblShiptProd
WHERE (((Year([ShipDate]))=Year(Date())-1));

I could run this in a procedure on a form, if I knew how to update the year part of the table name.
I'm sure there is a way of making a table with this query and naming the table at runtime with the previous year. i.e. If I run the procedure in January 2013 the table it would make would be called tblShipt2012.

As usual any advice is much appreciated.
Stephen Byrom
Stephen Byrom
1 Solution
You could do this using VBA in a command button click event:

Dim strSQL as string
Dim strTableName as string

strTableName = tblShipt & (Year(Date()) - 1)
strSQL = "SELECT tblShiptProd.* INTO " & strTableName & "FROM tblShiptProd WHERE (((Year([ShipDate]))=Year(Date())-1));"

CurrentDB.execute strSQL, dbfailonerror

Open in new window

Stephen ByromAuthor Commented:

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now