trim / out

[Janet95]

Hi I have a small task I've been scratching my head over and thought it best to start from scratch.

I have a table with a date field, data type is date.
The problem is I want to sort it by month, however if I sort by asc or desc is does it by year. I created a new table that fills in a date field by a query. This fields data type is a text field. I want to strip out all " / ".

so the data in the field is 9/25/2006. I want to trim out the / starting from the right.

I tried opening a form that did a record set, and went through each record and trimmed the / out, but that didn't work.

Am I making this more complex than needed. Any suggestions on how I can trim out the / so the data is changed from 9/25/2006 or 09/25/2006 to 9252006

Thanks for your any help in advance.

Thanks
Janet

 

[Eupher]

Janet,

Here's the thing you need to understand about dates in Access. What you see is almost never the data that is really stored. The actual date data is a double (double precision floating-point number). How this info is displayed is determined by your system settings and/or the format of the field in your table/form/report. You can use the Format function to get your dates to display as you wish, without the "/" character:

Format([blue][MyDate][/blue], "mdyyyy")

To sort by month in a query, use an expression as one of your fields and sort on that:

MyMonth: DatePart("m", [blue][MyDate][/blue])

...where [blue][MyDate][/blue] is the name of YOUR date field...
HTH,

Ken S.

 

[PHV]

so the data is changed from 9/25/2006 or 09/25/2006 to 9252006
The query may use an expression:
txtDate: Format([Date field], 'mddyyyy')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya Janet95 . . .

[blue]The problem is I want to sort it by month . . . [purple]I want to trim out the / starting from the right.[/purple][/blue]

[blue]You don't have to strip anything out to sort by month![/blue] If fact [blue]ideal for you appears to be a sort order by month, day , year.[/blue]

[green]What you do is use a query and add custom fields for sorting![/green]

For instance you base the [blue]RecordSource[/blue] of a form on a query. The query has the following custom field for sorting set to ascending:

[blue]MonthSort: Month([[purple][b][i]YourDateFieldName[/i][/b][/purple]])[/blue]

And the custom field can be set not to show.

If you decide your happy with this you can set the other two custom fields day & month:

[blue]DaySort: Day([AnyDate])
YearSort: Year([AnyDate])[/blue]

Additionally if the query is updateable without the custom fields it'll be updateable with.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[Janet95]

Thanks everyone for your help. I appreciate that

Thanks
Janet

 

[Janet95]

OK,I thought this was working correctly but it's not. In the query below I'm trying to order the tblYouth.DOB date field by month. The format of the field is mddyyyy in the table.

The order by ("M" tblYouth.DOB); is the part that's not working correctly.

SELECT tblYouth.FirstName, tblYouth.LastName, tblYouth.MIName, zz_YouthIDTotal.SchoolYear
FROM tblYouth INNER JOIN zz_YouthIDTotal ON tblYouth.YouthID = zz_YouthIDTotal.YouthID
WHERE (((tblYouth.DOB) Is Not Null) AND ((zz_YouthIDTotal.SchoolYear)=[forms]![frmReports]![cmbSchoolYear]))
order by ("M" tblYouth.DOB);

How can I order/sort this by month in the query?
Thxs in advace for your help everyone

Thanks
Janet

 

[Eupher]

Janet,

When you say you want to sort by month, do you mean alphabetically or numerically? And AFAIK,

("M" tblYouth.DOB)

...is not a valid ORDER BY clause.

Ken S.

 

[Eupher]

Janet,

You may try this instead. If alphabetic sort:

ORDER BY Format(tblYouth.DOB, "mmmm")

If numeric:

ORDER BY DatePart("m", tblYouth.DOB)

HTH,

Ken S.

 

[Janet95]

Thanks Everyone!!!

Ken it worked,it was by number.

I used ORDER BY DatePart("m", tblYouth.DOB)

Thanks you all are wonderfull!!!!

Thanks
Janet