UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Check Date To Be Between 5am And 5am 24 Hour Period, Access 2016    
 
   
SemiAuto40
post Mar 2 2020, 03:37 PM
Post#1



Posts: 745
Joined: 3-April 12
From: L.A. (lower Alabama)


I have users putting in the wrong (albeit actual computer system date) date at times, so what I want to do is to check that a new batch starting at 2.am actually gets the date that the shift started working which was 5am the morning before. How in the world do I do this?

Thanks in advance.
Go to the top of the page
 
strive4peace
post Mar 2 2020, 04:08 PM
Post#2


strive4peace
Posts: 20,532
Joined: 10-January 04



to get the previous day, you can subtract 1 from Date to be yesterday.
then you can use DateAdd to add 5 hours for 5 am

syntax is:
DateAdd(interval, number, date)
CODE
DateAdd ("h", 5, Date()-1)

for the end date, you can either add 1 to that, or use DateAdd with "d" for the interval and 1 for the number, or just use Date() without subtracting 1, and add 5 hours


This post has been edited by strive4peace: Mar 2 2020, 04:12 PM

--------------------
have an awesome day,
crystal

Microsoft MVP
Remote Training and Programming -- let's connect and build your application together! MsAccessGurus.com
~
video: Bar Graphs in Access Query using Unicode
Go to the top of the page
 
SemiAuto40
post Mar 2 2020, 04:27 PM
Post#3



Posts: 745
Joined: 3-April 12
From: L.A. (lower Alabama)


My first step is to get the users date for example 3/2/20, then I have to find out if the user time is between midnight and 4:59am. If the time is between then I have to take 1 day off the date reported by the PC, and set the user date back 1 day.
Go to the top of the page
 
strive4peace
post Mar 2 2020, 04:35 PM
Post#4


strive4peace
Posts: 20,532
Joined: 10-January 04



is date/time not stored together?
to see if time, in a control by itself, is less than 5am, assuming it is bound to a field with date/time as the data type, you can do this:
CODE
If me.myTimeControlname < timeserial(5,0,0) then


This post has been edited by strive4peace: Mar 2 2020, 04:37 PM

--------------------
have an awesome day,
crystal

Microsoft MVP
Remote Training and Programming -- let's connect and build your application together! MsAccessGurus.com
~
video: Bar Graphs in Access Query using Unicode
Go to the top of the page
 
strive4peace
post Mar 2 2020, 04:37 PM
Post#5


strive4peace
Posts: 20,532
Joined: 10-January 04



what are your field names? control names?

--------------------
have an awesome day,
crystal

Microsoft MVP
Remote Training and Programming -- let's connect and build your application together! MsAccessGurus.com
~
video: Bar Graphs in Access Query using Unicode
Go to the top of the page
 
Gustav
post Mar 3 2020, 03:39 AM
Post#6


UtterAccess VIP
Posts: 2,268
Joined: 21-February 07
From: Copenhagen


You can offset the current time by five hours and then take the date value only:

CODE
CurrentShiftDate = DateValue(DateAdd("h", -5, Now))

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
SemiAuto40
post Mar 3 2020, 01:05 PM
Post#7



Posts: 745
Joined: 3-April 12
From: L.A. (lower Alabama)


The batch sheet has among others, a control batchStartDate and a control batchStartTime. The customer separates the date and time into two fields. I expect the most simple solution is the
CODE
CurrentShiftDate = DateValue(DateAdd("h", -5, Now))
. The time is the time no matter what shift is working, but the date is what would change to be the previous computer date were the batch data entered between 12:01am - 4:59am.

I'm thankful for all of the directions uarulez2.gif
Go to the top of the page
 
strive4peace
post Mar 3 2020, 01:31 PM
Post#8


strive4peace
Posts: 20,532
Joined: 10-January 04



glad you got something you're happy with! Thanks for sharing ~

--------------------
have an awesome day,
crystal

Microsoft MVP
Remote Training and Programming -- let's connect and build your application together! MsAccessGurus.com
~
video: Bar Graphs in Access Query using Unicode
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th July 2020 - 04:37 PM