Setting text box format to short date and optional short time

[choaspes]

I want to be able to set text boxes so that if one enters a date and a time it displays in the format "dd/mm/yyyy hh:mm", but if one just enters a date is displays in the format "dd/mm/yyyy". Is this possible?

Stipulating "dd/mm/yyyy hh:mm" means that when you just enter a date it adds "0" values for the time e.g. entering "21/6/13" gives "21/06/2013 00:00".

General Date allows for an optional time, but it means that when you do enter a time it gives you seconds as well "dd/mm/yyyy hh:mm:ss" - and I don't want that.

Any ideas? Thanks in advance

 

[CJ_London]

not tested but something like this in your date field afterupdate event may work

if txtdate-datevalue(txtdate)=0 then
txtdate.format="dd/mm/yyyy"
else
txtdate.format="dd/mm/yyyy hh:mm"
end if

Note this will not work for continous or datasheet forms

 

[choaspes]

That looks like a stroke of genius to me. Thanks, will try it later!

 

[choaspes]

May I trouble you for one more bit of advice CJ? sorry if it's a completely stupid question...

I just posted a thread on the "reports" bit of the forum in which, I am told, my schoolboy error was formatting dates using Format() not DateValue() - one returning a string, the other returning a date. Why is using Format() correct in this scenario but wrong in the other? Is it because the form is writing to a field in which the data type is already set as Date/Time whereas in the query there is no such straightjacket for the result?

 

[Brianwarnock]

The first thing you need to know if you don't already is that the Format is merely for display, date/time is stored as a double with the integer part representing the year and the decimal the time, so when you store only the date 0 is stored for the time.

Having formatted the date/time in a control then this string will be sorted by text rules as lagbolt advised, here you are viewing only the date part but the zero time will still be stored.

Brian

 

[CJ_London]

Format will always return a string which is fine for presentation purposes but where you require the 'numeric or date' value for calculations/sorting etc you should use datevalue to convert a string to a date.

Ah - been beaten to it by Brian

 

[Brianwarnock]

Format will always return a string which is fine for presentation purposes but where you require the 'numeric or date' value for calculations/sorting etc you should use datevalue to convert a string to a date.

Ah - been beaten to it by Brian

Answers not quite the same, I feel that yours added clarity

Brian

 

[choaspes]

Thanks both of you.