Solved

unique number from current date

Posted on 2011-03-03
11
280 Views
Last Modified: 2012-05-11
like the following in t-sql
declare @tempdate varchar(23)
set @tempdate = CONVERT(VARCHAR(23), GETDATE(), 121)
select replace(replace(replace(replace(@tempdate,'-',''),' ',''),':',''),'.','')

what would be the feature to get a unique number from date in access?
0
Comment
Question by:anushahanna
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 12

Accepted Solution

by:
Paul_Harris_Fusion earned 125 total points
ID: 35028729
dim f as double
f = CDbl(Now())

This will give you a decimal number corresponding to the current date time.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 125 total points
ID: 35028781
I guess you can do...
cdbl(Now())*10000000000

I wouldn't like to guarantee uniqueness, although I don't see that your t-sql expression does that either.
0
 
LVL 75
ID: 35029588
Date with Time is already unique.

mx
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35029622
would we do something with date and time in access to produce a unique number? basically we will need the equivalent of
GETDATE()
CONVERT
REPLACE
in access code to make it happen?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35030067
I was interested in mx's comment.

I think it is very likely that the STORED value in a datetime field is unique in an application.  However, as soon as you do what the poster wants it ceases to be unique because the time 'granularity' is broadened - so that stored time values which differ at the nth decimal place all get rounded to the same second.

And to expand on my previous comment - I don't see that a time value which only goes to milliseconds can be thought of as unique.  Access could probably save 5 records in a millisecond.  I would imagine sql server can do more.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 6

Author Comment

by:anushahanna
ID: 35030240
Thanks for explaining that Peter.

if i can go to ms in access, that is good enough for this application.

how can you get it to the ms with format command?

    Dim DateValue As String
    DateValue= Format(Now(),"dd/mm/yyyy hh:nn:ss")
0
 
LVL 75
ID: 35030973
I guess it depends on what the usage is, which was not specified.
I suppose you could always append or add the Access Autonumber to it.

mx
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35031127
mx, every csv files that is processed needs a unique number attached, when the data is imported into the table.

how would you attach now() and autonumber together?
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 125 total points
ID: 35031264
I guess the table would have an Auto Number field, and you create a combined field with the Formatted Date/Time EG >>> yyyymmddHHnnss & AutoNumber value .... something like that.  Then, no matter if the date/time stamp was identical as Peter noted, you still end up with a unique value.

mx
0
 
LVL 14

Assisted Solution

by:pteranodon72
pteranodon72 earned 125 total points
ID: 35031480
Now() does not report milliseconds.

However, you can get date/time in milliseconds via API call:
Private Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Private Declare Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)

Public Function MSdatetime() As String
Dim tS As SYSTEMTIME
Dim sRet
On Error Resume Next
GetSystemTime tS
sRet = tS.wYear & Format(tS.wMonth, "00") & Format(tS.wDay, "00") _
& Format(tS.wHour, "00") & Format(tS.wMinute, "00") & Format(tS.wSecond, "00") _
 & Format(tS.wMilliseconds, "000")
MSdatetime = sRet
End Function

Open in new window


By formatting each component values into two places, you'll get different numbers with millisecond granularity. Be careful-- today's processors can do a lot in a millisecond!

HTH,

pT72
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35033594
thanks for the idea.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now