Only Display records before and after a change in teh value of a f

I have a table that looks like the following

RUNID  |  DATETIME   |    PROCESS
0001       1/1/09 1201      Neck
0001       1/1/09 1202      Neck
0001       1/1/09 1203      Neck
0001       1/1/09 1204      Neck
0001       1/1/09 1205      Neck
0001       1/1/09 1206      Body
0001       1/1/09 1207      Body
0001       1/1/09 1208      Body
0001       1/1/09 1209      Body
0001       1/1/09 1210      Neck
0001       1/1/09 1211      Neck
0001       1/1/09 1212      Neck
0001       1/1/09 1213      Neck
0001       1/1/09 1214      Body
0001       1/1/09 1215      Body
0001       1/1/09 1216      Body
0001       1/1/09 1217      Body
0001       1/1/09 1218      Shutdown

I need to make a query that only displays the recrods before and after a 
change in the process field.  So where it changes from Neck to Body I want to 
see the Last Neck and the first Body. So the above table would ideally yeild 
something like the following.

RUNID  |  DATETIME   |    PROCESS
0001       1/1/09 1201      Neck
0001       1/1/09 1205      Neck
0001       1/1/09 1206      Body
0001       1/1/09 1209      Body
0001       1/1/09 1210      Neck
0001       1/1/09 1213      Neck
0001       1/1/09 1214      Body
0001       1/1/09 1217      Body
0001       1/1/09 1218      Shutdown


I tried to use a Group By but it just lumped all the necks together and and 
all the Bodies together, which doesn't work for my needs since I need to be 
able to see the datetimes when it changes each time.

Thanks for the help
0
Utf
1/11/2010 6:02:01 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
806 Views

Similar Articles

[PageSpeed] 31

Rank oder by datetime over all processes, use your favorite algorithm.

That makes a query q1.



Make a new query:

SELECT q1.dateTime, q1.process
FROM q1 LEFT JOIN q1 AS q1_1
    ON q1.rank = q1_1.rank + 1
        AND q1.process <> q1_1.process




Vanderghast, Access MVP

"dandc0711" <dandc0711@discussions.microsoft.com> wrote in message 
news:EB3BA39A-24CD-4A4E-AEA4-ACEC7607C152@microsoft.com...
>I have a table that looks like the following
>
> RUNID  |  DATETIME   |    PROCESS
> 0001       1/1/09 1201      Neck
> 0001       1/1/09 1202      Neck
> 0001       1/1/09 1203      Neck
> 0001       1/1/09 1204      Neck
> 0001       1/1/09 1205      Neck
> 0001       1/1/09 1206      Body
> 0001       1/1/09 1207      Body
> 0001       1/1/09 1208      Body
> 0001       1/1/09 1209      Body
> 0001       1/1/09 1210      Neck
> 0001       1/1/09 1211      Neck
> 0001       1/1/09 1212      Neck
> 0001       1/1/09 1213      Neck
> 0001       1/1/09 1214      Body
> 0001       1/1/09 1215      Body
> 0001       1/1/09 1216      Body
> 0001       1/1/09 1217      Body
> 0001       1/1/09 1218      Shutdown
>
> I need to make a query that only displays the recrods before and after a
> change in the process field.  So where it changes from Neck to Body I want 
> to
> see the Last Neck and the first Body. So the above table would ideally 
> yeild
> something like the following.
>
> RUNID  |  DATETIME   |    PROCESS
> 0001       1/1/09 1201      Neck
> 0001       1/1/09 1205      Neck
> 0001       1/1/09 1206      Body
> 0001       1/1/09 1209      Body
> 0001       1/1/09 1210      Neck
> 0001       1/1/09 1213      Neck
> 0001       1/1/09 1214      Body
> 0001       1/1/09 1217      Body
> 0001       1/1/09 1218      Shutdown
>
>
> I tried to use a Group By but it just lumped all the necks together and 
> and
> all the Bodies together, which doesn't work for my needs since I need to 
> be
> able to see the datetimes when it changes each time.
>
> Thanks for the help 

0
vanderghast
1/11/2010 6:32:22 PM
The previous query was only supplying the first record of each new sequence, 
to get the first and the LAST record of each sequence:


