Problems inserting a date field into Access db

[Terry Murray]

Hi everybody:

When I try to insert a Date field using the Date() function into a field
that has a date type in an Access DB I obtain a time value of 12:01:18 AM
instead of the value of the date that I see when I display the result of
the Date() call using response.write.

Any help would greatly be appreciated.

Thanks

Terry

[Ken Schaefer]

You don't have the correct delimiters. You are, instead, submitting
something like 6/9/2003, which is 6 divided by 9 divided by 2003, which is a
small number, which equates to 1 minute and 18 seconds past midnight on
1/1/1900.

Instead, do:

strSQL = _
"INSERT INTO myTable (myDateField) VALUES (#" & Date() & "#)"

The # tell Access that you are submitting a string that's supposed to be
interpreted as a date.

However, if you just want to insert the current date, Access, internally,
has a Date() function as well, which you can call like this:

strSQL = _
"INSERT INTO myTable (myDateField) VALUES (Date())"

Cheers
Ken

"Terry Murray" <tgmurray@rogers.com> wrote in message
news:mqb6b.393104$4UE.81627@news01.bloor.is.net.ca ble.rogers.com...
: Hi everybody:
:
: When I try to insert a Date field using the Date() function into a
field
: that has a date type in an Access DB I obtain a time value of 12:01:18 AM
: instead of the value of the date that I see when I display the result of
: the Date() call using response.write.
:
: Any help would greatly be appreciated.
:
: Thanks
:
: Terry
:
:

[Terry Murray]

Thanks for the very quick response Ken. I did try using the Date function
directly the way you show it and received the same result.
Thanks again
Terry
"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:utiDw7BdDHA.1656@TK2MSFTNGP10.phx.gbl...

> You don't have the correct delimiters. You are, instead, submitting
> something like 6/9/2003, which is 6 divided by 9 divided by 2003, which is

a

> small number, which equates to 1 minute and 18 seconds past midnight on
> 1/1/1900.
>
> Instead, do:
>
> strSQL = _
> "INSERT INTO myTable (myDateField) VALUES (#" & Date() & "#)"
>
> The # tell Access that you are submitting a string that's supposed to be
> interpreted as a date.
>
> However, if you just want to insert the current date, Access, internally,
> has a Date() function as well, which you can call like this:
>
> strSQL = _
> "INSERT INTO myTable (myDateField) VALUES (Date())"
>
> Cheers
> Ken
>
> "Terry Murray" <tgmurray@rogers.com> wrote in message
> news:mqb6b.393104$4UE.81627@news01.bloor.is.net.ca ble.rogers.com...
> : Hi everybody:
> :
> : When I try to insert a Date field using the Date() function into a
> field
> : that has a date type in an Access DB I obtain a time value of 12:01:18

AM

> : instead of the value of the date that I see when I display the result

of

> : the Date() call using response.write.
> :
> : Any help would greatly be appreciated.
> :
> : Thanks
> :
> : Terry
> :
> :
>
>

[Ken Schaefer]

Which way did you try? I showed two different ways.

Also, what type of field is your date/time field?

Lastly, please post the code that you're actually using.

Cheers
Ken

"Terry Murray" <tgmurray@rogers.com> wrote in message
news:JNb6b.393224$4UE.110081@news01.bloor.is.net.c able.rogers.com...
: Thanks for the very quick response Ken. I did try using the Date function
: directly the way you show it and received the same result.
: Thanks again
: Terry
: "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
: news:utiDw7BdDHA.1656@TK2MSFTNGP10.phx.gbl...
: > You don't have the correct delimiters. You are, instead, submitting
: > something like 6/9/2003, which is 6 divided by 9 divided by 2003, which
is
: a
: > small number, which equates to 1 minute and 18 seconds past midnight on
: > 1/1/1900.
: >
: > Instead, do:
: >
: > strSQL = _
: > "INSERT INTO myTable (myDateField) VALUES (#" & Date() & "#)"
: >
: > The # tell Access that you are submitting a string that's supposed to be
: > interpreted as a date.
: >
: > However, if you just want to insert the current date, Access,
internally,
: > has a Date() function as well, which you can call like this:
: >
: > strSQL = _
: > "INSERT INTO myTable (myDateField) VALUES (Date())"
: >
: > Cheers
: > Ken
: >
: > "Terry Murray" <tgmurray@rogers.com> wrote in message
: > news:mqb6b.393104$4UE.81627@news01.bloor.is.net.ca ble.rogers.com...
: > : Hi everybody:
: > :
: > : When I try to insert a Date field using the Date() function into a
: > field
: > : that has a date type in an Access DB I obtain a time value of
12:01:18
: AM
: > : instead of the value of the date that I see when I display the result
: of
: > : the Date() call using response.write.
: > :
: > : Any help would greatly be appreciated.
: > :
: > : Thanks
: > :
: > : Terry
: > :
: > :
: >
: >
:
:

