update query

"Ian Davies" <ian.dandav RemoveThis @virgin.net> wrote in message
news:rVNPd.1680$ma4.240@newsfe2-gui.ntli.net...
 >I have created an update query in my dataenvironment to change the date in
 > the bound table to the sys date. However, the query will not accept
 > '=Date'
 > in the new value box. Does anyone know the correct function. The help
 > gives
 > it as 'Date'
 > Ian
 >

Depends on which database you are using, but Now usually works

--
John Blessing

<a style='text-decoration: underline;' href="http://www.LbeHelpdesk.com" target="_blank">http://www.LbeHelpdesk.com</a> - Help Desk software priced to suit all
businesses
<a style='text-decoration: underline;' href="http://www.room-booking-software.com" target="_blank">http://www.room-booking-software.com</a> - Schedule rooms & equipment bookings
for your meeting/class over the web.
<a style='text-decoration: underline;' href="http://www.lbetoolbox.com" target="_blank">http://www.lbetoolbox.com</a> - Remove Duplicates from MS Outlook<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: update query 

I am using Access 2000
Ive tried date, =date, dat(), =date(), now, =now
but nothing works

Ian


"John Blessing" <newsgroup.TakeThisOut@LbeHelpdesk.com> wrote in message
news:379r0nF56jd6jU1@individual.net...
 > "Ian Davies" <ian.dandav.TakeThisOut@virgin.net> wrote in message
 > news:rVNPd.1680$ma4.240@newsfe2-gui.ntli.net...
  > >I have created an update query in my dataenvironment to change the date
in
  > > the bound table to the sys date. However, the query will not accept
  > > '=Date'
  > > in the new value box. Does anyone know the correct function. The help
  > > gives
  > > it as 'Date'
  > > Ian
  > >
 >
 > Depends on which database you are using, but Now usually works
 >
 > --
 > John Blessing
 >
 > <a style='text-decoration: underline;' href="http://www.LbeHelpdesk.com" target="_blank">http://www.LbeHelpdesk.com</a> - Help Desk software priced to suit all
 > businesses
 > <a style='text-decoration: underline;' href="http://www.room-booking-software.com" target="_blank">http://www.room-booking-software.com</a> - Schedule rooms & equipment bookings
 > for your meeting/class over the web.
 > <a style='text-decoration: underline;' href="http://www.lbetoolbox.com" target="_blank">http://www.lbetoolbox.com</a> - Remove Duplicates from MS Outlook
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: update query 

Thanks

I just gave that a go with the following results


SQL Builder (as mentioned earlier)

UPDATE Timer
SET Days = 1, OpenDate = Date()
(As I mentioned I couldnt type in the 'date()' bit without the message "Your
entry cannot be converted to a valid date time value"


Using Access query

UPDATE Timer SET Timer.OpenDate = Date(), Timer.Days = 1;

even using cut and paste from Access it accepts it and allows me to save the
query but ON SAVING I get the message
'Incomplete SET clause
Unable to parse query text'


Ian


"Steve Gerrard" <mynamehere DeleteThis @comcast.net> wrote in message
news:042dnc5jg_-KVJLfRVn-sw@comcast.com...
 >
 > "Ian Davies" <ian.dandav DeleteThis @virgin.net> wrote in message
 > news:rVNPd.1680$ma4.240@newsfe2-gui.ntli.net...
 > |I have created an update query in my dataenvironment to change the date
 > in
 > | the bound table to the sys date. However, the query will not accept
 > '=Date'
 > | in the new value box. Does anyone know the correct function. The help
 > gives
 > | it as 'Date'
 > | Ian
 > |
 > |
 >
 > It sounds like you are trying to enter the value as a Parameter in the
 > DE, which is not going to work. You can create a query which accepts a
 > parameter, but you would then need to assign the value in code, just
 > before running the query. The DE new value box will accept only literal
 > values.
 >
 > It will be much easier for you to simply create the update query in
 > Access. Once you have tested it and are sure it works, switch the query
 > to SQL view, and copy the SQL text.
 >
 > Then, over in VB, in the data environment command, select the SQL
 > option, and paste in the SQL text. All done.
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: update query 

"Ian Davies" <ian.dandav.TakeThisOut@virgin.net> wrote in message
news:VqQPd.1439$xg6.219@newsfe1-gui.ntli.net...
| Thanks
|
| I just gave that a go with the following results
|
|
| SQL Builder (as mentioned earlier)
|
|
| even using cut and paste from Access it accepts it and allows me to
save the
| query but ON SAVING I get the message
| 'Incomplete SET clause
| Unable to parse query text'
|
|

SQL Builder gave me the same message. However, you don't have to use SQL
Builder. When you choose the SQL Statement option for your command, you
can just paste the text in the box, and click OK.

I set up the following:
DE1 (connected to db1.mdb)
cmdTest
SQL Statement:
UPDATE Table1 SET Field2 = DATE()

Private Sub Command1_Click()
Call DE1.cmdTest
End Sub

Field2 was indeed updated to 2/13/2005
 >> Stay informed about: update query 

Thanks
Ill do it that way then

Ian
"Steve Gerrard" <mynamehere DeleteThis @comcast.net> wrote in message
news:m_mdnUea8ONono3fRVn-gw@comcast.com...
 >
 > "Ian Davies" <ian.dandav DeleteThis @virgin.net> wrote in message
 > news:VqQPd.1439$xg6.219@newsfe1-gui.ntli.net...
 > | Thanks
 > |
 > | I just gave that a go with the following results
 > |
 > |
 > | SQL Builder (as mentioned earlier)
 > |
 > |
 > | even using cut and paste from Access it accepts it and allows me to
 > save the
 > | query but ON SAVING I get the message
 > | 'Incomplete SET clause
 > | Unable to parse query text'
 > |
 > |
 >
 > SQL Builder gave me the same message. However, you don't have to use SQL
 > Builder. When you choose the SQL Statement option for your command, you
 > can just paste the text in the box, and click OK.
 >
 > I set up the following:
 > DE1 (connected to db1.mdb)
 > cmdTest
 > SQL Statement:
 > UPDATE Table1 SET Field2 = DATE()
 >
 > Private Sub Command1_Click()
 > Call DE1.cmdTest
 > End Sub
 >
 > Field2 was indeed updated to 2/13/2005
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: update query