SELECT q1.dateTime, q1.process
FROM q1 LEFT JOIN q1 AS q1_1
    ON (q1.rank = q1_1.rank + 1
            AND q1.process <> q1_1.process)
        OR
        (q1.rank = q1_1.rank - 1
            AND q1.process <> q1_1.process)


which can list an original record twice if it is the only one of its 
sequence:

>> 0001       1/1/09 1205      Neck
>> 0001       1/1/09 1206      Body
>> 0001       1/1/09 1207      Neck


would list 1206  twice, since it is the first of a new sequence, and also 
the last one of its sequence. Add a DISTINCT to remove its duplicated 
occurence, if you want it to be listed just once.


Vanderghast, Access MVP

"vanderghast" <vanderghast@com> wrote in message 
news:%23qq5yxukKHA.5608@TK2MSFTNGP05.phx.gbl...
> Rank oder by datetime over all processes, use your favorite algorithm.
>
> That makes a query q1.
>
>
>
> Make a new query:
>
> SELECT q1.dateTime, q1.process
> FROM q1 LEFT JOIN q1 AS q1_1
>    ON q1.rank = q1_1.rank + 1
>        AND q1.process <> q1_1.process
>
>
>
>
> Vanderghast, Access MVP
>
> "dandc0711" <dandc0711@discussions.microsoft.com> wrote in message 
> news:EB3BA39A-24CD-4A4E-AEA4-ACEC7607C152@microsoft.com...
>>I have a table that looks like the following
>>
>> RUNID  |  DATETIME   |    PROCESS
>> 0001       1/1/09 1201      Neck
>> 0001       1/1/09 1202      Neck
>> 0001       1/1/09 1203      Neck
>> 0001       1/1/09 1204      Neck
>> 0001       1/1/09 1205      Neck
>> 0001       1/1/09 1206      Body
>> 0001       1/1/09 1207      Body
>> 0001       1/1/09 1208      Body
>> 0001       1/1/09 1209      Body
>> 0001       1/1/09 1210      Neck
>> 0001       1/1/09 1211      Neck
>> 0001       1/1/09 1212      Neck
>> 0001       1/1/09 1213      Neck
>> 0001       1/1/09 1214      Body
>> 0001       1/1/09 1215      Body
>> 0001       1/1/09 1216      Body
>> 0001       1/1/09 1217      Body
>> 0001       1/1/09 1218      Shutdown
>>
>> I need to make a query that only displays the recrods before and after a
>> change in the process field.  So where it changes from Neck to Body I 
>> want to
>> see the Last Neck and the first Body. So the above table would ideally 
>> yeild
>> something like the following.
>>
>> RUNID  |  DATETIME   |    PROCESS
>> 0001       1/1/09 1201      Neck
>> 0001       1/1/09 1205      Neck
>> 0001       1/1/09 1206      Body
>> 0001       1/1/09 1209      Body
>> 0001       1/1/09 1210      Neck
>> 0001       1/1/09 1213      Neck
>> 0001       1/1/09 1214      Body
>> 0001       1/1/09 1217      Body
>> 0001       1/1/09 1218      Shutdown
>>
>>
>> I tried to use a Group By but it just lumped all the necks together and 
>> and
>> all the Bodies together, which doesn't work for my needs since I need to 
>> be
>> able to see the datetimes when it changes each time.
>>
>> Thanks for the help
> 

0
vanderghast
1/11/2010 6:44:42 PM
This looks like it will solve my problem, thanks. Now If I can just figure 
out how to get it to run faster. With each table having nearly a million 
records its a rather slow load of the final query. 


"vanderghast" wrote:

