putting different dates together into a single group

#1 (**permalink**) 28-Jul-2006, 08:24 AM

let me explain:

I have a query that lists enquiries (one enquiry per record). Each of these
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11831-putting-different-dates-together-into-single.html
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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11831

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 (**permalink**) 28-Jul-2006, 08:24 AM

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/ex...ributors2.html for contact
info.

"scubadiver" wrote in message
news:B047257D-6F4B-432C-914C-E6642BF435CF@microsoft.com...
> let me explain:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11831
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11831
>
> 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?
>
>

#3 (**permalink**) 28-Jul-2006, 08:24 AM

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" 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/ex...ributors2.html for contact
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11831
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11831
> info.
>
>
> "scubadiver" 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 (**permalink**) 28-Jul-2006, 08:24 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11831
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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11831
> http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
> info.
>
>
> "scubadiver" 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 (**permalink**) 28-Jul-2006, 08:24 AM

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/ex...ributors2.html for contact
info.

"scubadiver" 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/ex...ributors2.html for contact
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11831
>> info.
>>
>>
>> "scubadiver" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11831
>> > 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?
>> >
>> >
>>
>>
>>