cannot update date in table

[vojinb]

Hy,
I have a table having one date field formatted as short date "15.12.2011", and on a form text box formatted as short date format. When I click on text box calendar appears and I choose proper date, but when I want to update date in table via query it returns error.
My query looks like:
CurrentDb.Execute ("UPDATE category SET date=" & Forms![main form]!txtDate.Value)
I don't see anything wrong but it keeps me returning error

And If I use US format update works but with date 31.12.1899

[alansidman]

What happens if you run the update query outside of VBA? Does it work? Are you getting an error message?

[vojinb]

Hi
when I run query:
UPDATE kategorija SET datum="21.12.2011"
then format of date works, but when I try to pick it from control on form gets error. Control is formatted ad date with calendar from side.

[alansidman]

Because it is a date value, you need to surround the date value with # symbols.

[vojinb]

Hi, ive tried also to use #, but it insert wrong date , for example I choose 15.12.2011 from calendar, and after update in my table is date 12.30.1899 )

[alansidman]

I just created a table (NewDates) with dates, a form with an unbound text box (qDate) and an update query.

Here is the SQL statement and it ran perfectly:

UPDATE NewDates SET NewDates.EndDate = [Forms]![frmTestSearch].[qDate];

I haven't tried to put this into a VBA statement, but the main difference between yours and mine appear to be the quotation marks and the ampersand.

[alansidman]

Ok I got it to work off a command button on a form. Here is the code:

CurrentDb.Execute ("UPDATE NewDates SET NewDates.EndDate =#" & [Forms]![frmTestSearch].[qDate]) & "#"

Need to have the update criteria surrounded by the # symbol
Alan

Crosspost: https://www.accessforums.net/showthr...ed=1#post93899

[vojinb]

Thanks,
It works ))
Great!