> The previous query was only supplying the first record of each new sequence, 
> to get the first and the LAST record of each sequence:
> 
> 
> SELECT q1.dateTime, q1.process
> FROM q1 LEFT JOIN q1 AS q1_1
>     ON (q1.rank = q1_1.rank + 1
>             AND q1.process <> q1_1.process)
>         OR
>         (q1.rank = q1_1.rank - 1
>             AND q1.process <> q1_1.process)
> 
> 
> which can list an original record twice if it is the only one of its 
> sequence:
> 
> >> 0001       1/1/09 1205      Neck
> >> 0001       1/1/09 1206      Body
> >> 0001       1/1/09 1207      Neck
> 
> 
> would list 1206  twice, since it is the first of a new sequence, and also 
> the last one of its sequence. Add a DISTINCT to remove its duplicated 
> occurence, if you want it to be listed just once.
> 
> 
> Vanderghast, Access MVP
> 
> "vanderghast" <vanderghast@com> wrote in message 
> news:%23qq5yxukKHA.5608@TK2MSFTNGP05.phx.gbl...
> > Rank oder by datetime over all processes, use your favorite algorithm.
> >
> > That makes a query q1.
> >
> >
> >
> > Make a new query:
> >
> > SELECT q1.dateTime, q1.process
> > FROM q1 LEFT JOIN q1 AS q1_1
> >    ON q1.rank = q1_1.rank + 1
> >        AND q1.process <> q1_1.process
> >
> >
> >
> >
> > Vanderghast, Access MVP
> >
> > "dandc0711" <dandc0711@discussions.microsoft.com> wrote in message 
> > news:EB3BA39A-24CD-4A4E-AEA4-ACEC7607C152@microsoft.com...
> >>I have a table that looks like the following
> >>
> >> RUNID  |  DATETIME   |    PROCESS
> >> 0001       1/1/09 1201      Neck
> >> 0001       1/1/09 1202      Neck
> >> 0001       1/1/09 1203      Neck
> >> 0001       1/1/09 1204      Neck
> >> 0001       1/1/09 1205      Neck
> >> 0001       1/1/09 1206      Body
> >> 0001       1/1/09 1207      Body
> >> 0001       1/1/09 1208      Body
> >> 0001       1/1/09 1209      Body
> >> 0001       1/1/09 1210      Neck
> >> 0001       1/1/09 1211      Neck
> >> 0001       1/1/09 1212      Neck
> >> 0001       1/1/09 1213      Neck
> >> 0001       1/1/09 1214      Body
> >> 0001       1/1/09 1215      Body
> >> 0001       1/1/09 1216      Body
> >> 0001       1/1/09 1217      Body
> >> 0001       1/1/09 1218      Shutdown
> >>
> >> I need to make a query that only displays the recrods before and after a
> >> change in the process field.  So where it changes from Neck to Body I 
> >> want to
> >> see the Last Neck and the first Body. So the above table would ideally 
> >> yeild
> >> something like the following.
> >>
> >> RUNID  |  DATETIME   |    PROCESS
> >> 0001       1/1/09 1201      Neck
> >> 0001       1/1/09 1205      Neck
> >> 0001       1/1/09 1206      Body
> >> 0001       1/1/09 1209      Body
> >> 0001       1/1/09 1210      Neck
> >> 0001       1/1/09 1213      Neck
> >> 0001       1/1/09 1214      Body
> >> 0001       1/1/09 1217      Body
> >> 0001       1/1/09 1218      Shutdown
> >>
> >>
> >> I tried to use a Group By but it just lumped all the necks together and 
> >> and
> >> all the Bodies together, which doesn't work for my needs since I need to 
> >> be
> >> able to see the datetimes when it changes each time.
> >>
> >> Thanks for the help
> > 
> 
> .
> 
0
Utf
1/11/2010 11:07:01 PM
Probably the time is spent in finding the ranks.

A 'fast' way to do it, with million of record, is to use an empty table, 
with an autonumber field, and fill the table with your data with an insert 
query having an ORDER BY clause:

INSERT INTO temp( dateTime, process) SELECT dateTime, process FROM somewhere 
ORDER BY dateTime, process


The autonumber should then supply the rank  we look for (note that it is not 
necessary for the rank to start at 1, or at 0, or at any starting value, in 
fact).



Vanderghast, Access MVP



