default date/time format in access?

[DKY]

I have an asp page that puts the date into a certain column in my access database and it works perfectly fine when the field is set up to receive 'text'. My date ends up looking like this. 3/18/2005 6:27:18 AM When I change it from 'text' to 'Date/Time' it no longer accepts this date. Is it because of the slashes? or the colons? is there supposed to be dashes instead of slashes or something? What is the acceptable format for the date and time when my field has 'Date/Time' selected?

 

[The_Doc_Man]

When storing a date field in Access and the origin of the date was a text box, try using [datefield] = CDate$([form].[textbox])

 

[DKY]

actually its not from a textbox. its from the now() function in asp. which outputs the date in the following format 3/18/2005 6:27:18 AM
Does Access not accept this format for some reason? If not, what is the correct format so I can figure out how to code it so that I get that format?

 

[The_Doc_Man]

The format is right for a date that has been translated to text. My question is whether you are giving Access the syntax it needs to recognize that the text string being presented to it should be interpreted as a date.

The way you would do that in quoted strings would be "# dd-mmm-yyyy hh:nn AM #" 'cause pound-signs delimit date constants. But I'm not clear on the exact formats of data transmission between your .asp and the field in the database. THAT's the sticking point.

 

[DKY]

The format is right for a date that has been translated to text. My question is whether you are giving Access the syntax it needs to recognize that the text string being presented to it should be interpreted as a date.

The way you would do that in quoted strings would be "# dd-mmm-yyyy hh:nn AM #" 'cause pound-signs delimit date constants. But I'm not clear on the exact formats of data transmission between your .asp and the field in the database. THAT's the sticking point.

AAHHHAAA!!! ...... you lost me. LOL. I'm not sure that I am giving access the syntax it needs to recognize that the text string being presented to it should be interpreted as a date cause I dont know what that means. My sql statement (when I do a response.write on the screen) looks like so.

INSERT INTO hotlines(date_time_opened) VALUES(#3/17/2005 4:02:05 PM#)

I'm sure there's something wrong, I just dont know what it is.

 

[The_Doc_Man]

Can you try the INSERT INTO with quote-marks before the first pound sign and after the second one? Make it a string. (In the format you showed me, it is not.)

 

[DKY]

so, like this?
INSERT INTO hotlines(date_time_opened) VALUES('#3/17/2005 4:02:05 PM#')

 

[DKY]

that doesnt work and neither do double quotes

 

[The_Doc_Man]

awww, darn.

That ought to have been valid. CDate$("date-time-string") doesn't do it either?

 

[DKY]

not sure how to use that, is that asp?

 

[snoko]

Don't pass the date (Now) from the asp just set the default value in the Access table to Now().

 

[DKY]

Did I mention that I never used access before? HAHAHAAA. How do I do that?

 

[DKY]

k, I looked into this and found out that when I put the value in access to Now() it works fine. Problem is, I want the date and time that the form was submitted and then I also want the date and time for other things so the now will work for this one, but later when I try and put the date and time into other places in this row it gives me errors from asp. I dont get it.

######EDIT

Actually I found out that it puts the date and time in fiine but when I do an sql statement like so
SELECT * FROM hotlines WHERE date_time_opened=#03/23/2005 11:29:45 AM#
I get an error. I'm not sure if this is an asp only question or if its an access question, so If anyone has any ideas please let me know.

 

[DKY]

this was an asp issue. case closed.