• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

unique number from current date

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
anushahanna
Asked:
anushahanna
  • 4
  • 3
  • 2
  • +2
4 Solutions
 
Paul_Harris_FusionCommented:
dim f as double
f = CDbl(Now())

This will give you a decimal number corresponding to the current date time.
0
 
peter57rCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Date with Time is already unique.

mx
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
anushahannaAuthor Commented:
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
 
peter57rCommented:
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
 
anushahannaAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
anushahannaAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
pteranodon72Commented:
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
 
anushahannaAuthor Commented:
thanks for the idea.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now