How to split date

[Chrisma]

I have a back end MS Access and one of this field was Start date with the format dd/mm/yyyy. In my Add form the user are going to enter the date in three text boxes ( days, month and year) I have a command to convert the date enter to become dd/mm/yyyy before it store to MS Access.

But now, I need to add another page to update the information and i need to split the start date from MS Access. In the update page i have 3 text boxes to enter the days, month and year separately.

anyone can help me to split the start date from MS Access using my update form.

here is the part of code for date:


<td width="39%"> <div align="center">
<b>Start Date:<br></b>
<i>(dd/mm/yyyy)</i><b><br>

<input type="text" tabindex="7" name="startd" size="2" maxlength="2" onkeyup="return autoTab(this, 2, event);" onBlur="leadingzero .value, 'updatetraining', 'startd')">
/
<input type="text" tabindex="8" name="startm" size="2" maxlength="2" onkeyup="return autoTab(this, 2, event);" onblur="leadingzero(this.value, 'addtraining', 'startm')">
/
<input type="text" tabindex="9" name="starty" size="4" maxlength="4" onkeyup="return autoTab(this, 4, event);" value="<% response.write session("start_date")%>" onblur="CheckDateOnTheFly document.updatetraining.startd.value,document.updatetraining.startm.value,document.updatetraining.starty.value)">

</div></td>



here is the code to save the update to Ms Access:
<%
application.lock
response.buffer = "TRUE"

'replace all single quotes with two single quotes inorder for names like o'connell to work in sql
function sqlsafe(str)
sqlsafe = Replace(str, "'", "''")
end function

function convertdate(day,month,year)
'This function takes the day,month and year which was entered in the form
'converts it into a format which is suitable to be inserted into oracle

'Change the numeric month into a text month
if month = 1 then
monthtxt = "JAN"
elseif month = 2 then
monthtxt = "FEB"
elseif month = 3 then
monthtxt = "MAR"
elseif month = 4 then
monthtxt = "APR"
elseif month = 5 then
monthtxt = "MAY"
elseif month = 6 then
monthtxt = "JUN"
elseif month = 7 then
monthtxt = "JUL"
elseif month = 8 then
monthtxt = "AUG"
elseif month = 9 then
monthtxt = "SEP"
elseif month = 10 then
monthtxt = "OCT"
elseif month = 11 then
monthtxt = "NOV"
elseif month = 12 then
monthtxt = "DEC"
end if

'Create Tmstmp variable
convertdate = day&"/"&monthtxt&"/"&year
end function

'define odbc variables
strdsn = "DSN=pd;UID=pd;PWD=pd"
set cn = server.createobject("ADODB.Connection")
set rs = server.createobject("ADODB.Recordset")
set rs2 = server.createobject("ADODB.Recordset")
cn.open strdsn


sql = "UPDATE TRAINING SET PROVIDER_NAME = "&provider&", FUND_SOURCE = "&funding&", HOURS = "&hours&", BRANCH_COSTS = "&brcst&", SCHOOL_COSTS = "&schcst&", ERT_COSTS = "&ert&", DIRECT_COSTS = "&direct&", PROGRAM_TYPE = '"&program_type&"' WHERE STAFF_SK = "&staff_sk&" AND COURSE_NAME = '"&sqlsafe(COURSE_NAME)&"' AND BRANCH = '"&sqlsafe(branch)&"' AND START_DATE = "&startdate&" "
'response.write sql
rs.open sql, cn

 

[seagoing]

Value for day...
<%
=right(("00" & cstr(day(session("start_date")))),2)
%>

Value for month...
<%
=right(("00" & cstr(month(session("start_date")))),2)
%>

Value for year...
<%
= cstr(year(session("start_date")))
%>

 

[Tarwn]

Rather than handle these as strings, you would be much better off converting them to actual dates. While I know there are some issues with sending dates to access and getting them to store correctly (especially when switchingbetween mm/dd/yyyy and dd/mm/yyyy) if you manage to overcome those then the rest of your code becomes much easier.

Getting day, month, year out of a date:
Response.Write Day(yourDate)
Response.Write Month(yourDate)
Response.Write Year(yourDate)

Getting a three character month:
Response.Write Left(MonthName(Month(yourDate)),3)

MS Access will also let you insert dates in other formats that will allow you to ignore the order of dd/mm/yyyy vs mm/dd/yyyy. For instance, using the DateSerial(year, month, day) function is a much faster way to convert 3 seperate values into a date. Then when you insert the value you could use DateFormat to output a long date (like March 21, 2006) so Access won't get confused about the formatting.

Also, when executing an update satement you don't need a recordset object, it's just overhead. You can use the .Execute method of the Connection object to execute queries and commands (queries return a recordset already formed, commands don't). So you could change your last part:

[b]Original[/b]
strdsn = "DSN=pd;UID=pd;PWD=pd"
set cn = server.createobject("ADODB.Connection")
set rs = server.createobject("ADODB.Recordset")
set rs2 = server.createobject("ADODB.Recordset")
cn.open strdsn


sql = "UPDATE TRAINING SET PROVIDER_NAME = "&provider&", FUND_SOURCE = "&funding&", HOURS = "&hours&", BRANCH_COSTS = "&brcst&", SCHOOL_COSTS = "&schcst&", ERT_COSTS = "&ert&", DIRECT_COSTS = "&direct&", PROGRAM_TYPE = '"&program_type&"' WHERE STAFF_SK = "&staff_sk&" AND COURSE_NAME = '"&sqlsafe(COURSE_NAME)&"' AND BRANCH = '"&sqlsafe(branch)&"' AND START_DATE = "&startdate&" "
    'response.write sql
    rs.open sql, cn

[b]Using cn.Execute[/b]
strdsn = "DSN=pd;UID=pd;PWD=pd"
set cn = server.createobject("ADODB.Connection")
cn.open strdsn


sql = "UPDATE TRAINING SET PROVIDER_NAME = "&provider&", FUND_SOURCE = "&funding&", HOURS = "&hours&", BRANCH_COSTS = "&brcst&", SCHOOL_COSTS = "&schcst&", ERT_COSTS = "&ert&", DIRECT_COSTS = "&direct&", PROGRAM_TYPE = '"&program_type&"' WHERE STAFF_SK = "&staff_sk&" AND COURSE_NAME = '"&sqlsafe(COURSE_NAME)&"' AND BRANCH = '"&sqlsafe(branch)&"' AND START_DATE = "&startdate&" "

cn.Execute sql

-T

 

[seagoing]

All true but Chrisma asked how to split an existing date into three component parts, to present each part seperately in his forms <input> fields, so the date could be edited?

 

[onpnt]

Tarwn was making a suggestion based on experience regarding efficiency and stability. Not going against the original question and or not going against your suggestion


General FAQ faq333-2924
5 steps to asking a question faq333-3811