What is wrong with my sql?

[Guest]

I am running the following sql from my vba code. I need to add a field to a
table and set its default to ("FY" concatenated with current year "YY"). I am
getting all sort of error messages. can some one tell me why?
thanks
here is my sql:
*************************************
"ALTER TABLE BuildingDetails_Temp ADD COLUMN ArchiveYear Text(10) DEFAULT
'FY' & #" & Format(DateAdd("yy", 1, Date), "yy") & "#"
***************************************
Al

 

[Allen Browne]

Try executing the command under ADO, i.e.:
Dim strSQL As String
strSQL = "ALTER TABLE ...
CurrentProject.Connection.Execute strSQL

 

[Jamie Collins]

I am running the following sql from my vba code. I need to add a field to a
table and set its default to ("FY" concatenated with current year "YY"). I am
getting all sort of error messages. can some one tell me why?
thanks
here is my sql:
*************************************
"ALTER TABLE BuildingDetails_Temp ADD COLUMN ArchiveYear Text(10) DEFAULT
'FY' & #" & Format(DateAdd("yy", 1, Date), "yy") & "#"
***************************************

Because you're made all sort of errors <g>?

The DateADD() parameter should be 'y' rather than 'yy'. Your quotes do
not balance e.g. try

SELECT 'FY' & FORMAT(NOW(), 'YY')

There may be a catch-22 here: you need to be in ANSI-92 Query Mode to
execute the ALTER TABLE..DEFAULT syntax but IIRC the same syntax only
allows literal values or niladic expressions such as current timestamp
in the DEFAULT definition, so DATE() is OK but Format causes a syntax
error. This also is the case for the SQL-92 standard on which the
syntax ANSI-92 Query Mode syntax is loosely based.

Perhaps you can use the Access user interface or DAO code to force the
expression into the DEFAULT?

Jamie.

--

 

[Guest]

Thanks Allen, it works nicely as you suggested.
Al