If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Make report alpha then numeric
I hate to be really dumb, but I have tried these suggestions in numerous
places and numerous ways. When I sent in for "New Users", I mean literally "New User". This is an inventory of subjects that were discussed in Council meetings since the 60's. I had Access 2000 and had the database working fine until book Z was finished and Book 1 came in. I will not be here long enough to get to Book 10! I now have Access 2007 and I cannot get the entries of book 1 to come after book Z (chronological order). I need detailed help if you have time. Where do I put the MySort.... at? "Clifford Bass" wrote: Hi Becky, Correction: IsNumber should be IsNumeric. Of course, these will fail to sort correctly once you get up to 10 for a book number because your second sort is a string, not a number. So you may want to add a second sort: MySort_2: IIf(IsNumeric([Field_X]), CLng([Field_X]), 0) So for these values you get these sort values A A 0 B B 0 1 ZZZZZZZ 1 2 ZZZZZZZ 2 10 ZZZZZZZ 10 100 ZZZZZZZ 100 1000 ZZZZZZZ 1000 Where the second sort value is numeric instead of a string. In this case you would not need to sort by [Field_X]. Hope that helps, Clifford Bass "Clifford Bass" wrote: Hi Karl and Becky, I think that should be: MySort_1: IIf(IsNumber(Left([Field_X], 1)), "ZZZZZZZ", [Field_X]) Or, if there is never going to be a book with both a number and a letter, maybe just: MySort_1: IIf(IsNumber([Field_X]), "ZZZZZZZ", [Field_X]) Alternatively, it may make sense to add in a "Sort_Order" field and populate that as desired. Clifford Bass |
#12
|
|||
|
|||
Make report alpha then numeric
Add a new calculated field to your query. Assuming your field is named
BookNumber you would enter the following in the field and sort "cells". Obviously replacing BookNumber with the name of the field that contains the book indentification. Field: MySort: IsNumeric([BookNumber],"ZZZ" & Format([BookNumber],"000"),[BookNumber]) Sort: Ascending That will keep everying in order until you reach book 1000. In other words, we will probably be immune from recrimination at the point book 1000 is reached. If you aren't using a query, then you need to so you can sort correctly, publish reports in the correct order, etc. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County beckyboop wrote: I hate to be really dumb, but I have tried these suggestions in numerous places and numerous ways. When I sent in for "New Users", I mean literally "New User". This is an inventory of subjects that were discussed in Council meetings since the 60's. I had Access 2000 and had the database working fine until book Z was finished and Book 1 came in. I will not be here long enough to get to Book 10! I now have Access 2007 and I cannot get the entries of book 1 to come after book Z (chronological order). I need detailed help if you have time. Where do I put the MySort.... at? |
#13
|
|||
|
|||
Make report alpha then numeric
Hi Becky,
And a final alternative to the other suggestions, inspired by your phrase "chronolgical order": Add one or two fields to your table: Start_Date and maybe End_Date. Fill in the dates for the existing books. You can now sort on the Start_Date. The existing and any future names/numbers the books will not matter one iota. Clifford Bass "beckyboop" wrote: I hate to be really dumb, but I have tried these suggestions in numerous places and numerous ways. When I sent in for "New Users", I mean literally "New User". This is an inventory of subjects that were discussed in Council meetings since the 60's. I had Access 2000 and had the database working fine until book Z was finished and Book 1 came in. I will not be here long enough to get to Book 10! I now have Access 2007 and I cannot get the entries of book 1 to come after book Z (chronological order). I need detailed help if you have time. Where do I put the MySort.... at? |
#14
|
|||
|
|||
Make report alpha then numeric
I have tried this and I get "The expression you entered has a function
containing the wrong number of arguments". I typed it in exactly like you have listed (field is named Book) assuming that the & is a continuance of the expression. I do not see a "Format Field" in the query design view. "John Spencer" wrote: Add a new calculated field to your query. Assuming your field is named BookNumber you would enter the following in the field and sort "cells". Obviously replacing BookNumber with the name of the field that contains the book indentification. Field: MySort: IsNumeric([BookNumber],"ZZZ" & Format([BookNumber],"000"),[BookNumber]) Sort: Ascending That will keep everying in order until you reach book 1000. In other words, we will probably be immune from recrimination at the point book 1000 is reached. If you aren't using a query, then you need to so you can sort correctly, publish reports in the correct order, etc. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County beckyboop wrote: I hate to be really dumb, but I have tried these suggestions in numerous places and numerous ways. When I sent in for "New Users", I mean literally "New User". This is an inventory of subjects that were discussed in Council meetings since the 60's. I had Access 2000 and had the database working fine until book Z was finished and Book 1 came in. I will not be here long enough to get to Book 10! I now have Access 2007 and I cannot get the entries of book 1 to come after book Z (chronological order). I need detailed help if you have time. Where do I put the MySort.... at? |
#15
|
|||
|
|||
Make report alpha then numeric
On Wed, 29 Jul 2009 11:38:01 -0700, beckyboop
wrote: I have tried this and I get "The expression you entered has a function containing the wrong number of arguments". I typed it in exactly like you have listed (field is named Book) assuming that the & is a continuance of the expression. I do not see a "Format Field" in the query design view. "John Spencer" wrote: Add a new calculated field to your query. Assuming your field is named BookNumber you would enter the following in the field and sort "cells". Obviously replacing BookNumber with the name of the field that contains the book indentification. Field: MySort: IsNumeric([BookNumber],"ZZZ" & Format([BookNumber],"000"),[BookNumber]) Sort: Ascending I think you got caught by wordwrap on the newsgroup. The lines Field: MySort: IsNumeric([BookNumber],"ZZZ" & Format([BookNumber],"000"),[BookNumber]) should all be on one line (one Field cell) in the query grid. -- John W. Vinson [MVP] |
#16
|
|||
|
|||
Make report alpha then numeric
I typed: MySort: IsNumeric([Book],"ZZZ" & Format([Book],"000"),[Book])
I get the error: the expression you entered has a function containing the wrong number of arguments. I do appreciate your help. "John W. Vinson" wrote: On Wed, 29 Jul 2009 11:38:01 -0700, beckyboop wrote: I have tried this and I get "The expression you entered has a function containing the wrong number of arguments". I typed it in exactly like you have listed (field is named Book) assuming that the & is a continuance of the expression. I do not see a "Format Field" in the query design view. "John Spencer" wrote: Add a new calculated field to your query. Assuming your field is named BookNumber you would enter the following in the field and sort "cells". Obviously replacing BookNumber with the name of the field that contains the book indentification. Field: MySort: IsNumeric([BookNumber],"ZZZ" & Format([BookNumber],"000"),[BookNumber]) Sort: Ascending I think you got caught by wordwrap on the newsgroup. The lines Field: MySort: IsNumeric([BookNumber],"ZZZ" & Format([BookNumber],"000"),[BookNumber]) should all be on one line (one Field cell) in the query grid. -- John W. Vinson [MVP] |
#17
|
|||
|
|||
Make report alpha then numeric
Hi Becky,
He forgot the IIf() part. Try (all one line): MySort: IIf(IsNumeric([Book]),"ZZZ" & Format([Book],"000"),[Book]) Clifford Bass "beckyboop" wrote: I typed: MySort: IsNumeric([Book],"ZZZ" & Format([Book],"000"),[Book]) I get the error: the expression you entered has a function containing the wrong number of arguments. I do appreciate your help. |
#18
|
|||
|
|||
Make report alpha then numeric
I typed MySort: IIfIsNumeric([Book],"ZZZ" & Format([Book],"000"),[Book])
I get the error: undefined function 'Format' in expression "beckyboop" wrote: I typed: MySort: IsNumeric([Book],"ZZZ" & Format([Book],"000"),[Book]) I get the error: the expression you entered has a function containing the wrong number of arguments. I do appreciate your help. "John W. Vinson" wrote: On Wed, 29 Jul 2009 11:38:01 -0700, beckyboop wrote: I have tried this and I get "The expression you entered has a function containing the wrong number of arguments". I typed it in exactly like you have listed (field is named Book) assuming that the & is a continuance of the expression. I do not see a "Format Field" in the query design view. "John Spencer" wrote: Add a new calculated field to your query. Assuming your field is named BookNumber you would enter the following in the field and sort "cells". Obviously replacing BookNumber with the name of the field that contains the book indentification. Field: MySort: IsNumeric([BookNumber],"ZZZ" & Format([BookNumber],"000"),[BookNumber]) Sort: Ascending I think you got caught by wordwrap on the newsgroup. The lines Field: MySort: IsNumeric([BookNumber],"ZZZ" & Format([BookNumber],"000"),[BookNumber]) should all be on one line (one Field cell) in the query grid. -- John W. Vinson [MVP] |
#19
|
|||
|
|||
Make report alpha then numeric
Computers sure are finicky!
There should be a parenthesis Between IIF and IsNumeric There should be a closing parenthesis after the first [Book] MySort: IIf(IsNumeric([Book]),"ZZZ" & Format([Book],"000"),[Book]) If you still get an error then post back with the error (as you have been patiently doing) and we will try again. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County beckyboop wrote: I typed MySort: IIfIsNumeric([Book],"ZZZ" & Format([Book],"000"),[Book]) I get the error: undefined function 'Format' in expression "beckyboop" wrote: I typed: MySort: IsNumeric([Book],"ZZZ" & Format([Book],"000"),[Book]) I get the error: the expression you entered has a function containing the wrong number of arguments. I do appreciate your help. |
#20
|
|||
|
|||
Make report alpha then numeric
My bad John.
I did type it that way in the query. When I try the data sheet view, I get the error: undefined function 'Format' in expression. "John Spencer" wrote: Computers sure are finicky! There should be a parenthesis Between IIF and IsNumeric There should be a closing parenthesis after the first [Book] MySort: IIf(IsNumeric([Book]),"ZZZ" & Format([Book],"000"),[Book]) If you still get an error then post back with the error (as you have been patiently doing) and we will try again. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County beckyboop wrote: I typed MySort: IIfIsNumeric([Book],"ZZZ" & Format([Book],"000"),[Book]) I get the error: undefined function 'Format' in expression "beckyboop" wrote: I typed: MySort: IsNumeric([Book],"ZZZ" & Format([Book],"000"),[Book]) I get the error: the expression you entered has a function containing the wrong number of arguments. I do appreciate your help. |
Thread Tools | |
Display Modes | |
|
|