[Terry Murray]

Hi Ken, the sql statement that I constructed follows:

sql = "INSERT INTO customers" & _
"(firstname,lastname,address," & _
"address_2,city,state,country," & _
"postcode,email,BDATE," & _
"password,userid,PW_QUE," & _
"pw_answer, MANUAL_REG,PLAYER_ID," & _
"phone,maillist,GENDER, logincount, lastlogindate) " & _
"VALUES " & _
"(" & "'" & fname & "','" & lname & "','" & straddress1 & "','" & _
straddress2 & "','" & city & "','" & provstate & "','" & country &
"','" & _
poszip & "','" & email & "','" & bdate & "','" & _
password & "','" & username & "'," & pw_que & ",'" & _
pw_answer & "'," & "No" & ",'" & handle & "','" & _
phonenum & "'," & maillist & ",'" & gender & "',1," & Date() &
")"

lastlogindate is a Date/Time field in Access.

Thanks
Terry


"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:OvlG1bDdDHA.1884@TK2MSFTNGP10.phx.gbl...

> Which way did you try? I showed two different ways.
>
> Also, what type of field is your date/time field?
>
> Lastly, please post the code that you're actually using.
>
> Cheers
> Ken
>
> "Terry Murray" <tgmurray@rogers.com> wrote in message
> news:JNb6b.393224$4UE.110081@news01.bloor.is.net.c able.rogers.com...
> : Thanks for the very quick response Ken. I did try using the Date

function

> : directly the way you show it and received the same result.
> : Thanks again
> : Terry
> : "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> : news:utiDw7BdDHA.1656@TK2MSFTNGP10.phx.gbl...
> : > You don't have the correct delimiters. You are, instead, submitting
> : > something like 6/9/2003, which is 6 divided by 9 divided by 2003,

which

> is
> : a
> : > small number, which equates to 1 minute and 18 seconds past midnight

on

> : > 1/1/1900.
> : >
> : > Instead, do:
> : >
> : > strSQL = _
> : > "INSERT INTO myTable (myDateField) VALUES (#" & Date() & "#)"
> : >
> : > The # tell Access that you are submitting a string that's supposed to

be

> : > interpreted as a date.
> : >
> : > However, if you just want to insert the current date, Access,
> internally,
> : > has a Date() function as well, which you can call like this:
> : >
> : > strSQL = _
> : > "INSERT INTO myTable (myDateField) VALUES (Date())"
> : >
> : > Cheers
> : > Ken
> : >
> : > "Terry Murray" <tgmurray@rogers.com> wrote in message
> : > news:mqb6b.393104$4UE.81627@news01.bloor.is.net.ca ble.rogers.com...
> : > : Hi everybody:
> : > :
> : > : When I try to insert a Date field using the Date() function into

a

> : > field
> : > : that has a date type in an Access DB I obtain a time value of
> 12:01:18
> : AM
> : > : instead of the value of the date that I see when I display the

result

> : of
> : > : the Date() call using response.write.
> : > :
> : > : Any help would greatly be appreciated.
> : > :
> : > : Thanks
> : > :
> : > : Terry
> : > :
> : > :
> : >
> : >
> :
> :
>
>

[Ken Schaefer]

Hi Terry,

