By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,885 Members | 803 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,885 IT Pros & Developers. It's quick & easy.

Saving current date but fixed time with every record

P: 3
Dear All
I am creating a database where I need to save current date with each record. So I can set default value as Date(). However, I want when I create new record, every time system should save today's date with fixed time (15:00:00). e.g. 16/10/2020 15:00:00. So, if tomorrow I will create another record, it should be 17/10/2020 15:00:00. I'm not able to figure out how to fix the time with dynamic dates.
thanks
regards
4 Days Ago #1
Share this Question
Share on Google+
6 Replies

100+
P: 186
If you just want to correct the date, you can use the following processing.
When storing in DB as string type, use the variable "s".
When storing in DB as Date type, use the variable "dt".
Expand|Select|Wrap|Line Numbers
  1.     Dim d As Date
  2.     Dim dt As Date
  3.     Dim s As String
  4.     d = Date
  5.     s = Format(d, "dd/MM/yyyy") & " 15:00:00"
  6.     dt = CDate(s)
If you want to make changes to the DB at that time, use the OnTime method of the Application object.
3 Days Ago #2

P: 3
Thanks for prompt reply.
Can you please guide Where should I put this code in a form? I also could not understand about OnTime method of Application Object, how I can make use of it.
thanks again
regards
3 Days Ago #3

cactusdata
Expert 100+
P: 122
Use the OnCurrent event of the form to call these code lines to set the DefaultValue property of the field or textbox.

The DefaultValue is always text, so format the date value properly:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.     Dim DefaultDate     As Date
  4.     Dim DefaultValue    As String
  5.  
  6.     DefaultDate = DateAdd("h", 15, DateValue(DateAdd("h", 9, Now)))
  7.     DefaultValue = "#" & Format(DefaultDate, "yyyy\/mm\/dd hh\:nn\:ss") & "#"
  8.  
  9.     Me!NameOfYourDateField.DefaultValue = DefaultValue
  10.  
  11. End Sub
3 Days Ago #4

100+
P: 186
I wrote that the OnTime function is used in # 2, but since Access does not have the OnTime function, I will create the OnTime function with VBA of Excel (Excel_OnTime).
Write a subroutine that calls this from Access (Call_xls_OnTime), and call the subroutine that registers the Access record in it.
(Access-> Excel-> Access)
I don't recommend it because it's complicated.

Access code
Expand|Select|Wrap|Line Numbers
  1. Public Sub Call_xls_OnTime()
  2.     Dim xlApp As Object
  3.     Dim xlWbk As Object
  4.     Set xlApp = CreateObject("Excel.Application")
  5.     Set xlWbk = xlApp.Workbooks.Open("Excel file's full path(Ex. c:\test\xxx.xlsm)")
  6.     xlApp.OnTime TimeValue("15:00:00"), "Excel_B"
  7.     xlApp.OnTime TimeValue("15:00:00"), xlWbk.Name & "!module_name.macro_name(Ex. module1.Excel_OnTime)" 'The module_name is the name of the module where VBA resides.
  8.     xlApp.Quit
  9. End Sub
  10.  
  11. Public Sub Add_Record()
  12.     'record add code
  13. End Sub
  14.  
Excel code
Expand|Select|Wrap|Line Numbers
  1. Public Sub Excel_OnTime()
  2.     Dim acApp As Object
  3.     Set acApp = GetObject("Access mdb file name(Ex. xxx.mdb)")    
  4.     'Run the "Command B" vba created by xxx.mdb
  5.     acApp.Application.Run "Add_Record"
  6.     Set acApp = Nothing
  7. End Sub
1 Days ago #5

twinnyfo
Expert Mod 2.5K+
P: 3,538
There is actually a much easier way to do this. Using Catusdata's code as an outline:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Dim DefaultDate     As Date
  3.  
  4.     DefaultDate = Date + (15/24)
  5.     Me!NameOfYourDateField.DefaultValue = DefaultValue
  6.  
  7. End Sub
This looks kind of silly, but it should work. The Date() Function returns just the date, which is a whole number value (a long integer). However, Date Values determine time by using a decimal/fraction of a whole day. Thus, [Date Value].5 = noon on that date. The value for 15/24 gives us the 15th hour of one day. Access will calculate that decimal portion and add it to the whole number date.

This work very well and easily unless you have extremely strange times that you want to assign. If you are always using standard hours (even quarter hours), this method will work and very simply.

Hope that hepps!
22 Hours Ago #6

cactusdata
Expert 100+
P: 122
I assumed, that after 15:00 until 00:00, the date of tomorrow should be used.
21 Hours Ago #7

Post your reply

Sign in to post your reply or Sign up for a free account.