Add 84 days onto a date input into a form 1

[MichelleButler]

Help!!! I have users inputting dates from a calendar that insert the dates they required into the form, but I would also like to be able to populate the next field that add 84 days onto it, from the first field. Here is an example of the code:

<%
Dim Conn ' Open Database Connection
Dim RS ' Open Record Set
Dim SQL ' SQL statement
Dim DSN ' DSN Less Connection
Dim StrParam '
StrParam = Request.QueryString("id")
DSN = "DBQ=" & Server.Mappath("../../Database/Master Tables.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"
Set RS = Server.CreateObject("ADODB.Recordset")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open DSN
SQL = " SELECT [12WeeklyReview].Id, [12WeeklyReview].[ServiceNo], [12WeeklyReview].Surname, [12WeeklyReview].[FirstName], [12WeeklyReview].Station, [12WeeklyReview].DateReview, [12WeeklyReview].DateReviewCarriedOut, [12WeeklyReview].DateNextReviewDue FROM 12WeeklyReview WHERE id="&StrParam
RS.Open SQL,Conn,3,1
if Request.Form("Update") = "Update" Then
if Request.Form("DateReviewCarriedOut") = "" Then
Response.Redirect("erroreditstudent.asp?Error=Date Review can't be blank")
End if
DateReviewCarriedOut = Request.Form("DateReviewCarriedOut")
SQLUpdate = "UPDATE 12WeeklyReview SET "
SQLUpdate = SQLUpdate & "DateReviewCarriedOut='"&DateReviewCarriedOut
SQLUpdate = SQLUpdate & "'WHERE ID=" & StrParam
Conn.execute SQLUpdate
response.Redirect("done.asp?Msg=Record has been updated")
Else
%>

The field DateReviewCarriedout is inserted with the date, but I would like to be able to add 84 days onto next the date that would be the NextReviewdate due. Please help!!!
Michelle

 

[Kendel]

You need to use DateAdd function

DateNextReviewDue = DateAdd("d", 84, DateReviewCarriedOut)

 

[MichelleButler]

Thank you for the speedy reply. Would I have to implement this into the update query.

Michelle

 

[Kendel]

Ok, here is what you need to do:

DateReviewCarriedOut = Request.Form("DateReviewCarriedOut")
DateNextReviewDue = DateAdd("d", 84, DateReviewCarriedOut)

SQLUpdate = "UPDATE 12WeeklyReview SET "
SQLUpdate = SQLUpdate & "DateReviewCarriedOut='"&DateReviewCarriedOut 
SQLUpdate = SQLUpdate & "DateNextReviewDue ='"&DateNextReviewDue 
SQLUpdate = SQLUpdate & "'WHERE ID=" & StrParam
Conn.execute SQLUpdate

 

[Kendel]

ahh....I think the SQLUpdate statement is missing some quotes. Should be

SQLUpdate = "UPDATE 12WeeklyReview SET DateReviewCarriedOut='"&DateReviewCarriedOut&"', DateNextReviewDue ='"&DateNextReviewDue&"' WHERE ID=" & StrParam

 

[MichelleButler]

Thank you Kendel, I will try this. Its has been a very long time since I have used tek tips, but everytime I post a problem, I have always had really good response. Many thanks again.!!!!!!!!!!!!!!!

Michelle

 

[Kendel]

Your welcome. DateAdd won't work if DateReviewCarriedOut is not a date. You can use IsDate() function to make sure all the variables are in the right date format.

 

[MichelleButler]

I am updating the DateReviewCarriedOut with a calendar using the following code:

<tr>
<td width="145" class="bodyBold8" bgcolor="#CCCCFF"><div align="right">Review Carried Out:</div></td>
<td bgcolor="#CCCCFF" width="259">
<input name="DateReviewCarriedOut" type="text" id="DateReviewCarriedOut" size="20"><img border="0" src="../../images/calendar.gif" name="ChoseDate" id="ChoseDate" IndexVal="DateReviewCarriedOut" onclick="vbscript:CALL GetDate(Me)"></td>
</tr>

Would I also need to Input DateNextReviewDue into the form aswell.

Michelle

 

[Kendel]

No, you don't need to. DateNextReviewDue is calculated based upon DateReviewCarriedOut.

 

[MichelleButler]

Thank you for the valuable information, Kendal. The coding you give me Kendal works really well, unfortunately the first date is in UK format but when it adds 84 days it returns it in american format. The first being UK and when it calculates the second dates The DateNextReviewDue, it returns it in American format. Any suggestions!!!

Michelle

 

[ChrisHirst]

add session.LCID = 2057 to top of the page to set UK date format

Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[MichelleButler]

Thank you Chris, I have already have this on the top of the form aswell.

Michelle

 

[ChrisHirst]

some of your problem is here

vbscript:CALL GetDate(Me) because you are using clientside vbscript the LCID setting will not affect the values and you will/could have a mix of settings depending on the client browser local time and locale settings.

to ensure accuracy of your dates you should be using all server-side that way you are in control of the format.

Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.