Sorting by Date in non-Date Field

[Chris1112]

I have a text box in which users sometimes place dates. The field is always Date/Time or something like "TBD". I know it's bad practice but I would like to be able to sort by date. When I use the following code, I get "data type mismatch." Is there a way to sort this field by date, then have the error fields sort after?

    Me.OrderBy = "Year([2ndWalk]),Month([2ndWalk]),Day([2ndWalk])"
    Me.Refresh

[TG_W]

Try the code below set for when the form loads in On Open

Me.OrderBy = [2ndWalk]
Me.OrderByOn = True

[Chris1112]

Sadly this doesn't work. The dates are entered inconsistently. I know...I know. Day() Month() and Year() are still able to pull from the way they're entered. For instance, field values are "Apr 18 5:00 PM", "Apr 30", and "TBD". Sorting chronologically might be difficult.

[TG_W]

Well, it really sounds like you need to just add additional fields for times and if a record is "TBD". I would use a check box for "TBD" and set the other 2 fields to Date/Time and format how you want the info to look like after it is entered. Users can enter a date with just "4/30", and Access will understand it is "04/30/2012" or "Apr 30" or however you want it to look. It's not just bad practice to not save your dates in Date/Time - you will not be able to get the order results that you want due to the inconsistent data inputs.

[Chris1112]

Yeah, unfortunately the date format is not consistent for a reason. If conditional formatting would handle it, I could set it there but instead it has to be as it is or the users will rebel. They use specific and non-specific dates to show estimated, scheduled, or actual times.

In any case, what I was hoping for was a qualifier to keep it from erroring when I pull Day() Month() and Year(). I suppose that would never work inside of a SortBy though. :\

[TG_W]

It's not a conditional formatting deal. Once you create the field in the table, you set field to Date/Time and the format at the bottom in "Field Properties".

There's no reason you couldn't have a Comments field next to the date so they can enter additional information, or perhaps you can create a Date/Time field for estimated, shceduled, or actual. I have all 3 fields ("Planned", "Forecast", and "Actual") in a few of my databases where I need to track and report all of the dates.

There's really not much point to create a database where data will not be in a controlled environment. Might as well just stick to Excel.

[Chris1112]

Thanks for the help.

This actually would be in Excel except that we require multiple users to utilize it. I suppose that I could create an unbound text box and update one of several fields based on what is put into that text box, then sort by them. I was hoping for something more straightforward.

[TG_W]

Straghtforward is controlling the field to Date/Time and allowing for comments outside of that field.

[Chris1112]

The issue is the specificity of the field. For instance, since dates are sometimes estimates as to the month, the day, or the specific time, how would you control the field to express that? For instance, if we estimate something to take place in November, the field would have the value "Nov 2012". If we have a scheduled time for that, it would be "November 2 08:15 AM". I wouldn't mind a date field, but if it always displays the time, it will be confusing as to what it represents, an estimate or an actual. This is where I wish I could use conditional formatting to change the Format from General Date to Short Date etc.

Suggestions on how to handle this would be appreciated.

Thanks

[TG_W]

Multiple fields - One for month, day, year, and time. Users would only fill in what they have estimates for. Concatenate month and year in a field on the form and order by the concatenated field then by day then by time. I would control month to a numeric field. To best control that, set the month to be a drop box linked to a table that has the month number and name and bind the number field after the month is selected.

[Chris1112]

Thanks for the help. This thread took a big turn and will be utterly useless to any who find it, but at least I should be fixed.

[TG_W]

Well, if you're sorted, at least that much is accomplished!