save datetime and inscrease numbers

**DrBunchman** · Feb 14 '08, 09:23 AM

Originally posted by phuc2583

I have some problem about the date time.I want to save a database with write by access that ID such as:yymm####
yy;year get two numbers(2008)->08
mm:month get two numbers->02
####:the order:0001,0002
the whole function:080200 01
when i save a customer that,Id increase 08020002,...,08 020010...
but step another month that increase 08030001,080300 02,...,08030010 ..
and go on like that
and also the year increase(2009) :09010001,..090 10010
Please help me to do that
thanks so much
Regards.
Hoang Phuc

Hi there,

Do you mean that each month you want to reset the customer number back to 0001? So an example of two months worth of ID's might be:

08020001, 08020002, 08030001, 08030002, 08030003 ?

Dr B

**phuc2583** · Feb 14 '08, 09:36 AM

Correctly!
Can you show me to do that or you know another the page that write the same of my code.
Thank you so much to your help
I am very fun if you help me

**CroCrew** · Feb 15 '08, 03:24 PM

Hello phuc2583,

Here is one working example (Example.asp) that you could base your code on. I did not know what type of database that you are using so I just did it in Access (let me know if you want a copy of the database).

First the database has six fields all text fields but the “KeyId” field that is an auto numbering field. An example of a record could look like this:

KeyId = 392
CustomerYear = 08
CustomerMonth = 02
CustomerNumber = 5
CustomerName = Billy Bob Smith
CustomerID = 08020005

Here is the code.

Example.asp
[code=asp]
<%
CurrentYear = Right(DatePart( "yyyy", Now()), 2)
CurrentMonth = Right("0" & DatePart("m", Now()), 2)

Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Open "DRIVER={Micros oft Access Driver (*.mdb)}; DBQ=" & Server.MapPath( "Database.m db")
Set rsList = Server.CreateOb ject("ADODB.Rec ordset")

If (Request.Form(" yActionFind")) Then
SQL = "SELECT KeyId, CustomerYear, CustomerMonth, CustomerNumber, CustomerName, CustomerID FROM TheTable"
SQL = SQL & " WHERE CustomerName LIKE '%" & Request.Form("y Name") & "%'"
Else
SQL = "SELECT Top 1 * FROM TheTable WHERE CustomerYear = '" & CurrentYear & "' AND CustomerMonth = '" & CurrentMonth & "' ORDER BY KeyId DESC"
End If

rsList.CursorTy pe = 1
rsList.LockType = 3
rsList.Open SQL, Conn

If (Request.Form(" xActionNew")) Then
NewCustomerNumb er = (rsList("Custom erNumber")+1)
NewCustomerID = CurrentYear & CurrentMonth & Right("0000" & NewCustomerNumb er, 4)
rsList.AddNew
rsList.Fields(" CustomerYear") = CurrentYear
rsList.Fields(" CustomerMonth") = CurrentMonth
rsList.Fields(" CustomerNumber" ) = NewCustomerNumb er
rsList.Fields(" CustomerName") = Request.Form("x Name")
rsList.Fields(" CustomerID") = NewCustomerID
rsList.update
End If
%>
<html>
<head>
<title>Exampl e</title>
</head>
<body>
<form method="post" action="Example .asp" name="xForm" id="xForm">
<input type="hidden" name="xActionNe w" value="true">
New Customer Name: <input type="text" name="xName" size="50">
<input type="submit" value="Add">
</form>
<form method="post" action="Example .asp" name="yForm" id="yForm">
<input type="hidden" name="yActionFi nd" value="true">
Find Customer By Name: <input type="text" name="yName" size="50">
<input type="submit" value="Find">
</form>

<%If (Request.Form(" yActionFind")) Then%>
<%If (rsList.EOF) Then%>
<b>No customer was found!</b><br>You can search by partial name.
<%Else%>
<%Do Until (rsList.EOF)%>
Customer <%=rsList("Cust omerName").valu e%>'s CustomerID is <%=rsList("Cust omerID").value% >.<br>
<%rsList.MoveNe xt%>
<%Response.Flus h%>
<%Loop%>
<%End If%>
<%Else%>
<%If (Request.Form(" xActionNew")) Then%>
Customer <%=Request.Form ("xName")%> has been added with the CustomerID of <%=NewCustomerI D%>.
<%End If%>
<%End If%>
</body>
</html>
[/code]

