Welcome to SPN

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

Sign Up Now!

putting different dates together into a single group

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

  1. scubadiver

    scubadiver
    Expand Collapse
    Guest

    let me explain:

    I have a query that lists enquiries (one enquiry per record). Each of these
    enquiries were on a certain date. The list will start on a Friday and end on
    a thursday.

    I have created a column in the query so that "1" is equal to Friday and so
    up to "7" equalling thursday.

    What I need to do is change the "2" and "3" into a "4" and leave 1,5,6 and 7
    alone.

    Or is there an easier way?
     
  2. Loading...

    Similar Threads Forum Date
    SciTech Putting Sunshine In The Tank Breaking News Jul 9, 2011
    Pacific Anuradha Koirala: Putting a Stop to Trade in Women Breaking News May 24, 2011
    Giving Up Your Spiritual Journey (and Putting Down Roots) Spiritual Articles May 5, 2010
    Putting Your Faith in God Spiritual Articles Feb 13, 2006
    Putting the Fallouts of the Islamic Invasion and British Occupation of India in Perspective (News Fr Interfaith Dialogues Jun 2, 2005

  3. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi.

    > What I need to do is change the "2" and "3" into a "4" and leave 1,5,6 and
    > 7
    > alone.


    Use an update query. Try:

    UPDATE tblStuff
    SET EnquiryDay = 4
    WHERE (EnquiryDay IN (2, 3))

    .. . . where tblStuff is the name of the table, and EnquiryDay is the name of
    the column with the numbers corresponding to the days of the week.

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    info.


    "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    news:B047257D-6F4B-432C-914C-E6642BF435CF@microsoft.com...
    > let me explain:
    >
    > I have a query that lists enquiries (one enquiry per record). Each of
    > these
    > enquiries were on a certain date. The list will start on a Friday and end
    > on
    > a thursday.
    >
    > I have created a column in the query so that "1" is equal to Friday and so
    > up to "7" equalling thursday.
    >
    > What I need to do is change the "2" and "3" into a "4" and leave 1,5,6 and
    > 7
    > alone.
    >
    > Or is there an easier way?
    >
    >
     
  4. Immanuel Sibero

    Immanuel Sibero
    Expand Collapse
    Guest

    Same idea but if dealing with a query then just add another column to the
    query and enter the following:

    =iif([EnquiryDay] IN ("2","3"),"4",[EnquiryDay])


    Immanuel Sibero



    "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@Spameater.orgZERO_SPAM> wrote in
    message news:O1spGvTlGHA.1240@TK2MSFTNGP05.phx.gbl...
    > Hi.
    >
    > > What I need to do is change the "2" and "3" into a "4" and leave 1,5,6

    and
    > > 7
    > > alone.

    >
    > Use an update query. Try:
    >
    > UPDATE tblStuff
    > SET EnquiryDay = 4
    > WHERE (EnquiryDay IN (2, 3))
    >
    > . . . where tblStuff is the name of the table, and EnquiryDay is the name

    of
    > the column with the numbers corresponding to the days of the week.
    >
    > HTH.
    > Gunny
    >
    > See http://www.QBuilt.com for all your database needs.
    > See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    > http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    > info.
    >
    >
    > "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    > news:B047257D-6F4B-432C-914C-E6642BF435CF@microsoft.com...
    > > let me explain:
    > >
    > > I have a query that lists enquiries (one enquiry per record). Each of
    > > these
    > > enquiries were on a certain date. The list will start on a Friday and

    end
    > > on
    > > a thursday.
    > >
    > > I have created a column in the query so that "1" is equal to Friday and

    so
    > > up to "7" equalling thursday.
    > >
    > > What I need to do is change the "2" and "3" into a "4" and leave 1,5,6

    and
    > > 7
    > > alone.
    > >
    > > Or is there an easier way?
    > >
    > >

    >
    >
     
  5. scubadiver

    scubadiver
    Expand Collapse
    Guest

    I used a similar one to Immanuel:

    Group: IIf(([diff]=2) Or ([diff]=3),4,[diff])

    and it does work but I realise its not helpful for producing a real date to
    put into a report.

    So using your idea where exactly do I put the code but using real dates (if
    possible)

    cheers.



    "'69 Camaro" wrote:

    > Hi.
    >
    > > What I need to do is change the "2" and "3" into a "4" and leave 1,5,6 and
    > > 7
    > > alone.

    >
    > Use an update query. Try:
    >
    > UPDATE tblStuff
    > SET EnquiryDay = 4
    > WHERE (EnquiryDay IN (2, 3))
    >
    > .. . . where tblStuff is the name of the table, and EnquiryDay is the name of
    > the column with the numbers corresponding to the days of the week.
    >
    > HTH.
    > Gunny
    >
    > See http://www.QBuilt.com for all your database needs.
    > See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    > http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    > info.
    >
    >
    > "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    > news:B047257D-6F4B-432C-914C-E6642BF435CF@microsoft.com...
    > > let me explain:
    > >
    > > I have a query that lists enquiries (one enquiry per record). Each of
    > > these
    > > enquiries were on a certain date. The list will start on a Friday and end
    > > on
    > > a thursday.
    > >
    > > I have created a column in the query so that "1" is equal to Friday and so
    > > up to "7" equalling thursday.
    > >
    > > What I need to do is change the "2" and "3" into a "4" and leave 1,5,6 and
    > > 7
    > > alone.
    > >
    > > Or is there an easier way?
    > >
    > >

    >
    >
    >
     
  6. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi.

    > but I realise its not helpful for producing a real date to
    > put into a report.


    I'm not sure I understand your question. Do you mean that you want to
    convert the numbers 1 through 7 to days of the week, i.e., "Wednesday" or
    replace them with an actual date, such as 6/21/2006?

    > So using your idea where exactly do I put the code but using real dates
    > (if
    > possible)


    First, make a backup of the table, just in case something goes wrong,
    because the update query will permanently change the values in that column.
    (I'm assuming that this is what you want to do.) Create a new query and go
    to the SQL View pane (View -> SQL View menu), then paste the code into the
    window. Replace the table name and column name with the names of your table
    and column. Hold off on the next step (running the query) until we clarify
    exactly what you mean by "using real dates."

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    info.


    "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    news:88F69204-9850-40EB-86A6-2E534E1DF4A3@microsoft.com...
    >I used a similar one to Immanuel:
    >
    > Group: IIf(([diff]=2) Or ([diff]=3),4,[diff])
    >
    > and it does work but I realise its not helpful for producing a real date
    > to
    > put into a report.
    >
    > So using your idea where exactly do I put the code but using real dates
    > (if
    > possible)
    >
    > cheers.
    >
    >
    >
    > "'69 Camaro" wrote:
    >
    >> Hi.
    >>
    >> > What I need to do is change the "2" and "3" into a "4" and leave 1,5,6
    >> > and
    >> > 7
    >> > alone.

    >>
    >> Use an update query. Try:
    >>
    >> UPDATE tblStuff
    >> SET EnquiryDay = 4
    >> WHERE (EnquiryDay IN (2, 3))
    >>
    >> .. . . where tblStuff is the name of the table, and EnquiryDay is the
    >> name of
    >> the column with the numbers corresponding to the days of the week.
    >>
    >> HTH.
    >> Gunny
    >>
    >> See http://www.QBuilt.com for all your database needs.
    >> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    >> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    >> info.
    >>
    >>
    >> "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    >> news:B047257D-6F4B-432C-914C-E6642BF435CF@microsoft.com...
    >> > let me explain:
    >> >
    >> > I have a query that lists enquiries (one enquiry per record). Each of
    >> > these
    >> > enquiries were on a certain date. The list will start on a Friday and
    >> > end
    >> > on
    >> > a thursday.
    >> >
    >> > I have created a column in the query so that "1" is equal to Friday and
    >> > so
    >> > up to "7" equalling thursday.
    >> >
    >> > What I need to do is change the "2" and "3" into a "4" and leave 1,5,6
    >> > and
    >> > 7
    >> > alone.
    >> >
    >> > Or is there an easier way?
    >> >
    >> >

    >>
    >>
    >>
     

Share This Page