Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Changing Date Format in Query to Export Data

Discussion in 'Information Technology' started by John, Jul 28, 2006.

  1. John

    John
    Expand Collapse
    Guest

    I use a query to generate an "exported" file. This query should create all
    output date fields as YYYYMMDD with no associated time (field will only
    contain 8 char for date).
    Table field is defined as: date/time with format: MM/DD/YYYY
    Query field should be defined as: YYYYMMDD

    What steps do i need to perform to enable this type of functionality in a
    query. If requires steps outside of the query please outline.

    thanks,
    John
     
  2. Loading...

    Similar Threads Forum Date
    S Asia Thinking about the big move: The Sikh way of life changing in Khyber-Pakhtunkhwa Breaking News Mar 22, 2014
    Opinion Haanji's Quick Takes on a Changing World (September 23, 2013) Breaking News Sep 22, 2013
    Changing my last name to Kaur... Questions and Answers May 15, 2013
    Life-changing Inspirational Stories Mar 31, 2013
    USA Sikhism and the Changing Electoral Demographic Breaking News Mar 8, 2013

  3. Michael H

    Michael H
    Expand Collapse
    Guest

    Hi John.

    Have you tried formatting the Field in the Query? In the Design view of the
    Query, right-click on your date field and select Properties to view the
    Properties Window. For the Format property, enter yyyymmdd .

    -Michael


    "John" wrote:

    > I use a query to generate an "exported" file. This query should create all
    > output date fields as YYYYMMDD with no associated time (field will only
    > contain 8 char for date).
    > Table field is defined as: date/time with format: MM/DD/YYYY
    > Query field should be defined as: YYYYMMDD
    >
    > What steps do i need to perform to enable this type of functionality in a
    > query. If requires steps outside of the query please outline.
    >
    > thanks,
    > John
     
  4. John

    John
    Expand Collapse
    Guest

    I used the properties on this field; however, when I export the results into
    a tab delimited file I get "19000101 000000" as a format for this field. I
    do not want the time portion only the 8 char date formated as 19000101.
    Also, the value is coming from a table called job with a field name of effdt.



    Thanks for your response,
    John


    "Michael H" wrote:

    > Hi John.
    >
    > Have you tried formatting the Field in the Query? In the Design view of the
    > Query, right-click on your date field and select Properties to view the
    > Properties Window. For the Format property, enter yyyymmdd .
    >
    > -Michael
    >
    >
    > "John" wrote:
    >
    > > I use a query to generate an "exported" file. This query should create all
    > > output date fields as YYYYMMDD with no associated time (field will only
    > > contain 8 char for date).
    > > Table field is defined as: date/time with format: MM/DD/YYYY
    > > Query field should be defined as: YYYYMMDD
    > >
    > > What steps do i need to perform to enable this type of functionality in a
    > > query. If requires steps outside of the query please outline.
    > >
    > > thanks,
    > > John
     
  5. Michael H

    Michael H
    Expand Collapse
    Guest

    In that case, try wrapping your date field in the Format function. In other
    words, instead of having just the fieldname "DateField" in the "Field" row of
    your query design, use this instead:
    FormattedDate: Format([DateField],"yyyymmdd")

    -Michael



    "John" wrote:

    > I used the properties on this field; however, when I export the results into
    > a tab delimited file I get "19000101 000000" as a format for this field. I
    > do not want the time portion only the 8 char date formated as 19000101.
    > Also, the value is coming from a table called job with a field name of effdt.
    >
    >
    >
    > Thanks for your response,
    > John
    >
    >
    > "Michael H" wrote:
    >
    > > Hi John.
    > >
    > > Have you tried formatting the Field in the Query? In the Design view of the
    > > Query, right-click on your date field and select Properties to view the
    > > Properties Window. For the Format property, enter yyyymmdd .
    > >
    > > -Michael
    > >
    > >
    > > "John" wrote:
    > >
    > > > I use a query to generate an "exported" file. This query should create all
    > > > output date fields as YYYYMMDD with no associated time (field will only
    > > > contain 8 char for date).
    > > > Table field is defined as: date/time with format: MM/DD/YYYY
    > > > Query field should be defined as: YYYYMMDD
    > > >
    > > > What steps do i need to perform to enable this type of functionality in a
    > > > query. If requires steps outside of the query please outline.
    > > >
    > > > thanks,
    > > > John
     
  6. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 25 Jul 2006 11:26:03 -0700, John
    <John@discussions.microsoft.com> wrote:

    >I use a query to generate an "exported" file. This query should create all
    >output date fields as YYYYMMDD with no associated time (field will only
    >contain 8 char for date).
    >Table field is defined as: date/time with format: MM/DD/YYYY
    >Query field should be defined as: YYYYMMDD
    >
    >What steps do i need to perform to enable this type of functionality in a
    >query. If requires steps outside of the query please outline.


    You'll need a calculated field in the Query, e.g.

    ExpDate: Format([datefield], "yyyymmdd")

    This will be a Text type field and can be exported in place of the
    date/time field itself.

    John W. Vinson[MVP]
     
  7. John

    John
    Expand Collapse
    Guest

    I tried the fieldname in brackets;however, the query is now prompting me for
    the entry via a dialog screen. I want the query to use a table's values
    instead.
    Table: Job Field: effdt
    How would i format the function call:
    FormattedDate: Format([?????],"yyyymmdd")

    thanks and again I appreciate the quick turnaround.

    "Michael H" wrote:

    > In that case, try wrapping your date field in the Format function. In other
    > words, instead of having just the fieldname "DateField" in the "Field" row of
    > your query design, use this instead:
    > FormattedDate: Format([DateField],"yyyymmdd")
    >
    > -Michael
    >
    >
    >
    > "John" wrote:
    >
    > > I used the properties on this field; however, when I export the results into
    > > a tab delimited file I get "19000101 000000" as a format for this field. I
    > > do not want the time portion only the 8 char date formated as 19000101.
    > > Also, the value is coming from a table called job with a field name of effdt.
    > >
    > >
    > >
    > > Thanks for your response,
    > > John
    > >
    > >
    > > "Michael H" wrote:
    > >
    > > > Hi John.
    > > >
    > > > Have you tried formatting the Field in the Query? In the Design view of the
    > > > Query, right-click on your date field and select Properties to view the
    > > > Properties Window. For the Format property, enter yyyymmdd .
    > > >
    > > > -Michael
    > > >
    > > >
    > > > "John" wrote:
    > > >
    > > > > I use a query to generate an "exported" file. This query should create all
    > > > > output date fields as YYYYMMDD with no associated time (field will only
    > > > > contain 8 char for date).
    > > > > Table field is defined as: date/time with format: MM/DD/YYYY
    > > > > Query field should be defined as: YYYYMMDD
    > > > >
    > > > > What steps do i need to perform to enable this type of functionality in a
    > > > > query. If requires steps outside of the query please outline.
    > > > >
    > > > > thanks,
    > > > > John
     
  8. John

    John
    Expand Collapse
    Guest

    Thanks for your responses, please disregard my last email as it was an error
    on my part.

    john


    "Michael H" wrote:

    > In that case, try wrapping your date field in the Format function. In other
    > words, instead of having just the fieldname "DateField" in the "Field" row of
    > your query design, use this instead:
    > FormattedDate: Format([DateField],"yyyymmdd")
    >
    > -Michael
    >
    >
    >
    > "John" wrote:
    >
    > > I used the properties on this field; however, when I export the results into
    > > a tab delimited file I get "19000101 000000" as a format for this field. I
    > > do not want the time portion only the 8 char date formated as 19000101.
    > > Also, the value is coming from a table called job with a field name of effdt.
    > >
    > >
    > >
    > > Thanks for your response,
    > > John
    > >
    > >
    > > "Michael H" wrote:
    > >
    > > > Hi John.
    > > >
    > > > Have you tried formatting the Field in the Query? In the Design view of the
    > > > Query, right-click on your date field and select Properties to view the
    > > > Properties Window. For the Format property, enter yyyymmdd .
    > > >
    > > > -Michael
    > > >
    > > >
    > > > "John" wrote:
    > > >
    > > > > I use a query to generate an "exported" file. This query should create all
    > > > > output date fields as YYYYMMDD with no associated time (field will only
    > > > > contain 8 char for date).
    > > > > Table field is defined as: date/time with format: MM/DD/YYYY
    > > > > Query field should be defined as: YYYYMMDD
    > > > >
    > > > > What steps do i need to perform to enable this type of functionality in a
    > > > > query. If requires steps outside of the query please outline.
    > > > >
    > > > > thanks,
    > > > > John
     
  9. John

    John
    Expand Collapse
    Guest

    John, thanks for your response. Both you and Michael were able to answer my
    question.

    Regards,
    John

    "John Vinson" wrote:

    > On Tue, 25 Jul 2006 11:26:03 -0700, John
    > <John@discussions.microsoft.com> wrote:
    >
    > >I use a query to generate an "exported" file. This query should create all
    > >output date fields as YYYYMMDD with no associated time (field will only
    > >contain 8 char for date).
    > >Table field is defined as: date/time with format: MM/DD/YYYY
    > >Query field should be defined as: YYYYMMDD
    > >
    > >What steps do i need to perform to enable this type of functionality in a
    > >query. If requires steps outside of the query please outline.

    >
    > You'll need a calculated field in the Query, e.g.
    >
    > ExpDate: Format([datefield], "yyyymmdd")
    >
    > This will be a Text type field and can be exported in place of the
    > date/time field itself.
    >
    > John W. Vinson[MVP]
    >
     
  10. rogereverest

    rogereverest
    Expand Collapse
    Guest

    Mr Vinson you are a hero - Iv'e been living with this problem for five years
    exporting a text file for mailmerge. Thank you so much - we wont have to go
    through mail deleting times anymore.

    "John Vinson" wrote:

    > On Tue, 25 Jul 2006 11:26:03 -0700, John
    > <John@discussions.microsoft.com> wrote:
    >
    > >I use a query to generate an "exported" file. This query should create all
    > >output date fields as YYYYMMDD with no associated time (field will only
    > >contain 8 char for date).
    > >Table field is defined as: date/time with format: MM/DD/YYYY
    > >Query field should be defined as: YYYYMMDD
    > >
    > >What steps do i need to perform to enable this type of functionality in a
    > >query. If requires steps outside of the query please outline.

    >
    > You'll need a calculated field in the Query, e.g.
    >
    > ExpDate: Format([datefield], "yyyymmdd")
    >
    > This will be a Text type field and can be exported in place of the
    > date/time field itself.
    >
    > John W. Vinson[MVP]
    >
     

Share This Page