Ordering by number and text

I use a report to print out checklists that in the detail section have item 
number to delineate each checklist item.  The item numbers as an example are 
1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on.  The report is 
grouped by checklist section and the grouping works perfectly but when the 
report is printed the item numbers are ordered in this order 1-1, 1-10, 1-11, 
1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9.

Obviously I want the the order to be in proper numerical sequence where 1-10 
comes after 1-9. but even replacing the '-' with a decimal point doesn't 
work.  I can't change the field type to a number because of the use of 
letters.  

Is there a relatively easy way to get the order I need from the report.

Thanks
Dan
0
Utf
1/17/2008 4:35:09 PM
access 16762 articles. 3 followers. Follow

9 Replies
697 Views

Similar Articles

[PageSpeed] 0

"DanWH" <DanWH@discussions.microsoft.com> wrote in message 
news:8A3F8587-BD01-4A41-9F63-81A989C017A7@microsoft.com...
>I use a report to print out checklists that in the detail section have item
> number to delineate each checklist item.  The item numbers as an example 
> are
> 1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on.  The report 
> is
> grouped by checklist section and the grouping works perfectly but when the
> report is printed the item numbers are ordered in this order 1-1, 1-10, 
> 1-11,
> 1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9.
>
> Obviously I want the the order to be in proper numerical sequence where 
> 1-10
> comes after 1-9. but even replacing the '-' with a decimal point doesn't
> work.  I can't change the field type to a number because of the use of
> letters.
>
> Is there a relatively easy way to get the order I need from the report.


Just a thought, but you might order by two values:

    Val(Replace([ItemNumber], "-", "."))
    [ItemNumber]

So the first would be the numeric value of the translated item number, and 
the second would be the item number alone.  That should cover both numeric 
sequencing and text suffixes -- if it works, of course. <g>

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

0
Dirk
1/17/2008 4:54:25 PM
Dirk, that worked perfectly for the form, but it won't do anything when I try 
to implement in the report design.  I'm trying to figure it out, but any help 
you can offer will be appreciated.

Dan

"Dirk Goldgar" wrote:

> "DanWH" <DanWH@discussions.microsoft.com> wrote in message 
> news:8A3F8587-BD01-4A41-9F63-81A989C017A7@microsoft.com...
> >I use a report to print out checklists that in the detail section have item
> > number to delineate each checklist item.  The item numbers as an example 
> > are
> > 1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on.  The report 
> > is
> > grouped by checklist section and the grouping works perfectly but when the
> > report is printed the item numbers are ordered in this order 1-1, 1-10, 
> > 1-11,
> > 1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9.
> >
> > Obviously I want the the order to be in proper numerical sequence where 
> > 1-10
> > comes after 1-9. but even replacing the '-' with a decimal point doesn't
> > work.  I can't change the field type to a number because of the use of
> > letters.
> >
> > Is there a relatively easy way to get the order I need from the report.
> 
> 
> Just a thought, but you might order by two values:
> 
>     Val(Replace([ItemNumber], "-", "."))
>     [ItemNumber]
> 
> So the first would be the numeric value of the translated item number, and 
> the second would be the item number alone.  That should cover both numeric 
> sequencing and text suffixes -- if it works, of course. <g>
> 
> -- 
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
> 
> (please reply to the newsgroup)
> 
0
Utf
1/17/2008 7:44:02 PM
"DanWH" <DanWH@discussions.microsoft.com> wrote in message 
news:4123620C-CB97-4C47-BFE6-26E8F4B29DF8@microsoft.com...
> Dirk, that worked perfectly for the form, but it won't do anything when I 
> try
> to implement in the report design.  I'm trying to figure it out, but any 
> help
> you can offer will be appreciated.

You'd do this in the report's Sorting & Grouping dialog.  Underneath the 
actual last group entry, create one entry for  the Val() expression, and a 
second one for the field.  Neither of these entries should have a group 
header or footer.

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

0
Dirk
1/18/2008 2:39:09 AM
Dirk,

When I enter Val(Replace([Item Number],"-",".")) in a sort group as you 
asked, I get the following error:

" Syntax error (comma) in query expression [Val(Replace([Item 
Number],"-","].["))]'. "

Please advise and I do appreciate all your help.
Dan

"Dirk Goldgar" wrote:

