Concatenated Date Field not formatting correctly

[Negger]

Hi, I have 2 date fields in a Table, both correctly set to Date/Time values and formatted as UK dd/mm/yyyy. These work fine in Forms & Queries. However, in one query I have concatenated the two [Datefields] in a calculated field to display as a single column. (The two date fields ae mutually exclusive, i.e. there is never a date in both fields in the same record -hence combining into a single display column for the purposes of this particular query).
The concatenation is Expr1: [Datefield1]&[Datefield2] which works fine in that it does just what I want. However, when I try to Order by this field (Ascending) the field is sorted by dd, not dd/mm/yyyy.
Research has found the following:
Access can also misinterpret calculated date fields in queries, especially where the date format is not American and the field contains some Nulls. The obvious symptoms are that the field is left-aligned and sorted as strings.
The solution is to explicitly typecast all calculated date fields, e.g.:
DueDate: CVDate([InvoiceDate] + 30) (Note: CDate() fails on Null values, so CVDate() has more uses than the "compatibility" issue highlighted in the Access documentation.)
This appears to be exactly what is happening, the concatenated Field is left aligned, and sorted as a string.
I need to force access to read the concatenated field as a UK Date, dd/mm/yyyy, but I don't know how ?
I don't understand how to apply the above, Can anyone assist ?
I had hoped to force it to read as Date by inserting #_#, but this isn't working.(I've tried various permutations)
There doesn't appear to be a way to format individual Fields in the Query Design View ?
Thank you all in advance.

 

[vbaInet]

The moment you concatenate it's automatically casted to a String.

Imagine this:

10 & 12, this will result in 1012. Would you call the result a String or a Number?

* If your dates are mutually exclusive use the Nz() function.
* The result will remain as a Date and the ordering will be correct.

 

[John Big Booty]

The discussion here and the solutions present might give you some pointers.

 

[Negger]

Hi John, Sorry, you're post is way over my head.
vbaInet, I take your point about the string, I had assumed that [Date]+Null =[Date], not "[Date]Null"
I've found Nz() function and entered Nz([DateField1],[Datefield2]) as the Expression, but it is giving me exactly the same as the concatenated field ?
Am I missing something in the syntax, or does anything else need adding to the Criteria ? Thanks Again

 

[vbaInet]

+ is also a concatenation operator but with a slight difference from &.

Same problem as in it's not sorting properly?

 

[Negger]

Sorry vbaNet, I've confused you now. What I meant to say was; I had assumed that when two fields were merged i.e. concatenated, that if one of those fields was blank, that it would simply return the value of the other Field, not return "ValueField1merged toEmptyField" i.e. the string! But that's a red herring!
I did try using a '+' instead of an '&' in the concatenation, but that returns an empty field - No Error Messages.
So we're back to the Nz() function if you can suggest any pointers on that one, and why it too is returning the same string and not a date ?

 

[vbaInet]

Whether you use + or & it will still return String so don't use it.

Tell me what the value is when you use:

[B]TypeName(Nz([DateField1],[Datefield2]))

[/B]

 

[Negger]

The extra ( ) is returning the same, i.e. a text string that looks like a dd/mm/yyyy Date, but is sorted by the dd ?

 

[vbaInet]

I would like to know exactly what the code I gave you returns. It should return the word "String" or "Date".

 

[Brianwarnock]

Might it be simpler to use
Iif(datedfield1 is null,datefield2,datefield1)

Brian

 

[Brianwarnock]

I would like to know exactly what the code I gave you returns. It should return the word "String" or "Date".

I have a feeling NZ always returns a string, but I'm probably wrong as I am way out of touch these days.

Brian

 

[vbaInet]

Hi Brian,

It preserves the data type of the input so if you pass Date you get Date. Maybe the OP isn't passing Date.

Tests:

?TypeName(Nz(Date(), ""))
?TypeName(Nz(Null, Date()))

 

[Brianwarnock]

Being a sceptic I stoked the boiler and booted up my old desktop and created a little db and yes it returned a string which sorted as a string, however my suggestion of the Iif works.

Brian

 

[vbaInet]

Interesting Brian. I just tested it out and it yielded the same result. I still don't think that Nz() returns a String because Nz(1) + Nz(1) should equal 11 if that was the case. I believe that value types returned from Nz() aren't taken into considertion when sorting in Access. Awful really!

 

[Negger]

Hi Both,
Sorry vbaInet, I misunderstood your previous post. That expression :
TypeName(Nz([DateField1],[Datefield2]))returns "Date" ?

 

[vbaInet]

Don't worry about that. We've seen the problem. Brian's solution will work for you.

 

[Negger]

Curiouser & Curioser ! I've also now entered Brians Iif Function and it works - to a point. It appears to be ignoring the "Ascending" in the Sort of the actual Expression, but it does respond to the drop down filter in the header of the Query itself, and Sorts correctly as a date - whereas the previous methods simply reverse the dd order.?

 

[vbaInet]

I don't understand. Does Brian's solution sort as a date or not? If it's not then you will just have to cast as you've already done, but I would use DateValue() instead of CVDate().

E.g.:

DateValue(Nz([Date1], [Date2]))

It will throw an error if both dates are Null.

 

[Brianwarnock]

It worked perfectly for me, as did wrapping the NZ solution in CDate or CVDate, all sorted correctly, but I always had a field with a valid date.

Brian

 

[Negger]

Thank you both for taking the time & trouble to "sort" this. Brian's Iif Function is now working fine, I beleive I confused it with all the various Expressions in play, once these were deleted it works exactly as I had wanted. The Query now opens with a single Date field, correctly sorted Oldest to Youngest, as you would expect from a Date.
You lost me again with the CDate & CVDate, but presumably I don't need these now ?