Re: Datepart function is it the right solution???

Moon Walker wrote:
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.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


.

Relevant Pages

  • Re: Multiple date formats in a Table
    ... could choose a future date so they all sort at the end. ... However if you are set up to use UK format dates of dd/mm/yy, ... converting any entries where you know the full date. ... > iii) Once you've got a function that works, create a new query in design ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Payroll Query
    ... I have tested both functions and the query against some dummy data, ... Yes the text column is in the format hh:mm and no there are no null values, ... function to return it as a Date/Time data type provided that that no value ... Public Function TimeSumAs String ...
    (microsoft.public.access.queries)
  • Re: DCOUNT format question
    ... > When I enter your string into the control on the form, ... >> Yes, your format is incorrect. ... >>>I can get this to work in a totals query but not on a form. ... >>> initials ...
    (microsoft.public.access.forms)
  • Re: Access not accepting functions in queries
    ... Simon, change the last line of your query statement to: ... Judging from your Format() expression, you wanted the date only, so use Date. ... Assuming that your Date field is a Date/Time type, you have now asked JET to match a date against a string. ...
    (microsoft.public.access.queries)
  • RE: Payroll Query
    ... I missed the from clause from the query: ... Yes the text column is in the format hh:mm and no there are no null values, ... function to return it as a Date/Time data type provided that that no value ... Public Function TimeSumAs String ...
    (microsoft.public.access.queries)