Hope this helps you out~

**phuc2583** · Feb 16 '08, 01:45 AM

Thanks so much to your help.I have done it
Regards
Hoang Phuc

**CroCrew** · Feb 16 '08, 02:22 PM

Your welcome and happy coding~

**phuc2583** · Feb 18 '08, 02:26 AM

thanks so much to your help me to do
but now i want add the day and OrderId:0802180 01
anh then it automatic
but not run
please check the code:

if trim(request("O ption"))="AddOr der" then
Set rs = Server.CreateOb ject("ADODB.Rec ordset")
' rs.Open "orderdetai l", conn, 1, 2
rs.CursorType = 1

rs.LockType = 3

rs.Open "orderdetai l", Conn

' Adding data
rs.AddNew
if not rs.eof then
strmonth=Right( "0" & DatePart("m", Now()), 2)
strday=day(date ())
stryear=Right(D atePart("yyyy", Now()), 2)
NewOrderNumber= (rs("orderID")+ 1)
NewsOrderID=str year & strmonth & strday & Right("0000" & NewOrderNumber, 4)
if not rs.eof then

'OrderID=sessio n("OrderID")
NewOrderNumber= (rs("orderID")+ 1)
OrderID=Newsord erID

end if

rs("OrderID")= NewsorderID
rs("CustomerId" ) = CustomerId
rs("Item") = Items
rs("Description ") = Descriptions
rs("Quantity")= Quantity
rs("price") = price
rs("UnitCurrenc y") = UnitCurrency
rs("CreateDate" ) = CreateDate
rs("ExpirationD ate") = ExpirationDate
response.Write( OrderID)
'rs.Update
end if
session("Neword erid")=Newsorde rID
session("Item") =items
session("Descri ptions")=Descri ptions
session("Quanti ty")=Quantity
session("price" )=price
session("UnitCu rrency")=UnitCu rrency
session("Create Date")=CreateDa te
session("Expira tionDate")=Expi rationDate

response.Redire ct "ShowOrderdetai l.asp"
rs.Close
Set rs = Nothing
end if

**phuc2583** · Feb 18 '08, 02:32 AM

The table is only one field :CustomerID
that all hold the value:Year,Mont h,number
and it come such:08020001
after i submit it that save it.
CustomerId:auto matic number
Thanks

**CroCrew** · Feb 18 '08, 01:08 PM

Hello phuc2583,

I would like to help you out. But, can you explain in more detail what you’re trying to do. The more information that you can give the better solution this site will then be able to provide to you. Take your time in explaining.

Thanks~

**phuc2583** · Feb 19 '08, 03:34 AM

Your help has showed me to do that better
but i try do it that my idea is:
In my database,i have a table(is name OrderID)
field:OrderID :number
Item :text
Description :text
that save all by access
the code that i wrote,i meet some problem
Ex;OrderID:0802 19002,080219005 ....
but if i submit then that save 080219003,
order by increase (1..10...)
but not run...
please check my code;
if trim(request("O ption"))="AddOr der" then
Set rs = Server.CreateOb ject("ADODB.Rec ordset")
rs.Open "orderdetai l", conn, 1, 3
' Adding data

rs.AddNew
if not rs.eof then
strmonth=Right( "0" & DatePart("m", Now()), 2)
strday=day(date ())
stryear=Right(D atePart("yyyy", Now()), 2)
'whole=stryear& strmonth&strday

'OrderID=whole& "0000"
if not rs.eof then
Set rs = Server.CreateOb ject("ADODB.Rec ordset")
rs.Open "orderdetai l", conn, 1, 3
OrderID =session("Order ID")
' OrderID=whole+" 0000"
rs.movelast
' OrderID=OrderID +1
NewOrderNumber= (rs("orderID")+ 1)
OrderID=stryear & strmonth & strday & Right("0000" & NewOrderNumber, 4)
end if
rs("OrderID") = OrderID
'rs.movefirst
rs("CustomerId" ) = CustomerId
rs("Item") = Items
rs("Description ") = Descriptions
rs("Quantity")= Quantity
rs("price") = price
rs("UnitCurrenc y") = UnitCurrency
rs("CreateDate" ) = CreateDate
rs("ExpirationD ate") = ExpirationDate
' rs.Update

