how to update ms access default dates for a given record.

[redmeat]

I'm using ASP and JScript.

How do I update a date value in a MS Access databse where I want the database to generate the value (date).

The database has the default value setup correctly because when I INSERT a new record it works fine. But how do I UPDATE the date value ehld, without passing it a date.

Many thanks.

Jack.

PS: I tried passing it empty string or null, but that they were stupid guesses.

[whammy]

If you've tried updating it and passing it a null or empty value, my guess would be that the default value will stay the same unless you actually update it by passing it a valid date value.

If that is indeed the case, I'd presume you'd need to pass it a new date value. Not sure on the exact syntax with JScript, but I assume the SQL Statement would be the same...

UPDATE tablename SET mydate = #1/1/2003# WHERE ID = 23

for instance.

Hope this works, I haven't had a chance to test it. :)

[redmeat]

Thanks, but I don't want to insert the date value, because calling the date value in JScript excludes 0's if they're not required.

For example, I need 01/05/2002 but JScript would give me 1/5/2002

So I thought about manually inserting the 0's but that is so convoluted and also converts the date to a string. All this I'm sure is not required if I could only refresh the existing date value in the Access database.

Any help would be sincerely appreciated.

Jack.

[glenngv]

The format followed by JScript may be the format specified in the Regional Settings of the server. The Short Date Style should be set to MM/dd/yyyy.
But for me, I won't rely on that settings because it could be changed. So to ensure to always insert date in the format you like, you need to make a function that converts to correct format. That's an extra code to execute but ensures the correct data. And you don't have to bother if the converted date is a string since you will passed the SQL statement as a string:

strDate = "01/05/2002" //date after conversion
strSQL = "UPDATE tablename SET mydate = #" + strDate + "# WHERE ID = 23"
rs.Open(strSQL)

Is my JScript syntax correct? Coz I haven't use it as a server-side language :D

[redmeat]

Thank you, that seems useful enough, but this is daft, surely there must be away to update the default date value in a database; especially since I have specified a default value and mask in the database itself.

Hmmmm, I'm considering getting a value by inserting a new temporary record in the database, getting the date value and then deleting the record.

Thanks for the input, as for the JScript, what JScript? :D

[BigDaddy]

Just a thought, but if you've got a default value for the date, would deleting the date value or setting it back to null cause it to be re-set by the database?

[redmeat]

Yeah, exactly, this idea caused me great excitement when I thought of it, but unfortuantly, Access doesn't allow me insert a null value either by the jeyword null or by an empty string or value.

[whammy]

In the database design view, did you check the box that says "Allow Nulls" for your date field?

:)

I thought that your original idea would work too, maybe that's the problem.

[redmeat]

Yeah, well I had tried that - but if I allow nulls, then it does exactly what it says on the tin: i.e. nothing. LOL. So it just allows a blank / empty field.

This is starting to replace sleep. Not good.

Thank you.

[whammy]

Why dont' you want to update the database with a new date? You can still insert it as a string, and put zeroes in wherever you want.

:confused:

[redmeat]

I just wanted to do away with daft formatting functions, and let the DB do all the work. More consistent, less bug prone (or maybe that's just my code LOL).

[whammy]

I tend to go with the Bruce Li method... (even in programming).

"Use what works".

Access isn't exactly a cutting edge database... lol

[redmeat]

LOL, yeah I know it's a little odd - being so picky and then using Access, but truth is I have yet to learn others. Hmmmm, well, thanks for the help and fun along the way.

[whammy]

Cool... but in the meantime I would just insert a new date... I KNOW that works, from experience. :D

[redmeat]

Yes and just as Bruce Lee also said "don't look at the finger, look at the............" ah hang on, that doesn't work.

hmmmm,

[whammy]

Haha

[whammy]

Don't forget - it doesn't matter how you insert the date, really. You can format it however you want when you pull it from the database using string manipulation on it (I would still use a datetime field)...