Datepart function is it the right solution???

M

Moon Walker

Hello dears,

I've got a problem with converting the date value from dd/mm/yyyy to mm/yyyy
e.g: 25/08/2007 I seek to make query that convert it to 08/2007.

Is there any way to convert the mentioned date value using the query
expression builder?

Waiting your kind reply…..
 
R

Rick Brandt

Moon said:
Hello dears,

I've got a problem with converting the date value from dd/mm/yyyy to
mm/yyyy e.g: 25/08/2007 I seek to make query that convert it to
08/2007.

Is there any way to convert the mentioned date value using the query
expression builder?

Waiting your kind reply...

If this is an actual DateTime DataType and not just a string that happens to
look like a date then it's important to point out that what you see has nothing
to do with what is stored. Formatting is not a "conversion" issue. You simply
want a different format then what you are getting by default. You can do two
things in your query. Either use the format *Property* of that column by
setting it to...

mm/yyyy

That will make the date appear as you want while still keeping it as a DateTime
type. It will still sort as a date and you can still apply criteria to it as a
date. You could also use the Format() *function* in a calculated column with...

AliasName: Format(FieldName,"mm/yyyy")

This will also produce the output with the appearance you want, but the output
will now be a string. It will sort as a string and you would have to apply
criteria to it as if it were a string.

An important distinction is that when using the format property the appearance
does not propagate. If you use your query as the basis of a form or report the
formatting will not be carried over. If you export the query the exported value
will not retain the formatting. When you use the Format() function you are
actually producing a hard string output and its appearance will propagate. Of
course, since it based on an expression the output of the function will not be
editable. The result of the format property would be editable provided nothing
else about the query prevents that.
 
M

Moon Walker

Dear,
First, thank you for the reply ^_^
Second, I want to tell that I seek to get the date with "mm/yyyy" which
means I want to cut and erase "dd/" part of the "dd/mm/yyyy"
..Is there any function may help?? thanx.
 
J

John Spencer

Are you trying to apply criteria to return records for a specific month and
year?
Or
Are you trying to group data by month and year?
Or
Are you trying to do something else?

You have been told how to display just the month and year in the records
returned.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
M

Moon Walker

Dear John ,

Yes I am trying to group data by month and year, and so I want to get the
date field as MM/yyyy value and format,

Thanks
 
M

Michel Walsh

Try:

.... GROUP BY FORMAT( yourDateTimeField, "mm/yyyy" )


Or, in the grid, first line of a new column:

FORMAT( yourDateTimeField, "mm/yyyy" )


and keep the proposed GROUP BY (assuming you are already in a total
query ).


You may find more interesting to use:


FORMAT( yourDateTimeField, "yyyy/mm")


since those STRINGS are now sort-able as if they were the dates they
represent.



Hoping it may help,
Vanderghast, Access MVP
 
M

Moon Walker

--
Regard,


Michel Walsh said:
Try:

.... GROUP BY FORMAT( yourDateTimeField, "mm/yyyy" )


Or, in the grid, first line of a new column:

FORMAT( yourDateTimeField, "mm/yyyy" )


and keep the proposed GROUP BY (assuming you are already in a total
query ).


You may find more interesting to use:


FORMAT( yourDateTimeField, "yyyy/mm")


since those STRINGS are now sort-able as if they were the dates they
represent.



Hoping it may help,
Vanderghast, Access MVP



Thanks dear
but this function return the field to text so i can't sort this field
how can I fix it and return it to number or date values
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DatePart 1
query on date 1
Date Calculation 1
Date Calculation 1
convert date field to month/year 1
Type mismatch in Function 5
DateTime field 3
Calculating a Date 1

Top