How do i format a query field to only show date and drop the Time Stamp (1 Viewer)

[Number11]

how can i format this field to remove the time please

 

[plog]

Use DateValue():

MS Access: DateValue Function

This MSAccess tutorial explains how to use the Access DateValue function with syntax and examples. The Microsoft Access DateValue function converts a string to a date.

www.techonthenet.com

 

[isladogs]

One thing to bear in mind as you are using dd/mm/yyyy date format...
Using DateValue in VBA will give an output in mm/dd/yyyy format except for unambiguous dates such as 31/07/2020.
For example

?DateValue(#04/11/2019 11:35:45#)
11/04/2019

One way of handling that is to format it first as follows

?DateValue(Format(#04/11/2019 11:35:45#,"mm/dd/yyyy"))
04/11/2019

Of course, if you do format as above, using DateValue is superfluous

?Format(#04/11/2019 11:35:45#,"mm/dd/yyyy")
04/11/2019

NOTE: This isn't an issue in a query as that resolves dates correctly depending on your location

Other solutions are possible as well....

 

[arnelgp]

One thing to bear in mind as you are using dd/mm/yyyy date format...

it will "appear" to whatever locale setting you have.
so i dont think this is necessary.

 

[isladogs]

Unfortunately it is necessary when using VBA code for those in the UK and much of the world who use dd/mm/yyyy date format.
The examples I gave in post #3 were copied direct from my immediate window
Here are two more examples

?DateValue(#11/07/2019 11:35:45#) 'ambiguous date so treated as 7 Nov 2019
07/11/2019

?DateValue(#21/07/2019 11:35:45#) 'unambiguous date =21 July 2019
21/07/2019

 

[cheekybuddha]

Hi Colin,

I think you need to re-test.

If you have dates stored in a table and run a query selecting DateValue([DateTimeField]) it will return the date correctly for 11th July 2019 11:35:45.

The date is stored correctly even though it displays in UK format 11/07/2019 11:35:45, and thus the function evaluates '11/07/2019' in the query result.

The VBA code you posted is also working correctly. The ambiguous date you pass to the function is 7th November. In VBA you always write dates in US or ISO unambiguous format even if you code in the UK! (especially since you are passing a date delimited as a date with octothorpes, and not a string representation of a date).

You do have to be careful if you pass a string date to the function as the date will first be evaluated with local rules:

?DateValue("11/07/2019 11:35:45")
11/07/2019
?DateValue(#07/11/2019 11:35:45#)
11/07/2019

 

[isladogs]

Hi David

Hi Colin,
I think you need to re-test.

No need. We are I believe saying much the same thing

Of course, queries will always resolve the dates correctly
Perhaps I should have made it clear I was referring to using this in VBA - I thought this was implicit in providing output from the immediate window.
Anyway, I have now made that explicit in the earlier posts.

I agree completely that the code is behaving correctly - my intention was purely to stress the outcome(s) to the OP
I'll try to make my point again using the 'ambiguous' date of 11/07/2019 11:35:45 (11 July 2019)

Firstly lets consider date values being passed (as would be the situation for the OP)

'Testing date values
?(#11/07/2019 11:35:45#)
07/11/2019 11:35:45 'output in mm/dd/yyyy format

?Format(#11/07/2019 11:35:45#,"mm/dd/yyyy")
11/07/2019 'output in dd/mm/yyyy format

?DateValue(#11/07/2019 11:35:45#)
07/11/2019  'output in mm/dd/yyyy format

?DateValue(Format(#11/07/2019 11:35:45#,"mm/dd/yyyy"))
11/07/2019 'output in dd/mm/yyyy format

All of the above are behaving EXACTLY as they should

Now lets test string values ...as would be the case after formatting

'Testing string values
?("11/07/2019 11:35:45")
11/07/2019 11:35:45 'output in dd/mm/yyyy format

?DateValue("11/07/2019 11:35:45")
11/07/2019 'output in dd/mm/yyyy format

?CStr(#11/07/2019 11:35:45#)
07/11/2019 11:35:45 'output in mm/dd/yyyy format

?DateValue(CStr(#11/07/2019 11:35:45#))
07/11/2019 'output in mm/dd/yyyy format

Once again, all of the above are behaving EXACTLY as they should

 

[cheekybuddha]

Using DateValue in VBA will give an output in mm/dd/yyyy format except for unambiguous dates such as 31/07/2020.

Well now that you have added your qualification it is clearer.

But your first example in Post #3 is still confusing.

You say: "Using DateValue in VBA will give an output in mm/dd/yyyy format"
and then show:

?DateValue(#04/11/2019 11:35:45#)
11/04/2019

However, the output there is dd/mm/yyyy - [y]our local format. You have passed 11th April and had 11th April (to us) returned.

 

[isladogs]

No. I meant where someone using dd/mm/yyyy format entered a value of 04/11/2019 (4 Nov 2019).
I think my intention was clear ... even if you don't

 

[cheekybuddha]

No. I meant where someone using dd/mm/yyyy format entered a value of 04/11/2019 (4 Nov 2019).
I think my intention was clear ... even if you don't

I know you think your intention is clear!

But still ?DateValue(#04/11/2019 11:35:45#) is 11th April, not 4th Nov. It's not clear (to me) that you're trying to warn that the date being passed is not what the OP might think it is. It came across more focused on the value returned 'appearing' flipped.

 

[isladogs]

That's exactly what I was trying to warn the OP.
I think we've beaten this horse to death...

 

[cheekybuddha]

 

[Galaxiom]

?DateValue(#11/07/2019 11:35:45#) 'ambiguous date so treated as 7 Nov 2019
07/11/2019

?DateValue(#21/07/2019 11:35:45#) 'unambiguous date =21 July 2019
21/07/2019

Nothing ambiguous about a date with # delimiters. It will be interpreted as an mm/dd/yyyy date regardless of the regional settings.

A date as a string is interpreted in the regional format. Nothing ambiguous there either.

Dates are expected to fall between 1 January 100 and 31 December 9999.

The problem is when dates are invalid in these contexts, there is a stupid fallback in Windows (not just VBA or Access) where it attempts to make them work in any format. It will even try yy/mm/dd which is why 29/2/21 will be interpreted as 21st February 1929.

I would have preferred they simply throw an error.

 

[arnelgp]

you try it with tables with those values.
and not Constant feed to DateValue().
because what the OP showed is in datasheet.
the Output will be whatever Locale date you set.