date sort problem from a newbie

[bkndbrown]

on my table I have a date column that is formatted as short date. In my query table I have it formatted to month via

Format$([outage loger].Date,'mmmm yyyy').

When it displays however it sorts the date alphabetically instead of chronologically. How can I fix this. I tried using the wizard to create my query table and it worked until I switched to design view and then it sorted alphabetically as well.

Please remember I am a newbie and will require very basic instructions.

 

[pbaldy]

Welcome to the site. Include the actual date field in the query and sort on that instead.

 

[georgedwilkinson]

In addition to Paul's alway excellent advice, you have a field named "Date" which is a reserved word. Probably best to change that to something more descriptive.

 

[pbaldy]

Good point George. The space in the table name is also inadvisable.

 

[ms2000]

Hi,

I set up a table with a text field (Month1) for a three-letter month. (Jan, Feb, etc.)

In a continuous list form, I want to be able to click the A->Z sort button in the toolbar and have the dates sorted by chrono. I use the A->Z sort button for several other fields in the form as well. Want to keep it simple for the users.

Is there a way to somehow use the MonthID field (number field) in the table to sort the Month1 field? Is there a bit of code that would use the MonthID to sort while still displaying the Month1 data.

I set up the form for this field using the Row Source Type = Table/Query
Row Source = Month Table
Column Count = 2
Column Widths = 0";1"
Bound Column = 2

Month Table = MonthID, Month1

Thanks for any help.
Mark

 

[georgedwilkinson]

Ah, I love it when we jump through hoops when we could have just done it right to begin with.

How'z about just making that field a date field and format the display to only show the 3 digit month? Then, regardless of what the user does right or wrong, the sort will always be in the right order.

 

[ms2000]

I know....I was thinking the same thing...I can change the date field as I am just finishing the design of the db, so there is not much data in there yet.

If I choose date/time as my data type, what is the code to just show the 3-letter month?

I tried a few things, but it didn't work. Also, would I be able to enter the data as Jan, Feb, etc?

Thanks again.
Mark

 

[boblarson]

Also, would I be able to enter the data as Jan, Feb, etc?

NewFieldNameHere:Format([YourDateFieldHere],"mmm")

 

[ms2000]

Thank you. I'm a bit of an idiot.

Where exactly does that code go?

And what is "NewFieldNameHere" mean?

In my table, I have two fields, one is the MonthID and the other is the Month1 (now a date/time data type).

In the field properties under Format, I've tried to enter the code, but it keeps throwing in lots a parens.

I also tried in the Input Mask field...to no avail.

Mark

 

[boblarson]

Sorry, I was using it as if it was in a query and NewFieldNameHere meant to type something in as a new field name for your value since the query won't let you rename the field to the same field you are extracting from.

 

[ms2000]

Ok, I changed the table to a date/time data type. Then I added the expression to display just the first three letters of each month...worked beautifully.

Now, in the form, I am running into issues with Data Type Mismatch when I use the A->Z sort button...

I want the 3-letter month to display in the drop-down box, which it does, but then the sort gives me that error.

Not sure which column to use as the bound column...any suggestions?

Mark