> "DanWH" <DanWH@discussions.microsoft.com> wrote in message 
> news:4123620C-CB97-4C47-BFE6-26E8F4B29DF8@microsoft.com...
> > Dirk, that worked perfectly for the form, but it won't do anything when I 
> > try
> > to implement in the report design.  I'm trying to figure it out, but any 
> > help
> > you can offer will be appreciated.
> 
> You'd do this in the report's Sorting & Grouping dialog.  Underneath the 
> actual last group entry, create one entry for  the Val() expression, and a 
> second one for the field.  Neither of these entries should have a group 
> header or footer.
> 
> -- 
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
> 
> (please reply to the newsgroup)
> 
0
Utf
1/18/2008 1:21:02 PM
"DanWH" <DanWH@discussions.microsoft.com> wrote in message 
news:4F01F412-C01F-447C-96C3-6AE7B6FC21C6@microsoft.com...
> Dirk,
>
> When I enter Val(Replace([Item Number],"-",".")) in a sort group as you
> asked, I get the following error:
>
> " Syntax error (comma) in query expression [Val(Replace([Item
> Number],"-","].["))]'. "


Hmm.  Try sticking an equals sign on the front:

    =Val(Replace([Item Number],"-","."))

I'm trusting that the name of the field is, in fact, "Item Number".

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

0
Dirk
1/18/2008 3:03:23 PM
Dirk,

It worked perfectly but only after I made a slight change

Instead of:  =Val(Replace[ItemNumber],"-","."))

I used:  =Val(Replace[ItemNUmber],"-",""))

Then put the next group level as just ItemNumber.

Thank You Very Much.

Dan

"Dirk Goldgar" wrote:

> "DanWH" <DanWH@discussions.microsoft.com> wrote in message 
> news:4F01F412-C01F-447C-96C3-6AE7B6FC21C6@microsoft.com...
> > Dirk,
> >
> > When I enter Val(Replace([Item Number],"-",".")) in a sort group as you
> > asked, I get the following error:
> >
> > " Syntax error (comma) in query expression [Val(Replace([Item
> > Number],"-","].["))]'. "
> 
> 
> Hmm.  Try sticking an equals sign on the front:
> 
>     =Val(Replace([Item Number],"-","."))
> 
> I'm trusting that the name of the field is, in fact, "Item Number".
> 
> -- 
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
> 
> (please reply to the newsgroup)
> 
0
Utf
1/18/2008 3:56:03 PM
"DanWH" <DanWH@discussions.microsoft.com> wrote in message 
news:FC5631A2-9728-42FB-B639-70406471DF5D@microsoft.com...
> Dirk,
>
> It worked perfectly but only after I made a slight change
>
> Instead of:  =Val(Replace[ItemNumber],"-","."))
>
> I used:  =Val(Replace[ItemNUmber],"-",""))
>
> Then put the next group level as just ItemNumber.

Great!  But now that I think about it further, I'm still not sure it's 
right -- I may have given you a bad expression to begin with.  Could you 
please verify that item numbers 1-9 and 1-10 sort correctly?  I have a 
feeling they won't.

> Thank You Very Much.

You're welcome ... but we may not be done.

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

0
Dirk
1/18/2008 6:05:20 PM
Dirk, I think you're right, since 1.1 (1-10) will be in front of 1.9 (1-9). 
I didn't have any trouble with report sorting and grouping, by the way.
I parsed the field.  In query design view, two calculated fields:
FirstPart: Val(Left([ItemNumber],InStr([ItemNumber],"-")-1))
SecondPart: 
Val(Right([ItemNumber],Len([ItemNumber])-InStr([ItemNumber],"-")))
Then I ordered by those two fields, followed by ItemNumber.  I think it 
works as intended, but I tested quickly, so I may not have considered all 
possibilities.
I would probably make a query with those fields, then use those fields in 
the report sorting and grouping, rather than add the expresssions directly 
to the sorting and grouping, but I expect it doesn't matter except as a 
personal preference choice.

"Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message 
news:elUKzyfWIHA.1164@TK2MSFTNGP02.phx.gbl...
> "DanWH" <DanWH@discussions.microsoft.com> wrote in message 
> news:FC5631A2-9728-42FB-B639-70406471DF5D@microsoft.com...
>> Dirk,
>>
>> It worked perfectly but only after I made a slight change
>>
>> Instead of:  =Val(Replace[ItemNumber],"-","."))
>>
>> I used:  =Val(Replace[ItemNUmber],"-",""))
>>
>> Then put the next group level as just ItemNumber.
>
> Great!  But now that I think about it further, I'm still not sure it's 
> right -- I may have given you a bad expression to begin with.  Could you 
> please verify that item numbers 1-9 and 1-10 sort correctly?  I have a 
> feeling they won't.
>
>> Thank You Very Much.
>
> You're welcome ... but we may not be done.
>
> -- 
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
> 

