Date field General (1 Viewer)

[ypma]

Advise sort. I have created a new date field in my table and set the Date format to general . When using the date picker to enter a date, only the short date is shown .
I have tried the format mm/dd/yyyy hh:nn AM/PM, however the result shows short date plus 00 00 and not the hours and minutes . Where am i going wrong
Regards Ypma

 

[arnelgp]

add Format Property of the textbox.

 

[ypma]

arnelgp thank you for your quick response. i have entered general date in the date time field ,general drop down. Not sure what you mean "add Format Property of the textbox." sorry if i am no being very clear.

Ypma

This is at table level only .

 

[CJ_London]

however the result shows short date plus 00 00 and not the hours and minutes

the 00 00 is the hours and minutes - using the datepicker only selects a date so the time element will always be zero which equates to 12 am (midnight)

I have tried the format mm/dd/yyyy hh:nn AM/PM. however the result shows short date plus 00 00 and not the hours and minutes

in the immediate window
?format(date,"dd/mm/yyyy hh:nn am/pm")
23/09/2019 12:00 am

?format(date,"dd/mm/yyyy hh:nn")
23/09/2019 00:00

So looks like you did not set the format property to

mm/dd/yyyy hh:nn AM/PM

 

[arnelgp]

datepicker as the name suggest is only for date (the time portion 0).

 

[MajP]

When using the date picker to enter a date, only the short date is shown .

since the date picker provides no time component only the date will be shown. It will not show a generic 00:00 AM, if that is what you are asking. If you type in a time component it should show.

 

[Pat Hartman]

NEVER set format properties at the table level. They do not restrict what can be stored. All they do is restrict the display and because of that, they may be hiding relevant data. General should show time so in theory it isn't hiding anything but I would still remove the format.

What are you using to populate the date field? The Date() function contains ONLY the date. Now() contains the current date and time.

 

[ypma]

Thanks to all of you that commented on my post. The point that a date picker is what it says on the can, is well made. I was initially testing at table level and Pat put me right there. The idea was for the user to enter a date in a date field on the form, resulting in a date and time element. The format option seems to always have the date 12:00. I have changed my approach and have used the Now() in the click event of the date field . I still don't fully understand why choosing the Format General Date did not produce the same results as the Now ().

Thank again
Still learning
Ypma

 

[Pat Hartman]

If you were entering "Now()", that should have worked even with the general format applied. Something else must have been involved.

Glad it is working now.

I was emphatic in my statement about not formatting at the table level because more than once, I've taken over apps built by others where they had mistakenly used Now() when they should have been using Date() - but not everywhere so some dates included a time element and others did not. To overcome the "problem", they added a format at the table level. It was hours before I figured out that my query was fine, it was the data that was a problem and the format was hiding the actual data values.

 

[ypma]

Pat, i asked for advise and expected constructive replies , you did just that , i did not take you post as a put down , but took you comments on board .

Regards
Ypma

 

[Pat Hartman]

I was just continuing my thoughts. Although your problem is solved, it is not at all clear what "fixed" it. When we know what "fixed" the problem, it is easier to move on with confidence that the problem will not recur so please post back if the problem recurs or if you know what you did that fixed it.

 

[ypma]

Pat, I did not fix my initial problem but worked around it. Normally I trigger a general date using the after update event.# eg me.dateconfirmed= now# My user wanted to be able to change this date by using the date picker, which of cause only produces the short date . Wrongly I thought i could disable the date picker on this date field and set the format to General Date. All the user had to do was enter a short date, and a time would automatically be added . As stated i worked around it by using the click even to produce the general date.

Thank you for your interest in my little problem .

Regards Ypma

 

[CJ_London]

you could have some code in the afterupdate event of your confirmedDate control

not tried but something like

if datevalue(ConfirmedDate.oldvalue)<>datevalue(ConfirmedDate) then
    ConfirmedDate=confirmedDate+timevalue(ConfirmedDate.oldvalue)
    'or as an alternative for the 'now' time ConfirmedDate=confirmedDate+Time()
end if

the oldvalue will not change until the record is updated

you can also try it this code in the beforeupdate event but then you reference ConfirmedDate.Text rather than ConfirmedDate

 

[ypma]

CJ London: As an exercise i tried your code in the after update event #Me.date_sent = date_sent + Time()#. I triggered the Now() resulting in
26/09/2019 17:10:42. I then used the date picker to enter today's date resulting in: 27/09/2019 04:50:55. Note, I am using UK time and date should this make a difference ?
Please comment as i am curious .
Regards Ypma.

 

[CJ_London]

as I said not tested - but you didn't say which version of code you used - the one picking the time from the oldvalue or the one using the time function

 

[ypma]

C J London:Appreciated the fact that you had not tested it . i used the alternative code you supplied #Me.date_sent = date_sent + Time()# and the time zones do not match, hence my question. i am using UK time zone and when the using code produced a time in the future as shown in my post.

Regards Ypma

 

[CJ_London]

I'm not sure what that would be. in the immediate window

?now()
27/09/2019 14:50:40
?time()
14:50:41
?date()+time()
27/09/2019 14:50:42
?#27/08/2018#+time()
27/08/2018 14:50:43



All show the same time

suggest in your click event use

debug.print these, substituting #27/08/2018# with your control name

also, if in UK, it may be you need to use the cdate function because month and day are the wrong way round, but that won't affect the time

confirmeddate=cdate(confirmeddate)+time()