[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

add one day to date on unbound access form

I have a simple form with a date picker.  I am trying to run a macro that has 2 queries that need date ranges .  The first query will run from the dates the user types in.  I want a second invisible set of controls that add one day to the dates the user puts in that the second query picks up.  Do the "invisible" controls use the first set of controls as a control source?  I need some logic so that I have ctlStartDate, ctlEndDate; and  then ctlStartDatePlusOne=(d,1[ctlStartDate]) and ctlEndDatePlusOne=(d,1[ctlEndtDate])

LindaOKState
0
LindaOKSTATE
Asked:
LindaOKSTATE
  • 8
  • 6
  • 4
2 Solutions
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
You could do the calculation in the query using form references

Something lik3 this:
Where [mydate] between forms![myformname].ctlStartDate] and (forms![myformname].ctlStartDate +1)


TIP: to add a day to a date you can use ctlStartDate +1
0
 
LindaOKSTATEAuthor Commented:
Here are the two criteria that are in query 1 and query 2

Query1
>=Format([Forms]![frm_Select Date Range]![ctlStartDate],"yyyymmdd") And <=Format([Forms]![frm_Select Date Range]![ctlEndDate],"yyyymmdd")

Query 2
>=Format(DateAdd("d",1,[Forms]![frm_Select Date Range]![ctlStartDate]),"yyyymmdd") And <=Format(DateAdd("d",1,[Forms]![frm_Select Date Range]![ctlEndDate]),"yyyymmdd")

As of now the date picker comes up twice (so the user has to type in 4 dates).  They type the same dates in twice and the query adds one day to the second query.  Since the original field is a string, to add a day it must be formated to a date.  So, how do I format this so it knows to add one day to the second set of queries?
0
 
Nick67Commented:
Hey Linda,

I like your first instinct best!
Me.ctlEndDate = Format(DateSerial(Year(Me.ctlStartDate) , Month(me.ctlStartDate), Day(me.ctlStartDate)+1), "dd-mmm-yyyy")

Keep the calculating in a hidden control and out of the query.  The query will be hairy enough.
Throw this in the afterupdate event of ctlStartDate

See the +1?
Any integer + or - in the year, the month or the date to get a date calculation going
One year
Me.ctlEndDate = Format(DateSerial(Year(Me.ctlStartDate)+1 , Month(me.ctlStartDate), Day(me.ctlStartDate)), "dd-mmm-yyyy")
one month
Me.ctlEndDate = Format(DateSerial(Year(Me.ctlStartDate) , Month(me.ctlStartDate)+1, Day(me.ctlStartDate)), "dd-mmm-yyyy")

One year, 4 months and 3 days from the date in the control
Me.ctlEndDate = Format(DateSerial(Year(Me.ctlStartDate)+1 , Month(me.ctlStartDate)+4, Day(me.ctlStartDate)+3), "dd-mmm-yyyy")
The first day of next month
Me.ctlEndDate = Format(DateSerial(Year(Me.ctlStartDate) , Month(me.ctlStartDate)+1, 1), "dd-mmm-yyyy")

Catch my drift?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Curious, why ask for the dates twice?

<<Since the original field is a string, to add a day it must be formated to a date.  So, how do I format this so it knows to add one day to the second set of queries?>>
DateAdd("d",1,[Forms]![frm_Select Date Range]![ctlStartDate]) does add one day to the date entered.

Is  ctlStartDate and ctlEndDate  a text string where the date is entered as yyyyddmm or iois it a date entered in mm/dd/yyyy format?

To convert a string in teh format yyyymmdd to a date data type I like to use the dateSerial function().

Example:

? DateSerial(left("20110509",4),mid("20110509",5,2),right("20110509",2))
5/9/2011 

Open in new window










0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
To get eh next day simple add 1 like this:

? DateSerial(left("20110509",4),mid("20110509",5,2),right("20110509",2)) + 1
5/10/2011 

Open in new window

0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
@Nick67,

The reason I suggest a query is that LindaOKState said " I am trying to run a macro" which generally means not a  VBA code solution is desired.

I agree that VBA code is better like you have suggested.

How would you do what you are recommending with a Macro?
0
 
LindaOKSTATEAuthor Commented:
I put this in:

Me.ctlStartDatePlusOne=Format(DateSerial(Year(Me.ctlStartDate), Month(Me.ctlStartDate), Day (Me.ctlStartDate)+1),"yyyymmdd")

It says the the after-update doesn't control the automation object
0
 
LindaOKSTATEAuthor Commented:
The first query gets records for a date and pastes them to temp table, the next query deletes the fields that do not have the correct date, and a third query is asking for same date but adds a day and appends those fields back to the temp table.  so we have one record with data from two dates.  but when i try to run the three queries through a macro the date picker comes up twice so I want to put the form at the top of the macro, have the dates for the first query be typed in and the second set be invisible to the user.
0
 
LindaOKSTATEAuthor Commented:
It says the the after-update doesn't control the automation object
0
 
Nick67Commented:
Here it is shimmed up.

I am not sure why you're getting the error.
Try the code in the sample
Dates.mdb
0
 
LindaOKSTATEAuthor Commented:
0
 
LindaOKSTATEAuthor Commented:
I get the OLE object error in mine.  But, I did see that the picker did place the dates in both the start and end fields.  
error2.jpg
0
 
Nick67Commented:
Give me a bit.
I am at home on Access 2010.
I'll punch the sample to work and open it in 2003 and sort the small issues out, and then we'll see
0
 
Nick67Commented:
Ok, here it is compiled in 2003.
It should nicely upgrade to 2007, and then let's see if it throws any errors.

Does the code work to update the textboxes?
Option Compare Database
Option Explicit

Private Sub ctlStartDate_AfterUpdate()
Me.ctlStartDatePlusOne = Format(DateSerial(Year(Me.ctlStartDate), Month(Me.ctlStartDate), Day(Me.ctlStartDate) + 1), "yyyymmdd")
Me.ctlStartDate = Format(DateSerial(Year(Me.ctlStartDate), Month(Me.ctlStartDate), Day(Me.ctlStartDate)), "yyyymmdd")
End Sub

Open in new window

Dates.mdb
0
 
LindaOKSTATEAuthor Commented:
Been fixing bugs and made some progress.  But how do I get the control data into the query?  I put this in the criteria field  >=[ctlStartDate] And <=[ctlEndDate], but it sends up a parmater box asking for ctlStartDate.
0
 
Nick67Commented:
It HAS to be this

between forms!frmWhateverYourFormNameIs!ctlStartDate and forms!frmWhateverYourFormNameIs!ctlStartDatePlusOne

AND only works like this when used with BETWEEN

Used without BETWEEN, it is the logical operator AND

The results may therefore not be what you expect
0
 
LindaOKSTATEAuthor Commented:
Ok, I got that figured out, now having problems with macro group or whatever.  I'm done for tonight, this whole report and the subreport we were working on last friday have totally worn me out.  I will post again tomorrow, and stop back by if you have time,

Thanks so much,
LindaOSU
0
 
Nick67Commented:
Okey dokey
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now