Format datetime like mm/dd/yyyy hh:mm if time value, else mm/dd/yyyy (2 Viewers)

[cedar]

In a datasheet, is there a way to format a datetime value with hour and minute only (no seconds) if there is a stored time value, but if the value stored is only a date, show as mm/dd/yyyy?

The goal is to have these outcomes:
enter a plain date 10/11/2023, show 10/11/2023
enter a date time value like 10/11/2023 7:30 am, show 10/11/2023 07:30 AM
enter a date time value like 10/11/2023 7:30:12 am, show 10/11/2023 07:30 AM (the seconds might come from Now())

Setting the format to mm/dd/yyyy hh:nn has the problem that hours and minutes are added to the plain date as 00:0.

This database is Access front end and back end.

I suppose the value shown could be plain text, converted from the date or datetime value in the query behind the form, but I'd prefer to use a formatting method to keep it simple. The field will be used for data entry, that means even more hoop-jumping to deal with if using the text presentation method.

 

[Pat Hartman]

In a relational database a column contains data of the same data type. In a form you can do some customization using conditional formatting but not this. But in a report, you could customize the formatting using code in the on Format event that tests whether the date contains time or not and changes the format property appropriately.

 

[Minty]

You could check if the datetime is an integer value using an IIf() statement something like

MyFormattedDate: IIf(Int(MyDate) = MyDate, Format(MyDate," mm/dd/yyyy"), Format(MyDate,"mm/dd/yyyy hh:nn")

That would check between a whole date and one with a time portion
If you put that in the underlying query for the datasheet form it should work.
No idea if it would let you edit it, though.

 

[cedar]

Minty I think that expression would be read only, so no edits.
Pat, yes; I am hoping for some voodo formatting technique. What I'm after could have been something that msft anticipated, but as far as I can tell there is nothing in the Access kit that allows.
If I find a way I'll post it back here.

 

[Gasman]

Why not try it and actually find out?

 

[MajP]

If you simply format as general date, does it not do what you are asking?

 

[The_Doc_Man]

The other solution would be to format the date/time sequence as "hh:nn" (which removes the date portion from the equation entirely) but then compare it to see if value "00:00" was reported. And if it does, use a different format string. But doing this in datasheet mode would be tricky, requiring that the specified datasheet would come from a query rather than a table.

SELECT [A], [B], [C], _
    IIF( Format( [D], "hh:nn" ) = "00:00", Format( [D], "dd/mm/yyyy" ), Format( [D], "dd/mm/yyyy hh:nn" ) ), _
    FROM MyTable ;

Show that query in datasheet view rather than showing MyTable. (But I have the same question as MajP about whether General format would do the job; I'm just showing an alternative.)

 

[Minty]

@The_Doc_Man That's what my solution was proposing, (I was just comparing the Int() Value) , but it needs to be editable, which I don't think a calculated result would be?

 

[ebs17]

The field will be used for data entry,

When entering, you could also separate the date field and time field. Then simple standard formats using the format property of the controls are sufficient.
This allows you to provide support through Datepicker and Timepicker.
Key combinations would also be possible:
CTRL + ; for today
CTRL + SHIFT + : for now

Whether you manage date and time in a single field or in two separate fields also depends on the subsequent primary use.

Separating is easy

SELECT DateValue(DateTimeField) AS XDate, TimeValue(DateTimeField) AS XTime ...

Putting it together is easy

SELECT XDate + XTime AS DateTimeValue ...

 

[Pat Hartman]

SELECT [A], , [C], _ IIF( Format( [D], "hh:nn" ) = "00:00", Format( [D], "dd/mm/yyyy" ), Format( [D], "dd/mm/yyyy hh:nn" ) ), _ FROM MyTable ;

This converts the date to a string and therefore breaks sort and compare logic.

What is wrong with @MajP 's solution? I don't use mixed formats (my date fields either have a time or they have no time) so I never noticed that my list forms would work using the general date format. Changing the alignment as MagP did won't be perfect unless you always used fixed width for each part of the expression. For example, changing 9/30 to 9/1 will make the alignment off.

 

[cedar]

If you simply format as general date, does it not do what you are asking?

View attachment 110295

MajP, General date shows seconds. For this purpose, that's visual clutter, and what I'm hoping to get rid of.

Not sure why I didn't get any notifications re these later updates - thanks everyone!

 

[cedar]

This converts the date to a string and therefore breaks sort and compare logic.

What is wrong with @MajP 's solution? I don't use mixed formats (my date fields either have a time or they have no time) so I never noticed that my list forms would work using the general date format. Changing the alignment as MagP did won't be perfect unless you always used fixed width for each part of the expression. For example, changing 9/30 to 9/1 will make the alignment off.

The issue with General Date is that it leaves seconds; I don't want to show seconds.

 

[cedar]

When entering, you could also separate the date field and time field. Then simple standard formats using the format property of the controls are sufficient.
This allows you to provide support through Datepicker and Timepicker.
Key combinations would also be possible:
CTRL + ; for today
CTRL + SHIFT + : for now

Whether you manage date and time in a single field or in two separate fields also depends on the subsequent primary use.

Separating is easy

SELECT DateValue(DateTimeField) AS XDate, TimeValue(DateTimeField) AS XTime ...

Putting it together is easy

SELECT XDate + XTime AS DateTimeValue ...

ebs17; I thought of separating into two data entry fields over the weekend. I may give that a try. Not sure exactly how I'd set it up; but probably a single col to store, but two inputs.
I think too that it might be possible to switch this to a continuous form; that would then allow for overlapping fields for presentation. But that'd take some experimenting as well.
What I was hoping for though was a sneaky way to format with the built-in formatting controls. I think after the input from all of you I can safely put away that hope. I'd be cool if msft added it; doesn't seem like a very exotic request.

 

[cedar]

This converts the date to a string and therefore breaks sort and compare logic.

What is wrong with @MajP 's solution? I don't use mixed formats (my date fields either have a time or they have no time) so I never noticed that my list forms would work using the general date format. Changing the alignment as MagP did won't be perfect unless you always used fixed width for each part of the expression. For example, changing 9/30 to 9/1 will make the alignment off.

Pat I tried adding the formatting expression into the query; the col becomes read only.

 

[The_Doc_Man]

OK, let's take this from another viewpoint. Showing the results IN A QUERY is certainly possible, but leads to the problem of the query becoming read-only if you have to use variable formats. The real question is one of presentation vs. computation and a query isn't the prime choice for presentation. A continuous form looks like a datasheet but can format a column using conditional formatting. So then the question becomes one of how you are actually using this and whether you need to manually update it. A continuous form CAN do that with variable formatting attached to the form, not the data field. Your first post said this might be used for data entry. With a continuous form, you can still do that.

 

[cedar]

Yes it is important that the form be used for data entry. Using a cont form is a way forward; I think I will switch over to that, unless I think of some other approach. Here I'd prefer datasheet over cont form because it's easy to nav between rows with keystrokes. It's one trade off vs another.
I'm even reconsidering the entire need. The user didn't ask to lose the seconds - that was my idea, because they are superfluous here, and just add noise. But I might just go for General Date and leave it, possibly to revisit.

 

[Pat Hartman]

Pat I tried adding the formatting expression into the query; the col becomes read only.

Correct. You can't use the Format() function. Your only option is the Format property or conditional formatting if you want the data to remain editable but I'm not sure you can do what you want with either

Forms are primarily for data entry. The alternate formatting is no problem at all for a report since you don't have to worry about updating the data. If you really want the alternate formatting in a form, you can use two columns. One that is updateable and one that is not.

 

[MajP]

This works OK, not great. It is a tabular form with the formatted textbox on top of the editable textbox. The focus changes to the editable on a key or mouse down.

 

[Minty]

Here I'd prefer datasheet over cont form because it's easy to nav between rows with keystrokes

You can simulate that with a simple piece of code, in the forms keydown event put the following:

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
 On Error Resume Next
    
    Select Case KeyCode
    
        Case vbKeyDown
            If CurrentRecord <> RecordsetClone.RecordCount Then
                DoCmd.GoToRecord , , acNext
            End If
            KeyCode = 0

        Case vbKeyUp
            If CurrentRecord <> 1 Then
                DoCmd.GoToRecord , , acPrevious
            End If
            KeyCode = 0

    End Select

End Sub

And make sure you turn Key Preview = Yes in the event list for the form.