Adding days to Now() but with specific times

[OLEYNEK]

Hello! First off... I'm not a "programmer". I've created hundreds of databases with MSACCESS, but primarily only using the wizards and tools with very little VBA or coding. I have a problem that I cannot wrap my head around. I have a form (Create Med Plan) which needs to take 2 values, #Days and X/Day and create a future plan for meds. For example, if #Days = 3 and X/Day = 2, the med should be given at 8am and 8pm for 3 days beginning on the following day. For this I need to create 6 records in a table, Med Plan, with the specific days and times to be administered where the day and time is one Day/Time field. I can add the day with no problem using DateAdd, but cannot then add the specific time in that fields using an update query. I'm lost. I know the answer is VBA, but wouldn't know where to begin. If anyone can help, Id be forever grateful!

 

[June7]

Yes, VBA using a loop to save the number of desired records. Want to provide database for analysis? Follow instructions at bottom of my post. Would make it easier to provide code specific to your db.

Tricky part is determining what times of day to use for the frequency of daily doses. How is this determined per patient? What would be times for 3 X/Day.

Advise not to use spaces nor symbols (underscore only exception) in naming convention.

 

[cheekybuddha]

I don't know whether what you are trying to do is the best way of going about it, but to get it to work you would use:

DATE: DateAdd("d",1,Date()) + #08:00:00#

(Also, using DATE as a field name, even in a query, is unwise)

 

[Gasman]

Do not use the day argument, use the hour argument.

 

[OLEYNEK]

You guys rock!!! Thank you. Every fiber of my being avoids VBA and I already had the query structures in place, so I went with the ### instead of """, and I'm good! I know... rookie mistake... but I'm a rookie, so....

 

[June7]

Gasman, why would you suggest hour argument to add a day?

Oleynek, how does your non-VBA solution create 6 records with various date/time values? Why would you need triple #?

 

[OLEYNEK]

In a very non-database-savy way, I created queries for each scenario and then ran them with the following. It's not pretty or efficient, but it works for this very small database, and I'm happy.

 

[Gasman]

Gasman, why would you suggest hour argument to add a day?

Oleynek, how does your non-VBA solution create 6 records with various date/time values? Why would you need triple #?

Add 32 hours to date?
That would make it 0800 the next day, would it not?

 

[cheekybuddha]

Gasman, why would you suggest hour argument to add a day?

It's not a bad suggestion.

One operation instead of 2:

DATE: DateAdd("h",32,Date())

 

[OLEYNEK]

The problem with the hour argument is that the time needs to be specific and not determined relative to the time of the entry. Sorry... does that make sense?

 

[Mark_]

As this is a med plan, you will want to be able to define the "Start time" based on patient needs and medication. Some medications are recommended as 1/day at bed time.

 

[Gasman]

The problem with the hour argument is that the time needs to be specific and not determined relative to the time of the entry. Sorry... does that make sense?

It is not relative to time entered. It is using Date(). Now if it used Now() (pun intended ) then it would take into account time of entry.

 

[June7]

Exactly how many query objects are there? How may days does your code allow for?

Again, strongly advise not to use spaces nor symbols (underscore only exception) in naming convention.

Also, best to avoid Screen.ActiveForm - use explicit reference to form or use Me. And don't need to repeat form reference, wrap all in a With block and use ElseIf:

With Me
If .[textboxName] = something Then
     'do this
ElseIf .[textboxName] = something else Then
     'do this
End If
...
End With

Actually, Select Case would be even more efficient.

Select Case Me.[textboxName]
      Case something
           'do this
      Case something else
           'do this
End Select

Instead of 20+ query objects, I would dynamically build SQL statement in VBA and use CurrentDb.Execute to create record. It would allow for any number of days and slight modification to add daily frequency options. Your code could be reduced (and all those objects deleted) to something like:

Sub CreateRecs(days As Integer, freq As Integer)
Dim db As DAO.Database
Dim x As Integer, y As Integer, t As Integer
Dim d As Date
Set db = CurrentDb
For x = 1 To days
    For y = 1 To freq
        Select Case freq
            Case 1
                t = 8
            Case 2
                t = Choose(y, 8, 20)
            Case 3
                t = Choose(y, 8, 14, 20)
            Case 4
                t = Choose(y, 8, 12, 16, 20)
        End Select
        d = Date + x + CDate(t & ":00:00")
        db.Execute "INSERT INTO Schedule(ScripID, MedDateTime) VALUES(" & Me.ScripID & ",#" & d & "#)"
    Next
Next
End Sub

 

[The_Doc_Man]

The problem with the hour argument is that the time needs to be specific and not determined relative to the time of the entry. Sorry... does that make sense?