"dandc0711" <dandc0711@discussions.microsoft.com> wrote in message 
news:45EF01C1-36CD-4474-9442-5C708A738172@microsoft.com...
> This looks like it will solve my problem, thanks. Now If I can just figure
> out how to get it to run faster. With each table having nearly a million
> records its a rather slow load of the final query.
>
>
> "vanderghast" wrote:
>
>> The previous query was only supplying the first record of each new 
>> sequence,
>> to get the first and the LAST record of each sequence:
>>
>>
>> SELECT q1.dateTime, q1.process
>> FROM q1 LEFT JOIN q1 AS q1_1
>>     ON (q1.rank = q1_1.rank + 1
>>             AND q1.process <> q1_1.process)
>>         OR
>>         (q1.rank = q1_1.rank - 1
>>             AND q1.process <> q1_1.process)
>>
>>
>> which can list an original record twice if it is the only one of its
>> sequence:
>>
>> >> 0001       1/1/09 1205      Neck
>> >> 0001       1/1/09 1206      Body
>> >> 0001       1/1/09 1207      Neck
>>
>>
>> would list 1206  twice, since it is the first of a new sequence, and also
>> the last one of its sequence. Add a DISTINCT to remove its duplicated
>> occurence, if you want it to be listed just once.
>>
>>
>> Vanderghast, Access MVP
>>
>> "vanderghast" <vanderghast@com> wrote in message
>> news:%23qq5yxukKHA.5608@TK2MSFTNGP05.phx.gbl...
>> > Rank oder by datetime over all processes, use your favorite algorithm.
>> >
>> > That makes a query q1.
>> >
>> >
>> >
>> > Make a new query:
>> >
>> > SELECT q1.dateTime, q1.process
>> > FROM q1 LEFT JOIN q1 AS q1_1
>> >    ON q1.rank = q1_1.rank + 1
>> >        AND q1.process <> q1_1.process
>> >
>> >
>> >
>> >
>> > Vanderghast, Access MVP
>> >
>> > "dandc0711" <dandc0711@discussions.microsoft.com> wrote in message
>> > news:EB3BA39A-24CD-4A4E-AEA4-ACEC7607C152@microsoft.com...
>> >>I have a table that looks like the following
>> >>
>> >> RUNID  |  DATETIME   |    PROCESS
>> >> 0001       1/1/09 1201      Neck
>> >> 0001       1/1/09 1202      Neck
>> >> 0001       1/1/09 1203      Neck
>> >> 0001       1/1/09 1204      Neck
>> >> 0001       1/1/09 1205      Neck
>> >> 0001       1/1/09 1206      Body
>> >> 0001       1/1/09 1207      Body
>> >> 0001       1/1/09 1208      Body
>> >> 0001       1/1/09 1209      Body
>> >> 0001       1/1/09 1210      Neck
>> >> 0001       1/1/09 1211      Neck
>> >> 0001       1/1/09 1212      Neck
>> >> 0001       1/1/09 1213      Neck
>> >> 0001       1/1/09 1214      Body
>> >> 0001       1/1/09 1215      Body
>> >> 0001       1/1/09 1216      Body
>> >> 0001       1/1/09 1217      Body
>> >> 0001       1/1/09 1218      Shutdown
>> >>
>> >> I need to make a query that only displays the recrods before and after 
>> >> a
>> >> change in the process field.  So where it changes from Neck to Body I
>> >> want to
>> >> see the Last Neck and the first Body. So the above table would ideally
>> >> yeild
>> >> something like the following.
>> >>
>> >> RUNID  |  DATETIME   |    PROCESS
>> >> 0001       1/1/09 1201      Neck
>> >> 0001       1/1/09 1205      Neck
>> >> 0001       1/1/09 1206      Body
>> >> 0001       1/1/09 1209      Body
>> >> 0001       1/1/09 1210      Neck
>> >> 0001       1/1/09 1213      Neck
>> >> 0001       1/1/09 1214      Body
>> >> 0001       1/1/09 1217      Body
>> >> 0001       1/1/09 1218      Shutdown
>> >>
>> >>
>> >> I tried to use a Group By but it just lumped all the necks together 
>> >> and
>> >> and
>> >> all the Bodies together, which doesn't work for my needs since I need 
>> >> to
>> >> be
>> >> able to see the datetimes when it changes each time.
>> >>
>> >> Thanks for the help
>> >
>>
>> .
>> 

0
vanderghast
1/12/2010 6:25:53 PM
Reply:

Similar Artilces:

Change entries in popup list for partially typed email addresses
When I start a new email and type in the first few letters of a recipient name or email address, I am given a helpful list of candidate addresses. However, the email addresses of one acquaintance in this list needs to be corrected. How can I do this? I tried looking in the address book, but none of the lists seem to contain this contact. I am using Outlook 2003 on Windows XP. The autocompletion cache has nothing to do with your Contacts. It is simply a cache of your prior recipients. Remove individual addresses from the autocompletion cache by highlighting the entry when pres...

Accessing Record Buffer in MCI
Hi all, I am using MCI to record user audio through mic or any other recording device. While I can play it back and save the audio easily, I have not found any method to directly access recorded buffer. How do i do this? Thanks in advance nightcrawler wrote: > Hi all, > > I am using MCI to record user audio through mic or any other recording > device. While I can play it back and save the audio easily, I have not > found any method to directly access recorded buffer. > > How do i do this? > > Thanks in advance I think you can save your recorded buffer to a mem...

Windows for newly converted records not showing up...
We seems to have a problem with our CRM system where someone tries to "Convert a Lead" and clicks the "Open newly created record(s)" checkbox. The system will convert the lead and create all the necessary records, but it will NOT open the newly created records. At first I thought it was a Pop-Up block issue, but I tried it myself with all Pop-Up blocking turned OFF and it still is happening. Any Ideas? Regards, Eric S. ...

Export chart value
I would like to export a chart without the link source from table data worksheet. Please help me. Thanks:mad: -- excelvn ------------------------------------------------------------------------ excelvn's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32724 View this thread: http://www.excelforum.com/showthread.php?threadid=555867 Hi, Maybe Jon Peltier's page on delinking chart data will help. http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html Cheers Andy excelvn wrote: > I would like to export a chart without the link source from table da...

Wrong time displayed
When I view my calendar in the office using Outlook, the meetings are listed correctly. When I login to our server remotely across the Internet, my mail works fine, but my calendar displays all times 8 hours off ( a 9 am meeting displays as a 5 pm meeting) !! Outlook has the option of maintaining separate time zones so you can, for example, see what time your 5PM conference call is going to seem to that person in, say, London. Not sure about your setup, but you might have either a second time zone set or be switching between them. In general, the following is how to set one up. If you...

voting buttons not recording.
On occasions when voting Yes/No buttons (Outlook 2002) have been used the replies do not always tally on the original sender's e-mail. Any suggestions as to why and any fix. ...

use the current time to return a value
Simple formula? if the time is >=07:00:00<15:00:00, G$1. = 1 if time >=15:00:00 < 23:00:00 G$1 = 2 if Time >=23:00:00 < 07:00:00 G$1 = 3 I have tried to do this a few different ways and have been unsuccessful. Could someone help please? One way: G1: =IF(OR(MOD(NOW(),1)<7/24,MOD(NOW(),1)>=23/24), 3, IF(MOD(NOW(),1)>=15/24, 2, 1)) In article <1133191901.819183.53950@g14g2000cwa.googlegroups.com>, sjd371@netzero.com wrote: > Simple formula? if the time is >=07:00:00<15:00:00, G$1. = 1 if time > >=15:00:00 < 23:00:00 G$1 = 2 if Time...

changing tcp port used by exchange 5.5?
Hello, We are trying to find out if it is possible to change the tcp port that exchange 5.5 uses to send outgoing (internet bound) emails? specifically, we are trying to have our internal exchange 5.5 server send all outbound emails to our unix based/postfix server using a port other then 25. I have search both technet and google but seem to only find articles relating to inbound or client based issues. can you point me in the right direction/tech article? I have to admit I don't know if this applies to 5.5, but maybe it'll help if it's somewhat similar. This is how it'...

Do not want to display negative formula results
If the result of a formula is negative, how do I display it as a zero? A couple of ways =IF(yourformula<0,0,yourformula) =MAX(yourformula,0) On Sun, 12 Sep 2004 11:29:02 -0700, "plabi" <plabi@discussions.microsoft.com> wrote: >If the result of a formula is negative, how do I display it as a zero? IF(A1-B1<0,0,A1-B1) "plabi" <plabi@discussions.microsoft.com> wrote in message news:4E229238-BE46-4A96-A9D2-6104B9C2DC73@microsoft.com... > If the result of a formula is negative, how do I display it as a zero? ...

