Microsoft Access
Microsoft Access

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: 2017-12-08

Who is Participating?

Solutions

LVL 54

Gustav BrockConnect With a Mentor CIOCommented: 2017-12-08

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

LVL 54

Gustav BrockCIOCommented: 2017-12-08

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

/gustav

PeterBaileyUkAuthor Commented: 2017-12-08

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.

LVL 54

Gustav BrockCIOCommented: 2017-12-08

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

PeterBaileyUkAuthor Commented: 2017-12-08

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.

PeterBaileyUkAuthor Commented: 2017-12-08

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

LVL 54

Gustav BrockCIOCommented: 2017-12-08

If you miss the local date, it is:

PhoneTime = #17:32#

LocalDateTime = Date() + PhoneTime

Open in new window

/gustav

PeterBaileyUkAuthor Commented: 2017-12-08

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?

PeterBaileyUkAuthor Commented: 2017-12-08

no I can just add in a variable and then get the rest ok I think I am fine now

PeterBaileyUkAuthor Commented: 2017-12-08

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.

LVL 54

Gustav BrockCIOCommented: 2017-12-08

Indeed, never (as in never) use anything else than Date for storing date and time.

/gustav

PeterBaileyUkAuthor Commented: 2017-12-08

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

PeterBaileyUkAuthor Commented: 2017-12-08

Thank you so much your a guru at this stuff.

LVL 54

Gustav BrockCIOCommented: 2017-12-08

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