0
BruceM
1/18/2008 8:50:53 PM
Dirk, Everything sorts correctly it sorts 1-8 , 1-9, 1-10, 1-11 and so forth. 
 I still have a lot of data yet to input but with all the data that is in 
there now, it is working the way I want it to.  Hopefull it will keep working 
correctly.

Thanks
Dan

"Dirk Goldgar" wrote:

> "DanWH" <DanWH@discussions.microsoft.com> wrote in message 
> news:FC5631A2-9728-42FB-B639-70406471DF5D@microsoft.com...
> > Dirk,
> >
> > It worked perfectly but only after I made a slight change
> >
> > Instead of:  =Val(Replace[ItemNumber],"-","."))
> >
> > I used:  =Val(Replace[ItemNUmber],"-",""))
> >
> > Then put the next group level as just ItemNumber.
> 
> Great!  But now that I think about it further, I'm still not sure it's 
> right -- I may have given you a bad expression to begin with.  Could you 
> please verify that item numbers 1-9 and 1-10 sort correctly?  I have a 
> feeling they won't.
> 
> > Thank You Very Much.
> 
> You're welcome ... but we may not be done.
> 
> -- 
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
> 
> (please reply to the newsgroup)
> 
> 
0
Utf
1/18/2008 9:42:01 PM
Reply:

Similar Artilces:

How do I split one cell of 6 numbers into two cells of 3 and 3?
I have a column of data that has six numbers in each cell, however I need to split this into two columns, where the first column is the first three digits and the second column is the last three. There is no separator (space, comma or dash) between the numbers. Any suggestions? Use Text To Columns. Select your column of data, then: <Data> <TextToColumns> Click "Fixed Width", then <Next>, Click in the data to display the "break line", and drag it to separate the numbers as desired. Then <Finish> -- HTH, RD =================================...

Graph with text
I have a status field "Open, Working, On-Hold, or Done" and a second column for project name. I want to create a chart that shows the project name on the x-axis and the status on the Y axis. THIS IS A SECOND REQUEST, please help. DDBeards You need to convert the status to a numerical scale (1,2,3,4), plot X=project vs Y=numerical status, and finally fix up the vertical axis: http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ DDBe...

Number formatting in chart
The secondary axis in my chart is dollar value. It is showing in thousands, which is what I want, but the data labels I would like it to show 177 only, for example, instead of 177,883. It would be understood that the labels are in thousands. Is this possible? Connie Okay, I guess I didn't make myself very clear. I will try to take a different approach. How do I customize the format of numbers in a column so that it rounds 177,883 to 178? Connie "Connie Martin" wrote: > The secondary axis in my chart is dollar value. It is showing in thousands, > which is ...

How do I arch a line of text within a banner?
I am trying to design a banner that will have two lines of text. Rather than both lines being straight, I would like to arch the top line over the second line so that I can insert artwork between the two lines of text. Can this be accomplished with this program? If not, is there a program available the I could use instead. Thank You. *Brandon* numberonesignguy wrote: > I am trying to design a banner that will have two lines of text. > Rather than both lines being straight, I would like to arch the top > line over the second line so that I can insert artwork between the > two...

Select All Pictures and Change Wrapping Style to Behind Text
I am trying to get a macro that can change the wrapping style to behind text for all the pictures in the document. It would also be great if I could get this macro to change a lot of other properties too. For example, I want all the photos behind text, center horizontal alignment, height = 4", width = proportional. It would also be great if this macro could add a text box under each picture which was grouped with its respective picture. Any advice on any of this would be greatly appreciated. Funny you should ask this now. I was just fighting with the same problem yeste...

Why doesn't the text fit its self around my graphics?
I'm using Publisher 2003. I've inserted a clipart but the text goes under the graphic. I want the text to flow around the graphic. PLEASE help me get the text to flow around my graphic. I've used Pub 2000 and haven't run into this problem....can't find anything to fix this! Thanks in advance!!! -- Kelvin Using Pub 2000 here also Kelvin, Give this a try. Put the clipart on top layer. Use the icons in the tool bar that look like three little squares. One sends the active text or image box to the back and the other brings them to the front. -- Don -------- Vancou...