session("OrderI D")=OrderID
session("Item") =items
session("Descri ptions")=Descri ptions
session("Quanti ty")=Quantity
session("price" )=price
session("UnitCu rrency")=UnitCu rrency
session("Create Date")=CreateDa te
session("Expira tionDate")=Expi rationDate

response.Redire ct "ShowOrderdetai l.asp"
end if
rs.Close
Set rs = Nothing
end if
thanks so much

**CroCrew** · Feb 19 '08, 01:33 PM

I am very sorry. I think we are having a communication problem because I’m still not understanding your question/problem. So I am going to make a stab at what you might be asking for help on.

If you’re looking to add the current day to the “CustomerID” so that the new format of CustomerId looks like this:

[Year][Month][Day][Auto Number]
[yy][mm][dd][0000]

To get the day in the format that you seem to need it in try this:

[code=asp]
strday=Right("0 " & DatePart("d", Now()), 2)
[/code]

If that is not what you’re looking for then try to explain what you want but this time don’t use any code in your explanation.

Hope this helps~

**phuc2583** · Feb 20 '08, 08:57 AM

if trim(request("O ption"))="AddMo reOrder" then
Set Con = Server.CreateOb ject("ADODB.Con nection")
Set rs = Server.CreateOb ject("ADODB.Rec ordset")
data_source = "Provider=Micro soft.Jet.OLEDB. 4.0; Data Source=" & _
server.mappath( "db/customer.mdb")
Con.Open data_source
rs.Open "Orderdetai l", conn, 1, 3
' Adding data
strmonth=Right( "0" & DatePart("m", Now()), 2)
strday=Right("0 " & DatePart("d", Now()), 2)
stryear=Right(D atePart("yyyy", Now()), 2)
rs.AddNew

if not rs.eof then
Set rs = Server.CreateOb ject("ADODB.Rec ordset")
rs.Open "select * from Orderdetail", conn, 1, 3
NewsOrderId=rs( "OrderID")+ 1
'rs.movelast()
OrderID=stryear &strmonth&strda y&right("0000"& NewsOrderId,4)
end if

rs("OrderID") = OrderID

rs("CustomerId" ) = request.Form((" CustomerId"))
rs("Item") = Items
rs("Description ") = Descriptions
rs("Quantity")= Quantity
rs("price") = price
rs("UnitCurrenc y") = UnitCurrency
CreateDate=requ est.Form("day1" )&"/"&request.Form( "month1")&"/"&request.Form( "year1")
rs("CreateDate" ) = CreateDate
ExpirationDate= request.Form("d ay2")&"/"&request.Form( "month2")&"/"&request.Form( "year2")
rs("ExpirationD ate") = ExpirationDate
rs.Update
response.Write( OrderID)
rs.close
set rs=nothing
' end if

session("OrderI D")=OrderID
session("Item") =items
session("Descri ptions")=Descri ptions
session("Quanti ty")=Quantity
session("price" )=price
session("UnitCu rrency")=UnitCu rrency
session("Create Date")=CreateDa te
session("Expira tionDate")=Expi rationDate

' response.Redire ct "ShowOrderCusto mer.asp"
'rs.Close
' Set rs = Nothing

end if
after i submit that not save and increase number
all the code that i wrote but not run
Thanks so much

**CroCrew** · Feb 20 '08, 02:03 PM

Hello phuc2583,

Please edit your last post or repost and follow the posting guidelines by adding the code tags before and after your pasted code so your post looks cleaner. This will also add line numbers to each line. Posting guidelines can be found to the right of the input box when you’re typing your post/message.

I see a few problems in your code but I need for you fix your last post first before I can help. Don’t forget to preview your post before submitting/saving it.

I/we will be waiting~

**phuc2583** · Feb 21 '08, 10:15 AM

that great?
i have completed and it run very good
this add one var session()
Thanks
Regards
Hoang Phuc

save datetime and inscrease numbers

save datetime and inscrease numbers

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment