Trouble with expression for renaming a table

[Susan L]

I need to make a backup of a table and rename the backup. I saw a post from
tina about using a macro that I thought would solve my issue, but I can’t get
it to work.

The table name: Exch_Agr
I want the name to be: Exch_Agr_09_2008, with the 09 being the previous month.

I had tried the following:
=“Exch_Agr_”& Month(Now())-1 &"_" & Format(DateAdd "m", -1,Date()),"mm"
Got error that Access could not parse the expression. Having failed there, I
copied tina’s expression and plugged in my table name, thinking if I could
get that working I could change it to fit my desired outcome.

Action: Rename
New Name: ="Exch_Agr_” & Format(Date(),"mmddyyyy")
Object Type: Table
Old Name: "Exch_Agr"
Received same error -- cannot parse

Removed the = sign and it did rename the table, but with the expression --
“Exch_Agr” & Format, etc.

I’m sure it’s my syntax at fault. Can anyone set me straight?

--
susan

[Steve Schapel]

Susan,

In your first example, you have omittes some spaces where there ought to
be spaces. And also the closing parenthesis is in the wrong place. And
also the last bit doesn't make sense and does not relate to the concept
of the current yeart that you are asking for. And also some of the quote
marks are in "smart quote" format... they need to be plain text quotes.
It should be like this:

="Exch_Agr_" & Month(Date())-1 & "_" & Year(Date())

Well, that still won't quote do it. That will return Exch_Agr_9_2008.
If you want the leading 0 on the month, try this:
="Exch_Agr_" & Format(Month(Date())-1,"00") & "_" & Year(Date())

--
Steve Schapel, Microsoft Access MVP

Susan L wrote:
> I need to make a backup of a table and rename the backup. I saw a post from

> tina about using a macro that I thought would solve my issue, but I can�t get

> it to work.
>
> The table name: Exch_Agr
> I want the name to be: Exch_Agr_09_2008, with the 09 being the previous month.
>
> I had tried the following:

> =�Exch_Agr_�& Month(Now())-1 &"_" & Format(DateAdd "m", -1,Date()),"mm"

> Got error that Access could not parse the expression. Having failed there, I

> copied tina�s expression and plugged in my table name, thinking if I could

> get that working I could change it to fit my desired outcome.
>
> Action: Rename

> New Name: ="Exch_Agr_� & Format(Date(),"mmddyyyy")

> Object Type: Table
> Old Name: "Exch_Agr"
> Received same error -- cannot parse
>
> Removed the = sign and it did rename the table, but with the expression --

> �Exch_Agr� & Format, etc.
>
> I�m sure it�s my syntax at fault. Can anyone set me straight?
>

[Susan L]

Thanks so much Steve. Guess I had more than a little trouble with syntax.
--
susan

"Steve Schapel" wrote:

> Susan,
>
> In your first example, you have omittes some spaces where there ought to
> be spaces. And also the closing parenthesis is in the wrong place. And
> also the last bit doesn't make sense and does not relate to the concept
> of the current yeart that you are asking for. And also some of the quote
> marks are in "smart quote" format... they need to be plain text quotes.
> It should be like this:
>
> ="Exch_Agr_" & Month(Date())-1 & "_" & Year(Date())
>
> Well, that still won't quote do it. That will return Exch_Agr_9_2008.
> If you want the leading 0 on the month, try this:
> ="Exch_Agr_" & Format(Month(Date())-1,"00") & "_" & Year(Date())
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Susan L wrote:
> > I need to make a backup of a table and rename the backup. I saw a post from

> > tina about using a macro that I thought would solve my issue, but I can’t get

> > it to work.
> >
> > The table name: Exch_Agr
> > I want the name to be: Exch_Agr_09_2008, with the 09 being the previous month.
> >
> > I had tried the following:

> > =“Exch_Agr_”& Month(Now())-1 &"_" & Format(DateAdd "m", -1,Date()),"mm"

> > Got error that Access could not parse the expression. Having failed there, I

> > copied tina’s expression and plugged in my table name, thinking if I could

> > get that working I could change it to fit my desired outcome.
> >
> > Action: Rename

> > New Name: ="Exch_Agr_” & Format(Date(),"mmddyyyy")

> > Object Type: Table
> > Old Name: "Exch_Agr"
> > Received same error -- cannot parse
> >
> > Removed the = sign and it did rename the table, but with the expression --

> > “Exch_Agr” & Format, etc.
> >
> > I’m sure it’s my syntax at fault. Can anyone set me straight?
> >
>