Inserting page numbers in a cell
Certain data in my spreadsheet has to reference to a specific page number when the spreadsheet is printed. I can't find a formula that would provide information about a page number (something like =CELL("filename")) Any help would be appreciated. need something similar. i have a list of manufacturers' and their respective products on a sheet. items on this list are constantly being added and removed. this list is also filtered so that certain data can be printed. On a master sheet I want to be able to list the pages that a particular manufacturer's items are on, ...

Text to Columns
500+ sentences, I need to seperate (text to columns) after the first "-" only. There are subsequent "-" but I only want it seperated at the first. And when I seperate it in the second column I do not want a space preceeding the text. Can you give advice on this, any help would be much appreciated! Thanks Sample Data AIR TAXI - An aircraft operator who conducts operations for hire or compensation in accordance with FAR Part 135 in an aircraft with 30 or fewer passenger seats and a payload capacity of 7,500# or less. An air taxi operates on an on-dem...

selecting text and spikes
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I want to do the usual: insert the cursor, click/drag to highlight, press F3 to copy, then paste. <br><br>But when I click, Word selects multiple words or lines, when I attempt to copy (F3), the text deletes. When I look under the Edit menu, it says &quot;Undo spike.&quot; <br><br>Yikes, spikes? what am i doing? or what is word doing? <br><br>BTW, when i tried to enter text in Word help, i couldn't until i restarted and reset the pram. F3 is the Spike key, not ...

Writing Text File with code UTF-8
Hello Does somebody know how to write a Text File with code UTF-8 from Dexterity? I know that are 3 functions to write text files: TextFile_Writeline() TextFile_WriteDOS() TextFile_WriteText() But there is not therein a parameter to achieve text file be written with code UTF-8. I=92ll appreciate your guide. Best regards. ------=_NextPart_0001_A41C49B3 Content-Type: text/plain Content-Transfer-Encoding: 7bit Dexterity does not support double byte or unicode. You might need to use COM or win32 dll calls to get the OS to write the file for you. The difference between WriteLine and WriteDOS i...

Ordering legend
I have a line graph with a number of series (six). The graph als incorporates a legend. The problem is that I want to modify the order of the series as the are displayed in the legend. Currently the legend lists each of th series in the order that the series were added to the graph. I hav been trying to reorder the legend so that it lists each series in a order that I want but to no avail. Can anyone help -- Message posted from http://www.ExcelForum.com Select the chart, and select one of the series. Choose Format>Selected Data Series Select the Series Order tab Select one of the serie...

Order will not transfer to invoice
We have a sales order that has been allocated and fufilled, but will not transfer to invoice. We are using GP 7.5, SQL 2000. All service packs are up to date. The original issue with this order is that on the sales transaction entry screen, once expanded, there were no amounts in the Qty to Invoice and in the Qty Fulfilled. We were able to update the tables, and correct this information. When you look at the Sales Qty Status screen, it shows that the amounts were transferred to invoice. We can not locate an invoice number anywhere for that customer / amount. When you try and tr...

I can not copy text into a brochure without another textbox opening
Hi all, Every time I try to copy and past into a textbox already created by a publisher template, another textbox is created by defalut to add the text into it. I want to just add the text into the textbox already within the template like you could with Publisher 2000. Thanks, Kelsey Is your cursor inside the textbox when you paste? If you just paste Publisher will create a new text box. If this isn't the answer create a new textbox, paste and delete the original text box. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com &q...

New History Item, Mail Message sends all messages as Rich Text Format
We are using Business Contact Manager 2007 with Outlook 2007. We are not using Exchange server as a backend, just plain old pop3/smtp mail servers. Problem we are having is when we open up an account, Click on New History Item, Mail Message, and if we send an attachment with that, the person that receives the message gets it in Rich Text format. If we right click on the Email address itself and click on Send Mail. The message will be sent out fine. Right click on the message and looking at the properties of the email address has the internet format listed as "Let Outlook decide the ...

