Access date to next day

I am keeping track of blood pressure here for a friend who is in China. I get the bp on my phone which shows my local time. so I enter the local time in the time field and it adds the necessary time in the chtime field via the field update event. How can I make it populate the date field automatically to the next day once the chinese time crosses over to the next date?

Currently I have this:
Private Sub LocalTime_AfterUpdate()

Dim CurrentMonth As Integer
Dim CurrentDate As Date
Dim PreviousDate As Date
Dim StdDate As Date
Dim DaylightToday As Boolean
Dim DaylightYesterday As Boolean
Dim LResponse As Integer
Dim LocalB As Long


LocalB = biasHour(GetLocalTimeZoneBias(False))
DaylightToday = IsLocalDaylightSavingTime
If DaylightToday = False Then
' here we go back an hour to standard time so 7 hour difference
Me.ChTime.Value = Format(DateAdd("h", 7, Me.LocalTime), "Short Time")
Else
' here we go forward an hour to DST so 6 hour difference
Me.ChTime.Value = Format(DateAdd("h", 6, Me.LocalTime), "Short Time")
End If


End Sub

Open in new window

PeterBaileyUkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Why not record the date/time? Then add the difference, and format the result (as now) to display only the time.

/gustav
0
PeterBaileyUkAuthor Commented:
When I do that how do I enter the the datetime together? I dont mind I guess I made life harder for myself, when I do that does it then show the correct date against the time?

Anyway I know your mind works well around time, you can see what is happening here.

My Chinese friend will just want her date and time and not mine for the doctor.
0
Gustav BrockCIOCommented:
Then, afterwards, extract the time only:

Me!ChTime.Value = TimeValue(DateAdd("h", 6, Me!LocalTime.Value))

Open in new window


But I'm not sure what you really mean?

/gustav
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

FREE DOWNLOAD
PeterBaileyUkAuthor Commented:
you said Why not record the date/time so I guess thats a number.

let me rephrase : the time comes in as my local time as its a message on my phone
 
: 17:32  145/91 78

my date is 08/12/2017 and my time 17:32
her time is 0032 09/12/2017

maybe I didnt understand your method properly,

so when she goes to the doctor the result above would say 9/12/2017 00:32

my entry form at present doesnt handle the date and I take a lot of readings so I was trying to make it fast so i just enter time and bp and everything else adjusts as if i were in china.
0
PeterBaileyUkAuthor Commented:
I wonder if your timezone function would do it to give me the remote date if i enter my date and the local time of the bp
0
Gustav BrockCIOCommented:
08/12/2017 and my time 17:32

Then your local time is 2017-12-08 17:32:00 and

Chinese date/time = DateAdd("h", 7, #2017-12-08 17:32:00") 
-> 2017-12-09 00:32:00

Chinese time = TimeValue(DateAdd("h", 7, #2017-12-08 17:32:00"))
-> 00:32

Open in new window

I see no issue here.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
If you miss the local date, it is:

PhoneTime = #17:32#

LocalDateTime = Date() + PhoneTime

Open in new window

/gustav
0
PeterBaileyUkAuthor Commented:
I see what your doing, is it possible to enter just the time into that time field and then the update turn it into the datetime you suggested?
0
PeterBaileyUkAuthor Commented:
no I can just add in a variable and then get the rest ok I think I am fine now
0
PeterBaileyUkAuthor Commented:
Should I have changed the table fields from strings to date?  The form was based on a query and although the query worked and let me view it would let me input data after.

Now if I use the table as the data source the fields show numbers and not the time or date.
0
Gustav BrockCIOCommented:
Indeed, never (as in never) use anything else than Date for storing date and time.

/gustav
0
PeterBaileyUkAuthor Commented:
Got it and all as you said.

LocalDT = Date + Me.LocalTime.Value
DaylightToday = IsLocalDaylightSavingTime

If DaylightToday = False Then
' here we go back an hour to standard time so 7 hour difference
    ChineseDT = DateAdd("h", 7, LocalDT)
    Me.ChTime.Value = TimeValue(ChineseDT)
    Me.DTDate.Value = DateValue(ChineseDT)
    If Me.ChTime >= #12:00:00 PM# And Me.ChTime <= #11:59:00 PM# Then
        Me.AMPM.Value = "PM"
    Else
        Me.AMPM.Value = "AM"
    End If

Else
' here we go forward an hour to DST so 6 hour difference
    ChineseDT = TimeValue(DateAdd("h", 6, LocalDT))
    Me.ChTime.Value = TimeValue(ChineseDT)
    Me.DTDate.Value = DateValue(ChineseDT)
    If Me.ChTime >= #12:00:00 PM# And Me.ChTime <= #11:59:00 PM# Then
        Me.AMPM.Value = "PM"
    Else
        Me.AMPM.Value = "AM"
    End If
End If

Open in new window

0
PeterBaileyUkAuthor Commented:
Thank you so much your a guru at this stuff.
0
Gustav BrockCIOCommented:
Thanks. And I can add, that you don't have to work that hard ... Format will do it for you:

? Format(time, "hh:nn")
18:59
? Format(time, "hh:nn ampm")
06:59 
? Format(time, "hh:nn am/pm")
06:59 pm
? Format(time, "hh:nn AM/PM")
06:59 PM
? Format(time, "AM/PM")
PM

Open in new window

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.