how to include current date in output table name

[pooksa]

Can someone please tell me how to include the current date in the name of the output table from a make-table query?

For example I want the table to be named "dogs06062003" today and when I run the query tomorrow it would be named "dogs06072003"

I'm thinking it's something like dogs"&Date() ???

Thanks in advance!

pooksa

 

[smedvid]

Dim lcMonth as String
Dim lcDay as String
Dim lcYear as String

lcMonth = Format(Month(Date()),##) 'Not sure on syntax..
lcYear = Format(Year(Date()),##) 'Not sure on syntax..
lcDay = Format(Day(Date()),##) 'Not sure on syntax..

lcFileName = "dogs" & lcYear & lcMonth & lcDay


Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[pooksa]

Steve,

Thank you for your prompt response - unfortunately I have no idea how to incorporate what you said to do

I'm sorry to sound so ignorant - I don't know VB at all.

If you have any additional suggestions, I'd appreciate them.

Thanks,
pooksa

 

[mpastore]

- Create a new accee module (modules, new)
- paste the follwing into it:

Sub dog_table_make()

Dim sSQL As String
sSQL = &quot;SELECT Table1.* INTO dogs<<DATE>>&quot; _
& &quot; FROM Table1&quot;

sSQL = Replace(sSQL, &quot;<<DATE>>&quot;, Format(Date, &quot;MMDDYYYY&quot)

CurrentDb.Execute (sSQL)


End Sub


If you need instructions to run, ask

Mike Pastore

Hats off to (Roy) Harper

 

[pooksa]

Mike,

Hey that's closer - it named the table &quot;dogs_Date&quot;

I just cut/pasted and named the module modDogsByDate...

almost there what did I do wrong?

Thanks!
pooksa (Chris)

 

[smedvid]

Sorry u need to learn how to code..

create a New Module in the database.. .

paste code below... then call from macro or button.

htwh..



Public Function RunMyQuery()
Dim lcQueryStr As String
Dim lcYear As String
Dim lcDay As String
Dim lcMonth As String
Dim ThisDB As DAO.Database
Dim lcTableName As String
Dim lc_Table As DAO.TableDef
Set ThisDB = CurrentDb

lcYear = Format(Year(Date), &quot;yyyy&quot
lcDay = Format(Day(Date), &quot;00&quot
lcMonth = Format(Month(Date), &quot;00&quot
lcTableName = &quot;Dog&quot; & lcYear & lcMonth & lcDay

lcQueryStr = &quot;SELECT Table1.ID, Table1.Field1, Table1.Field2 &quot;
lcQueryStr = lcQueryStr & &quot;INTO &quot; & lcTableName & &quot; &quot;
lcQueryStr = lcQueryStr & &quot;FROM Table1;&quot;

'Does Table Exist Alraedy - If So Delete
For Each lc_Table In ThisDB.TableDefs
If Trim(lc_Table.Name) = Trim(lcTableName) Then
' Delete Existing Query!
ThisDB.TableDefs.Delete (lcTableName)
ThisDB.TableDefs.Refresh
End If
Next lc_Table

ThisDB.Execute lcQueryStr, dbFailOnError

MsgBox (&quot;Done&quot
End Function




Steve Medvid
&quot;IT Consultant & Web Master&quot;
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[mpastore]

Try this...

Dim sSQL As String
sSQL = &quot;SELECT Table1.* INTO dogs<<DATE>>&quot; _
& &quot; FROM Table1&quot;

sSQL = Replace(sSQL, &quot;<<DATE>>&quot;, Format(Now(), &quot;MMDDYYYY&quot)

CurrentDb.Execute (sSQL)


Mike Pastore

Hats off to (Roy) Harper

 

[smedvid]

Note I think the short code example will fail if run twice...

Steve Medvid
&quot;IT Consultant & Web Master&quot;
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[mpastore]

Ja,

Need to put in check to see if table already there.

Mike Pastore

Hats off to (Roy) Harper

 

[pooksa]

I tried putting the &quot;new&quot; short code into the module, still created a table named dog_date

I deleted short code and replaced with long code, same thing, table named dog_date

Maybe I'm shooting too high - this is a qry that will be run once per day and each day I wanted it to create a new table with the current date, not delete yesterday's and replace... is that possible?

Or should I just output to the table and manually edit the name to include date everyday?

 

[mpastore]

Can you send me the code you put into your module?
What version of ms access are you running?

Mike Pastore

Hats off to (Roy) Harper

 

[pooksa]

Option Compare Database
Sub dog_table_make()


Dim sSQL As String
sSQL = &quot;SELECT Table1.* INTO dogs<<DATE>>&quot; _
& &quot; FROM Table1&quot;

sSQL = Replace(sSQL, &quot;<<DATE>>&quot;, Format(Now(), &quot;MMDDYYYY&quot)

CurrentDb.Execute (sSQL)

End Sub


This is everything on the module page - but when I ran it again, now it just names the table &quot;dog&quot;.

I swear I thought this was really easy, when I chose make-table and it asks what table to output to I thought I just had to put something like dog&Today() for the table name and it would call the table dog06062003

Sorry to be such a pain!

Chris

 

[mpastore]

You're not being a pain. Do this...go into the command window (CTL+G) and paste the following:

?Format(Now(), &quot;MMDDYYYY&quot)

Now hit enter, what do you see?
Also, what version of access are you running?

Mike Pastore

Hats off to (Roy) Harper

 

[pooksa]

Mike,

I'm running Access2000

Here is everything in the VB code:

Option Compare Database
Sub dog_table_make()


Dim sSQL As String
sSQL = &quot;SELECT Table1.* INTO dogs<<DATE>>&quot; _
& &quot; FROM Table1&quot;

sSQL = Replace(sSQL, &quot;<<DATE>>&quot;, Format(Now(), &quot;MMDDYYYY&quot)

CurrentDb.Execute (sSQL)

End Sub

Thanks for all your help - both of you

Chris

 

[mpastore]

Chris, you need to debug the code. Do this


Option Compare Database
Sub dog_table_make()


Dim sSQL As String
sSQL = &quot;SELECT Table1.* INTO dogs<<DATE>>&quot; _
& &quot; FROM Table1&quot;

sSQL = Replace(sSQL, &quot;<<DATE>>&quot;, Format(Now(), &quot;MMDDYYYY&quot)
stop

CurrentDb.Execute (sSQL)

End Sub

Now run the code, the cursor will stop at the &quot;stop&quot; command. In order to evaluate what sSQL is, go to control window (bottom of screen) and type:

?sSQL

Press enter, what do you see?


Mike Pastore

Hats off to (Roy) Harper

 

[pooksa]

Mike,

OK, I made a new module called modTest and copy/pasted the above.

To &quot;run&quot; it, do I just double click on modTest in the modules tab? I just see the same screen where I pasted the code.......

if I type ?sSQL at the bottom of that screen and hit enter it changes it to Print sSQL - do I don't think I ran it correctly

We have Access2000

 

[mpastore]

To run it, click your mouse anywhere in the module and press the F5 key. You can also type

call dog_table_make

in your command window and press enter. How were you running the module before. You were reporting test results regarding table name, I'm confused how you did this before.

Mike Pastore

Hats off to (Roy) Harper

 

[pooksa]

Hi Mike,

I think I already had the qry naming the table dog_date and never &quot;ran&quot; the code.

So if I understand now:

1. If I run the qry, it makes a table called &quot;dogs&quot;,
2. then if I run the code it makes a new table called dogs06062003
3. I need to create a macro to OpenQuery then RunCode to get the dogs06062003 table I really want
4. Tomorrow (Monday) when I run the Macro it will overwrite the &quot;dogs&quot; table, leave the &quot;dogs06062003&quot; table alone and make a new table called &quot;dogs06092003&quot;

Am I close?

Chris

 

[mpastore]

You are very close. Good luck!

Mike Pastore

Hats off to (Roy) Harper

 

[pooksa]

Good Morning Mike!

Thank you so much for all your help last week! I ran the macro today and everything worked Just wanted to let you know how much I appreciate all your help!

Chris