Text box set to memo vs. Lines Coded on a report
I have a report which looks like a legal pad with lines. 24 actual spaces. The number of lines can not change. I have duplicated a form from Excel to eliminate using it altogether. So it looks just like the Excel Report. I coded the lines into the report. I am using a text box with data type set to memo. I have several issues. 1. If the data entered into the textbox from my form fits in to the text box on one line, then there is no problem. I can just move the page footer to more or less the right position to allow it to fit inside the coded lines. (It is not exact, but close...

Question: Macro overloading, passing variable number of arguments
Excel-97 (SR-2) In general I would like to write macros that can be overloaded, for example Hyperlink() has an optional displaytext field. HyperLink( Filename) HyperLink( Filename, DisplayText) Please could someone post a short, useful macro that does such a thing, rather than give a description of how to do so. I have searched the online help for "overloading" and "arguments" but nothing useful is shown there. TIA, Fred. See the help for Functions You are just talking about optional arguments. Function MyFunc(MyStr As String, Optional MyArg1 As _ Integer = 5, _ ...

row number in Excel
Hi, I am using ODBC to connect to an Excel database, does anyone know of a function that I can use to select from row 50 to 100 of a spreadsheet. So, is it possible to use an sql statement on Excel database to get a range of rows? Does Excel have a row number function? Thanks Feng ...

Numbers left-aligned in a cell
I can enter a number as text by=20 (1) formatting the cell as text=20 or=20 (2) leaving the format as general but keying in a single quote with the = number e.g. '76. Either way, the number will be left-aligned with a green triangle i.e. = if I invoke error checking. If I untick error checking and don't notice = the left alignment, the single quote should alert me that the number is = in text format, right?=20 Do you see another difference between (1) and (2) especially in terms of = application of the number (in text format)? =20 Thanks. Epinn You aren't going to see th...

Rounding up numbers in hh:mm:ss format
I have a columnn of numbers stored in the hh:mm:ss (custom) format. I need to round up all cells that have ss>0 an add 1 to mm; in other words all cells with seconds should be rounded up to the next minute. How can I do that? Cheers, Thorbjorn Hi try =ROUNDUP(A1*24*60,0)/(24*60) -- Regards Frank Kabel Frankfurt, Germany Thorbjorn Sundboe wrote: > I have a columnn of numbers stored in the hh:mm:ss (custom) format. I > need to round up all cells that have ss>0 an add 1 to mm; in other > words all cells with seconds should be rounded up to the next minute. > How can I do ...

Can't send if text entered in message body
I'm having a strange send error. If I just put the recipient address and a subject, the email will send. If I add ANY text to the message, when I click "Send & Receive" it starts to send, I get the progress bar, then it just sits there until it errors out. I'm totally stumped by this one...any help out there? Thanks, Rod ...

Text wrap spec
People, I thought I understood that the text wrap limit for any given cell was 1024 and indeed when I look at the specification for Excel, it states that only 1024 text characters will display in a cell. However, I have the following condition (Excel 2003 running under Windows XP Pro). I have a column that is 35 wide. I entered repeating text in four paragraphs. The total length of my string is 1093 characters (verified using the Len function). The last word wrap occurs at character 1032, definitely beyond the 1024 limit. The total number of characters that shows in the cell is 1074 an...

Import/Export Text Specification
Is it possible to copy a single "Import/Export Text Specification" from one mdb to another? From the file that does not have the specification, File | Get External Data .... | Import, then choose .mdb file type and choose the file that has the specification in it, then click Options button in the wizard window and select the checkbox for importing import/export specifications. -- Ken Snell <MS ACCESS MVP> -- Ken Snell <MS ACCESS MVP> "JimP" <jpockmire@houston.rr.com> wrote in message news:4656fdde$0$9933$4c368faf@roadrunner.co...

Enter various number of blank lines in cells
I have a text spreadsheet index with 5 columns of information that has a various number of wrapped phrases in each cell. I would like to add various numbers of blank lines in some of the cells to make the information in the columns line up horizontally. Could you suggest a way to do this? For a forced line feed: <Alt> <Enter> -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Jim"...

exmerge: number of messages processed: 0
I am running exchange 2003 enterprise. I've had an instance where the log file was deleted and then firstly I could not mount the store. Did eseutil /mh, then performed eseutil /d. Got clean sheet, but could not do defragmentation. In the end had the ost files for 85% of the users and recovered them, got the pst and it was OK. As for the rest, tried using exmerge to get mailboxes into pst files. I have no issue with permissions, but when I use it, I only get folders copies and no items inside of them? (get the above number of messages processed to be 0 for the mailboxes). Basically, ...

How to set Tab Stop order programmatically?
Hi there, In a dialog, there are three controls: A, B, and C, the tab stop order is A -> B -> C, it is set in the design time. My question is how to change this order when the program is running, like change to A -> C -> B. Thanks in advance Vincent "Vincent" <msdn@cwbsupport.com> skrev i en meddelelse news:%23Utp8rC5DHA.2760@TK2MSFTNGP09.phx.gbl... > Hi there, > > In a dialog, there are three controls: A, B, and C, the tab stop order is > A -> B -> C, it is set in the design time. My question is how to change this > order when the program...