Update a date field using a trigger.

[SoDakRah]

I need to update a date field in a table when a trigger fires on a table. I
use this code:

CREATE TRIGGER Update_IMA_CycleDate ON ITR
FOR UPDATE
AS

IF UPDATE ITR_Reason like 'Cycle*'

BEGIN
UPDATE ITEM Set IMA_CycleDate = Format(Date(),"mm/dd/yyyy hh:mm")
inner join Item on ITR_ItemID = IMA_ItemID where
Inserted.ITR_ID = ITR.ITR_ID
End

I get Error 195: 'Date' is not a recognized function name. Any ideas why?

 

[Douglas J. Steele]

It makes no sense to format a date with hours and minutes, since Date
doesn't include time.

Take a look at http://www.sql-server-helper.com/tips/date-formats.aspx

 

[SoDakRah]

Ok, I did not mention I want time included. But my real problem is the error
message. The following all give the same error 195: Date(), DateTime(),
Now(), and GetDate().

 

[Douglas J. Steele]

Since you're using triggers, which aren't part of Access, you might try
asking in a newsgroup related to SQL Server.

My comment about time was due to you including time in your Format
statement. In fact, using the Format statement is questionable, since Format
returns a string.

 

[Larry Linson]

According to Access 2003 VBA Help, "DateTime" is the name of a module, the
contents of which you can view with the Object Browser. You call Functions
or Sub Procedures, not Modules, in Access. "GetDate" is not found in Access
VBA Help.

Date() and Now() work just fine for me in Queries against Jet with Access
2003.

The Jet database engine (default with Access) does not have provision for
triggers.

So perhaps there is more here that you have not mentioned. If you will
provide a precise, concise detail description of the environment and what
you are trying to do, maybe someone can offer a useful suggestion.

Larry Linson
Microsoft Office Access MVP

 

[Paul Shapiro]

I presume your trigger is running in SQL Server? If so, the function you
want is GetDate(). Date() is an Access-specific function and does not exist
in SQL Server. Format() is also Access-specific. Assuming your CycleDate
field is of the DateTime datatype, or SmallDateTime, then no conversion is
needed. If it's a string field, consider changing it to a smalldatetime
field or look up SQL Server's Convert function.

 

[SoDakRah]

Accept my apology. Your information was correct. It was my frustration
which prompted my previous reply. As I see in a later response, I am mixing
SQL and Access logic.

Thank you.

 

[SoDakRah]

Thanks to all, I have the syntax correct now.

I presume your trigger is running in SQL Server? If so, the function you
want is GetDate(). Date() is an Access-specific function and does not exist
in SQL Server. Format() is also Access-specific. Assuming your CycleDate
field is of the DateTime datatype, or SmallDateTime, then no conversion is
needed. If it's a string field, consider changing it to a smalldatetime
field or look up SQL Server's Convert function.