Offline Outlook Synchronization should allow ALL records
I finally got my Great Plains integration with CRM working, and I have to say that I am very disappointed that my customers do not synch with Outlook. I just realized that only records that are "owned" by me will synchronize, and not my entire database. All of my Great Plains records are owned by the Integration user. I'm not sure that if I change ownership to myself, whether that will mess up the integration with Great Plains. (does anyone know the answer to this?) My suggestion is that all users should be able to synchronize all accounts, contacts & leads with th...

Display Empty Cells as Bar on Bar Chart
I'm creating a spreadsheet to track patch deployment and it has five phases: Initial, Test, Pilot, Due Date and Production. Most of the cells have dates in them but some don't. I would like for the cells with no dates to either show a bar associated with the phase with no date or show nothing, meaning there would be a break in the bar. Is this possible? url:http://www.ureader.com/gp/1029-1.aspx ...

how to change range for dynamic chart in excel 2000 with button?
i need a help please. i have problem in excel chart. i add data to the table. and then if i want to update my chart i need to change the range too, isn't it? is there any solution for that? i've already use dynamic chart. but it always show data from Jan04 till Jan05. If I add Feb05 it will change from Feb04 to Feb05. what about if i want to see Dec03 till Dec04? is there any command or something else like using macro or vb? thank you in advance.. ivan Ivan, You should be able to set the dynamic chart to show just about any range you want. These articles should help: http://pu...

Changing English text to Spanish text
Hello can anyone help me out with this... I'm creating a newsletter, and want to see if there is a way to convert the english text to spanish... Any help would be great.. Thank you Gilbert Saenz sagi300@dshs.wa.gov There are translation services available for a cost. "Gilbert Saenz" <anonymous@discussions.microsoft.com> wrote in message news:184001c3fbd5$f566d9b0$a001280a@phx.gbl... > Hello can anyone help me out with this... I'm creating a > newsletter, and want to see if there is a way to convert > the english text to spanish... Any help would be great.....

How can I get Positive values only from the random number generat.
I am generating a random group of numbers using Excel's add-in "Random Number Generator". I am using the normal distribution, but I want positive values only. I know that the test I am doing will work with the negative values I have generated, because the mean/stdev are correct. However, the activity for which the numbers are associated can never be negative, so my presentation will be less effective. Any ideas? You could use the randbetween function and specify a positive range i.e. =Randbetween(1,10000000) HTH Jimbola "Markw3700" wrote: > I am gene...

Excell
How do I change font colour in a footer?? There are options to change the font, size, underline etc but not colour... Can anyone help? You can't change the font colour in a footer. If you really need coloured text, you could print just the footer using Word, then print the Excel file onto those pages. Or, if you have Excel 2002, you can insert a coloured picture into the footer, so you could create a graphic with the text, and insert that. cinabar wrote: > How do I change font colour in a footer?? > > There are options to change the font, size, underline etc > but not...

hide colum based on cell value on each worksheets
I have a workbook with several worksheets. I am trying to write a VBA macro that would look at row 5 to see if any cells indicate "Hide column". If it does, it would hide all the columns indicating "Hide column" on this worksheet. Then it would continue the same process to the next worksheet, and the next one ... I currently have the macro working for the individual worksheet (see below), but I would like to have it work to do all the worksheets of the workbook. Sub HideColumn() Application.ScreenUpdating = False Application.Calculation = xlCalculation...

help with combo box display...plz...
i have a multicolumn combo box...with columns FName, MName, and LName...how can i enable it to display the 3 values of the column when a row is selected?? it's like that i'm merging the 3 values int the text box display of the combo box...please....thank yu -- ai® On Fri, 20 Apr 2007 08:30:20 GMT, ai_enjoi wrote: > i have a multicolumn combo box...with columns FName, MName, and LName...how > can i enable it to display the 3 values of the column when a row is selected?? > it's like that i'm merging the 3 values int the text box display of the combo > box......