Pretty much. You are printing up instructions for a staff member to administer meds to someone under professional care, or something like that.

A DATE data type in Access is a DOUBLE (scientific) number that has been adapted to represent a date and time. (We call the adaptation a "TYPECAST", where one type has been cast in a different role.) The DATE field's number, like any scientific number, has two parts - an integer and a fraction. The integer is days since a reference date. The fraction is the fraction of the day with midnight being 0 and noon being 0.5, and also note that though a DOUBLE has enough bits to store fractions of a second, the formatting routines stop at seconds either way. And from your general description, it would appear to me that even seconds don't make a difference.

Date/Time data type (Format property)

Office VBA reference topic

learn.microsoft.com

You have several choices.

If you want to just display a time of 8:00 AM, use FORMAT( (8/24), "Medium Time" ) and for evening times such as 8:00 PM, use FORMAT( (20/24), "Medium Time" ) (i.e. 8 PM in military time is 20:00) If you have a DATE field or value and you want to SET the time, it takes two parts in the expression: datetimefield = DATE( date-field-value ) + (8/24). The DATE function strips only the time off of the DATE field/value, leaving it at midnight of that date. Then you can add back the fraction. Unfortunately, if your fraction is more complex, like "half past 9 AM" then the fraction gets uglier.

As an alternative you can generate all of this by just having string constants available that you just tack on to the text form of the date, if that is what you need. I.e. if this is only needed for printing, then store the required constants and pick the one you need.

If you really, really want to make the date variable include the time and you wanted to add it, the correct expression involves something called a type tag. For dates, it is the pound-sign or octothorpe (#). There are other tags, but this is the one I think YOU need.

datefield = DATE( date-field-value) + "#08:00:00#"

The reason that you got nowhere with + "08:00:00" is because that is a STRING data type that you are trying to add to a DATE data type and you get a type mismatch. Including the type hint tags makes the content of that string be interpreted as a time of day.

 

[Gasman]

Doc, do you mean DateValue() ?

 

[The_Doc_Man]

Doc, do you mean DateValue() ?

Damnit, my 28 1/2 years using VAX BASIC did it to me again. That DATE() function is different for VAX BASIC and VBA.

Instead of DATE(X) to strip time from a DATE variable, use CDATE(LONG(date-variable)).

Note that DATEVALUE() wants to convert a STRING input to a DATE value. So in that context, no. I did NOT mean DATEVALUE(). I wanted to convert a date possibly with time to a date at midnight. The LONG data type has enough bits that a proper DATE variable will not overflow the LONG data type. (This is partly because Access won't let you work with dates after 31-Dec-9999.)

 

[cheekybuddha]

DATE() converts a DATE data type input (with or without times) into a pure date, a DOUBLE field holding an integer number of days and no fractions

Did you test that, Doc?

 

[DHookom]

I would use a data driven solution (maybe a couple lines of code) where the number per day and times are stored in related tables. A plan with twice a day at 8 AM and 8 PM become a record in tblMedPlans with two related records in tblMedPlanTimes with TimeOfDay of 8:00 and 20:00. There is also a tblDates with every date ever needed.

Then your Create Med Plan form has text boxes for the start date, Number of Days, end date (calculated), and a combo box to select the med plan.

The subform has a record source of:

SELECT tblDates.Dt, tblMedPlanTimes.TimeOfDay
FROM tblDates, tblMedPlanTimes
WHERE tblDates.Dt Between [Forms].[frmCreateMedPlan].[txtStartDate] And
   [Forms].[frmCreateMedPlan].[txtEndDate] AND
   tblMedPlanTimes.pldMPlID=[Forms].[frmCreateMedPlan].[cboMedPlan]
ORDER BY tblDates.Dt, tblMedPlanTimes.TimeOfDay;

You should add line of code to requery the subform and code to run an append query based on the same SQL.

When you want to add med plans, it's all data with no code modifications.

 

[The_Doc_Man]

Did you test that, Doc?

See my reply to Gasman.

 

[Gasman]

Damnit, my 28 1/2 years using VAX BASIC did it to me again. That DATE() function is different for VAX BASIC and VBA.

Instead of DATE(X) to strip time from a DATE variable, use CDATE(LONG(date-variable)).

Note that DATEVALUE() wants to convert a STRING input to a DATE value. So in that context, no. I did NOT mean DATEVALUE(). I wanted to convert a date possibly with time to a date at midnight. The LONG data type has enough bits that a proper DATE variable will not overflow the LONG data type. (This is partly because Access won't let you work with dates after 31-Dec-9999.)

I took DateValue to just extract the date from a datetime field. Just like TimeValue extracts just the time?