Using Date as one of the field in Access

[Jack]

Hi,
I am using Date as one of the fields in a Access table. Now when I am tring
to run a update query (UPDATE tblExpense SET Date ='03/15/2005',
contractedServiceExpense='15', travelexpense='20', pesonnelexpense='5' WHERE
tranid='2') , I am getting an error with a highlight on Date. I do no know
why this is happening. I would appreciate any help on this. Thanks. Regards.

 

[OsmarJr]

Try:

UPDATE tblExpense SET Date = #03/15/2005#,
contractedServiceExpense='15', travelexpense='20', pesonnelexpense='5' WHERE
tranid='2')

Dates and times must be enclosed in ##.


"Jack" escreveu:

 

[Jack]

Thanks for your advise. I changed my update code as:
UPDATE tblExpense SET Date = #03/15/2005# , contractedServiceExpense='15',
travelexpense='20', pesonnelexpense='5' WHERE tranid='2'
With this change though the same error is there with the date field being
highlighted. Any thoughts? Regards.

 

[LeAnne]

In addition to OsmarJr's comment, I'd suggest changing the name of your
field to something other than "Date." "Date" is a reserved word in
Access in that it refers to a specific function, and is therefore
reserved by the Jet db engine for SQL statements.

LeAnne

 

[Jack]

I guess the update statement did not work as I checked my date field which is
kept as a text field. However, if the date field is changed to date datatype,
then your concept should work. I will give it a shot. Thanks for the insight.
Regards.

 

[Debra Farnham]

Hi Jack

Date is a reserved Word in Access (it is actually a function). Try using a
tag in front of the word date or add a more descriptive word before the word
date and your query should work (so long as the syntax is correct which I
did not check).

HTH

Debra

Thanks for your advise. I changed my update code as:
UPDATE tblExpense SET Date = #03/15/2005# , contractedServiceExpense='15',
travelexpense='20', pesonnelexpense='5' WHERE tranid='2'
With this change though the same error is there with the date field being
highlighted. Any thoughts? Regards.

Regards.

 

[Jack]

Thanks for your advise LeAnne and Debra. The update query works now. However,
I am going to change the field name of Date to something different as Date is
a reserved word.
Regards.

 

[LeAnne]

I don't follow what you mean when you say 'therefore'. DATE is a
reserved word in MS Access *and* MS Jet (and odbc and SQL-92 and SQL-99
and SQL-2003 and others).

Jamie.

This is an ACCESS newsgroup. The question was about ACCESS. My
experience with rdbms's has been entirely with ACCESS. Consequently, my
reply to the OP specifically addressed the use of DATE() within the
ACCESS environment: that the word "date" refers to a specific function;
and for that reason the word is reserved by Jet. That the word is also
reserved in other Jet-based apps was immaterial.

LeAnne

 

[LeAnne]

I answered the original post in sufficient detail to solve the immediate
problem as described by the OP. If my phraseology was confusing, it was
not my intention to mislead anyone.

So are you here to actually help people, too, or are you just here to
correct those who are?

LeAnne