That code below implements neither of my two suggestions. If you want to use
the VBScript Date() function in your ASP code, then you need to also use the
appropriate date/time delimeters (which are: #).

strSQL = _
"INSERT INTO myTable " & _
"(myDateField) " & _
"VALUES(#" & Date() & "#)"

-or you can use the Date() function inside Access:

strSQL = _
"INSERT INTO myTable " & _
"(myDateField) " & _
"VALUES(Date())"

Cheers
Ken

"Terry Murray" <tgmurray@rogers.com> wrote in message
news:9zi6b.172152$_V.130846@news04.bloor.is.net.ca ble.rogers.com...
: Hi Ken, the sql statement that I constructed follows:
:
: sql = "INSERT INTO customers" & _
: "(firstname,lastname,address," & _
: "address_2,city,state,country," & _
: "postcode,email,BDATE," & _
: "password,userid,PW_QUE," & _
: "pw_answer, MANUAL_REG,PLAYER_ID," & _
: "phone,maillist,GENDER, logincount, lastlogindate) " & _
: "VALUES " & _
: "(" & "'" & fname & "','" & lname & "','" & straddress1 & "','" & _
: straddress2 & "','" & city & "','" & provstate & "','" & country &
: "','" & _
: poszip & "','" & email & "','" & bdate & "','" & _
: password & "','" & username & "'," & pw_que & ",'" & _
: pw_answer & "'," & "No" & ",'" & handle & "','" & _
: phonenum & "'," & maillist & ",'" & gender & "',1," & Date() &
: ")"
:
: lastlogindate is a Date/Time field in Access.
:
: Thanks
: Terry
:
:
: "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
: news:OvlG1bDdDHA.1884@TK2MSFTNGP10.phx.gbl...
: > Which way did you try? I showed two different ways.
: >
: > Also, what type of field is your date/time field?
: >
: > Lastly, please post the code that you're actually using.
: >
: > Cheers
: > Ken
: >
: > "Terry Murray" <tgmurray@rogers.com> wrote in message
: > news:JNb6b.393224$4UE.110081@news01.bloor.is.net.c able.rogers.com...
: > : Thanks for the very quick response Ken. I did try using the Date
: function
: > : directly the way you show it and received the same result.
: > : Thanks again
: > : Terry
: > : "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
: > : news:utiDw7BdDHA.1656@TK2MSFTNGP10.phx.gbl...
: > : > You don't have the correct delimiters. You are, instead, submitting
: > : > something like 6/9/2003, which is 6 divided by 9 divided by 2003,
: which
: > is
: > : a
: > : > small number, which equates to 1 minute and 18 seconds past midnight
: on
: > : > 1/1/1900.
: > : >
: > : > Instead, do:
: > : >
: > : > strSQL = _
: > : > "INSERT INTO myTable (myDateField) VALUES (#" & Date() & "#)"
: > : >
: > : > The # tell Access that you are submitting a string that's supposed
to
: be
: > : > interpreted as a date.
: > : >
: > : > However, if you just want to insert the current date, Access,
: > internally,
: > : > has a Date() function as well, which you can call like this:
: > : >
: > : > strSQL = _
: > : > "INSERT INTO myTable (myDateField) VALUES (Date())"
: > : >
: > : > Cheers
: > : > Ken
: > : >
: > : > "Terry Murray" <tgmurray@rogers.com> wrote in message
: > : > news:mqb6b.393104$4UE.81627@news01.bloor.is.net.ca ble.rogers.com...
: > : > : Hi everybody:
: > : > :
: > : > : When I try to insert a Date field using the Date() function
into
: a
: > : > field
: > : > : that has a date type in an Access DB I obtain a time value of
: > 12:01:18
: > : AM
: > : > : instead of the value of the date that I see when I display the
: result
: > : of
: > : > : the Date() call using response.write.
: > : > :
: > : > : Any help would greatly be appreciated.
: > : > :
: > : > : Thanks
: > : > :
: > : > : Terry
: > : > :
: > : > :
: > : >
: > : >
: > :
: > :
: >
: >
:
:

[Terry Liston]

Although certainly all of the responses from the guru's here are correct, if
your goal is simply to insert the current date into the database at the time
you create the record (in other words, when was this record created?), then
the easiest way is to set the field in Access with a default date. Open the
table in Access in design mode, go to the field in question, then in the
default value section, put in Date()

This will insert the current date automatically when the record is created,
then you don't have to do it in ASP.


"Terry Murray" <tgmurray@rogers.com> wrote in message
news:mqb6b.393104$4UE.81627@news01.bloor.is.net.ca ble.rogers.com...

> Hi everybody:
>
> When I try to insert a Date field using the Date() function into a

field

> that has a date type in an Access DB I obtain a time value of 12:01:18 AM
> instead of the value of the date that I see when I display the result of
> the Date() call using response.write.
>
> Any help would greatly be appreciated.
>
> Thanks
>
> Terry
>
>