date sort problem from a newbie

bkndbrown

New member
Local time
Today, 09:41
Joined
Jul 7, 2008
Messages
1
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.
 
Welcome to the site. Include the actual date field in the query and sort on that instead.
 
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.
 
Good point George. The space in the table name is also inadvisable.
 
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
 
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.
 
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
 
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
 
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.
 
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
 

Users who are viewing this thread

Back
Top Bottom