When message changes from text to HTML format, font is not the default
Hi, Environment: My outlook 2003 default format is HTML. The Default fond for new/reply messages is Arial. The Problem: I get a message in text format. When I manually change the format of this message to HTML, the font of the message is Times Roman. How can I make outlook automatically use the default font (Arial) as the font for messages changed from text to HTML ? Thanks, Ron. -- ronbarak ...

Textbox value automatically selected, How?
This has to be a simple one. When opening a userform I would like the value stored in the firs textbox to be selected. Therefore I can just start typing over th current value. Currently I have to click and drag the mouse over th old value to select it -- Message posted from http://www.ExcelForum.com Kevlar Try Private Sub UserForm_Activate() Me.TextBox1.SelStart = 0 Me.TextBox1.SelLength = Len(Me.TextBox1) End Su -- Message posted from http://www.ExcelForum.com That works well. Thanks Now, how do I get the list of choices in a listbox to default to th top ite -- Message posted from...

switching the axes, categories to values and vice versa
I'm trying to create a statistical add-on that makes a dot plot in excel 07. right now i'm using a line chart with markers to create the values on the y-axis and the categories on the x-axis, aka text items on the bottom. i'm trying to figure out a way to switch these two, so that the categories are on the y-axis and the values are on the x-axis while still maintaining my data so that if someone changes it, the graph updates. i've looked at the peltier pages and can't seem to get the bar-xy combo to work. i create my secondary axis, move it over, and change th...

How to not have formula change after inserting column from another sheet
One file - a few sheets. I run a macro weekly to copy column B of my CURRENT PORTFOLIO sheet and insert that data between Col I and J (Moving older data to the right - off the screen.) I then populate col B with this weeks data. That works fine, BUT another sheet of that file ( GRAPH) uses that data to track a few moving averages. To keep the GRAPH sheet accurate, after the macro I then have to modify the contents for the SOURCE field of the graph because NOW $I$1 becomes $J$1 and $I$28 becomes $J$28. I can't use an INDIRECT command - I get an error. Name = Weekly Moving Average, ...

Formatting when combining text & value in a cell
The value of my cell is ="Product Margin is "&Z5 Z5 is 63.6% (in Z5) but 0.63653676693 in my combined cell. How do I format the percent when combining text & value? Thank you for your time. One way: ="Product Margin is " & TEXT(Z5, "0.0%") In article <1157984365.490796.307300@i3g2000cwc.googlegroups.com>, "John13" <johnasmith13@gmail.com> wrote: > The value of my cell is > > ="Product Margin is "&Z5 > > Z5 is 63.6% (in Z5) but 0.63653676693 in my combined cell. How do I > format the...

code wild card value for form variable
i use a check box in an option group to generate a specific value for a variable. using the following code If Forms![fee sched form]![select region] = 2 Then Forms![fee sched form]![region var] = "Roch" End If If Forms![fee sched form]![select region] = 3 Then Forms![fee sched form]![region var] = "CNY" End If this variable is hidden on the form and value is used a query. how do i code this, so i can put "*" in the variable and thereby get all values in the query? the query code generates like "*" but i do not know how to code via the fo...

recurring deposit with wrong value
Isn't it possible to show the missing amount of a recurring value (and maybe the missing deposits afterwards?) It does show the next missing recurring value but not the difference between the expected and received value. My situation: I've got a recurring deposit that misses 100 euro for one deposit and misses two deposits a few months later. So at least i'd like to know from money that i miss the 100 euro and maybe some othere afterwards or i'd like to know that i miss the 100 euro and all other payments afterwards. Thanks for your reply. Somnus ...

Changing Quote status from Active to Draft
After we re did our picklists for reps (were originally not working - not done right), it erased that data from all our past quotes. i need to run a work flow rule to chang the Quotes status from Active to Draft, but don't want to do that if it will affect the orders created. Can someone please help me with this. thank you It really didn't "erase" the data. The problem most likely is that you removed the entries for the previously saved values. You could just update the numeric value of the underlying records with SQL (not supported, but in this case safe). Othe...