Using Date as one of the field in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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:
 
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.
 
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
 
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.
 
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
Jack said:
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.
Click to expand...
Regards.
 
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 said:
"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.
Click to expand...

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.

--
 
Jamie said:
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.
Click to expand...


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 said:
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.
Click to expand...

You've got things quite a bit confused, it seems.

Take a look in the Object Browser in the MS Access Visual basic Editor.
Change the dropdown from '<all libraries>' to 'Access' and search using
the keyword 'date'. You'll see lots of BeforeUpdate's and AfterUpdate's
but no DATE function.

Change the dropdown to VBA and you will see the DATE function listed
for VBA's DateTime class. That's the very same VBA library that is used
in all the MS Office apps. Jet also has a DATE function.

Some people use 'Access' and 'Jet' interchangeably and usually there's
no harm in that. But you have stated (twice) that the reason the word
DATE is reserved by Jet is because it is reserved by MS Access and that
is incorrect. The word DATE is reserved in Jet in its own right,
presumably because Jet has a DATE function. I cannot say for sure but I
suspect the reason MS Access has DATE as a reserved word is because it
is reserved in Jet, in which case you would have got your reasoning
completely the wrong way round.
That the word is also
reserved in other Jet-based apps was immaterial.
Click to expand...

I'm not referring to 'other Jet-based apps', I'm referring to Jet
itself i.e. the very same Jet as used by MS Access apps. Take a look
this KB article, in particular the title:

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/default.aspx?scid=kb;en-us;321266
This is an ACCESS newsgroup. The question was about ACCESS. My
experience with rdbms's has been entirely with ACCESS.
Click to expand...

Are you saying that discussions about Jet are prohibited here? It was
you who first referred to Jet in this thread, I think.

Jamie.

--
 
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
 
LeAnne said:
So are you here to actually help people, too, or are you just here to
Click to expand...
correct those who are?
Click to expand...

Do a search in the google groups archive using my name or handle, then
make you own judgement on the quality of the help I offer. You may even
discover whether I get sore when someone picks me up on a misstatement.

If this thread results in someone thinking more in terms of 'Access'
and 'VBA' and 'Jet' as distinct entities then I have done good, I
think.

Jamie.

--
 
Back
Top