General discussion

Locked

Update field calculates date

By jlindseth ·
I need a little help. I have an MS Access database that users are going to be inputting data via a website, then once the data is in the database they will periodically go into the database via a website and update the various fields. It is currently working great except for one field, the ?startdate? field. It calculates the date and writes the product or sum to the database instead of the actual date (example?. if 1-27-73 is put in the update field, it writes ?99 to the database. Thanks forthe help!!!! Here is a scaled down version of the code from the ASP:

<%
strCasename = Request.Form("Casename")
strReferralDate = Request.Form("ReferralDate")
sngStartdate = Request.Form("Startdate")
If Request.Form("Action") = "Delete Record"Then
strAction = "D"
Else
strAction = "U"
End If
If strAction = "U" Then
SQL = "UPDATE CFSInput SET CFSInput.ReferralDate = '" & strReferralDate & "', CFSInput.Startdate = '" & sngStartdate & "', " WHERE CFSInput.casename = '" & strcasename & "'"
Else
SQL = "DELETE CFSInput.* From CFSInput WHERE CFSInput.casename = '" & strcasename & "'"
End If
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "FGCDATA"
Set objCommand = Server.CreateObject("ADODB.Command")
Set RS = Server.CreateObject("ADODB.Recordset")
objCommand.CommandText = SQL
objCommand.CommandType = adCmdText
Set objCommand.ActiveConnection = objConnection
RS.Open objCommand,,adOpenForwardOnly,adLockReadOnly
If strAction = "U" Then
%><h2>Record <%= strcasename %> Updated</h2>
<% Else %>
<h2>Record <%= strcasename %> Deleted</h2>
<% End If %>

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Update field calculates date

by jp-mattenet In reply to Update field calculates d ...

Hi,
Have your tried by specifically setting the date format, in a way that the database engine recognize. IMHO Access will try to convert the string that holds the date, using some predefined rules (check MS-access help for LNS support on dates),and never returns you an error! It just convert the date into garbage. My best results whare using formats where the month is not in numeric format (01-JUN-2000).

Hope it helps,
JP

Collapse -

Update field calculates date

by jlindseth In reply to Update field calculates d ...

The question was auto-closed by TechRepublic

Collapse -

Update field calculates date

by donq In reply to Update field calculates d ...

I assume from your text you have an Access 2000 field named "Startdate". You have a number of things to do:

One. In the Access Table where "Startdate" lives VERIFY Startdate's Data Type is "Date/Time". If it isn't - change it AND set the Format property to "mm/dd/yyyy". - Close the table and open your Active Server Page and insure it is linked to the "Date/Time" field Formatted "mm/dd/yyyy".

Access stores dates as a number incrementing each day by one so incoming data (from the internet) MUST be recognized as a Data/Time value so Access will automatically convert user input to a Long (numerical) value and store it as such.

Collapse -

Update field calculates date

by jlindseth In reply to Update field calculates d ...

The question was auto-closed by TechRepublic

Collapse -

Update field calculates date

by rgr_mi_usa In reply to Update field calculates d ...

if the date is a text literal or in a string variable Access still requires the incoming date string to be surrounded by #'s to be recognized as a date as follows:

mydatefield = #01-02-2000#

in your example, change it to:
CFSInput.Startdate =#" & sngStartdate & "#,"

Collapse -

Update field calculates date

by jlindseth In reply to Update field calculates d ...

The question was auto-closed by TechRepublic

Collapse -

Update field calculates date

by jlindseth In reply to Update field calculates d ...

This question was auto closed due to inactivity

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums