Unable to sort date field

[JayRo71]

I have a field [Bill Period] as short text. The data is provided to me as such, "11/07/22-12/07/22", which represents a start date and an end date.

I extracted the start day and formatted it as a date using the following expression. StartDate: CDate(Left([tblWegoWiseSpikes]![Bill Period],8))

It works perfectly until I try to sort. "Invalid use of Null", is returned.

I tried using just the Left() function, which sorts, but it sorts incorrectly, because it is not a date field.

Thank you

 

[dhookom]

I would store this as two date type data fields which would allow for correct sorting. However, do all of your dates have leading 0s for Jan-Sep? Are all of the Bill Periods populated?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Text in the format of MM/DD/YY - you cannot sort in any meaningful way (as Dates).
If you convert your date into YYMMDD, you will be able to sort this text (providing you don't have dates prior to 2000)

If you have any periods as "5/5/22-5/6/22", your StartDate will not work :-(
I would be very tempted to try:
[tt]
StartDate: CDate([blue]Split([/blue]tblWegoWiseSpikes![Bill Period][blue], "-"), 0[/blue])
[/tt]
If the [tt]Split()[/tt] idea works, you may want to consider Calculated Fields in Access for your [tt]StartDate[/tt] and [tt]EndDate[/tt] fields based on your [tt][Bill Period][/tt] field


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[JayRo71]

dhookum, I think you bring up a great point. Maybe there is a field that is not populated. I will check. Thank you!
Andrzejek. I will try your suggestion and write back. Thank you!

 

[JayRo71]

dhookum, you were correct. I had one record that had no data and it that is what caused the problem. Thank you!
Andrzejek, I see where you are going with split() and I am happy you showed that to me. I am going to toy around with split() as this may help in future datasets, I come across any bill dates that omit the leading 0.

Based on the ideas both of you have shared with me, I think I will run an update query to add in a leading 0 if it does not exist. I could also include an update query to delete any blank records.

Thank you both for your help!