Hi everyone again,
I have a date field in a query and I want to convert it to a datetime one.
e.g I want 01/01/2017 to become 01/01/2017 00:00:00
but,
result should also be a date field, not a string (as a Format function outcome)
Any ideas?
In the absence of any conversion function, fields in a query retain the data type property of their origin. You are confusing the issue because the query's field will be a string if the source-field in the query's table source is a string. It will be a date if the query's table source is a date. If you get a date without a time it is because the time got truncated away as explained earlier. You are confusing "intrinsic display format" with "actual data storage" format.
Internally, a DATE field is a TYPECAST of type DOUBLE. If you aren't sure, I'll be explicit. A typecast field is stored one way but interpreted another way, and Access knows how to interpret the two types differently even though internally they are the same. That is the same issue as Boolean or Yes/No fields, which are typecasts of BYTE (integer). Stored as a byte, they are interpreted as TRUE or FALSE.
Back to DATE fields... Just like putting the double-quote marks (") around something makes it a string, putting octothorpes (#) around something makes it a DATE (though of course if you give an impossible input string for that, it fails.)
This field's value would later be used in vba code to position records sorted by date and time (after some calculations). Since the field is a string (no matter how it appears) can't do that. Am I misinterpreting something?
Yes. Using a format function, you perform a type conversion (to string) - but that isn't what you HAVE to store or use. Using a control or field format property, the implied type conversion ONLY applies to what is displayed on screen or in print, not what is stored in the record - UNLESS you actually store that formatted date into a STRING field or variable.
Forgive me if I get a bit pedantic here, but I need to emphasize a point. Computers in general and Access in particular provide lots of "illusions" such that you can't always believe your eyes. If you display an integer, it was just a stream of bits. If you display a scientific number, it was just a stream of bits... and so on. It is all about what is stored vs. what is shown. It DOESN'T MATTER how something is shown.
To use something in a computation is different from using something for display. It comes back to how you store it (or in WHAT you store it.) It DOESN'T MATTER how the query shows the field. It matters where and how you put it away for later use. Your concern over how the query displayed the field is misplaced. You DO have to treat the field consistently with regard to how you store it and how you use it, but you can separately display the field in any way you want - as long as you don't allow the output of a FORMAT function to be used in place of the actual data element.
I don't know if I confused you more or if the point got through. But what I saw was someone having issues with form vs. function. I hope that I adequately addressed that.