Re: Rearrange data in a column, Access 2003
- From: SherryScrapDog <SherryScrapDog@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 14 Mar 2008 06:01:02 -0700
Hi John,
Thanks for the response! I tried this and am getting a 'syntax error,
missing operand'. Here is my SQL:
UPDATE Soldiers1 SET [Page] = Right([Page], 1) & "- " &
Format(Val([Page],"00")
WHERE [Page] LIKE "#[a-zA-Z]" OR [Page] LIKE "##[a-zA-Z]";
I didn't explain this in very much detail before, but this is just a
temporary file that will be loaded into a database that has a text field for
page numbers because I am loading many individual files. These are index
files of names from books for our genealogy society and allows queries of
names for researchers to see which books a name appears in. This particular
book (from the Civil War period) is separated by aplphabetic tabs and most of
the names for a letter begin with that letter. However, some names do not.
So, in the tab for A, I have Abrams, Ackley, Murphy, etc. I'm assuming a
page about Ackley references Murphy. I checked to see if the pages exceed 99
within a letter, and they do not. Most of files I load have pages such as 3
or 6, 11, 40 or 16-18. But this one is different. I also have a form that
lists all of the records for a book, and that is why I would like these to
display in the proper order, like the book. I already take care of the other
regular page numbers.
I have learned some about SQL, but not enough to figure out the above error.
I keep learning and I have learned so much by looking at these questions.
Can you tell me what's wrong with what I have? Thanks so much, Sherry
"John W. Vinson" wrote:
On Thu, 13 Mar 2008 19:25:04 -0700, SherryScrapDog.
<SherryScrapDog@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I have a column that is for Page Numbers, and this particular table contains
a letter in addition to the page number. Here are examples:
1A
3A
10A
1B
13B
I would like to do 2 things: I would like to move the letter to the front
formatted as "A- " and I would like to make one-digit numbers 2-digits so
they will sort in the proper order. These letters are meaningful to the
users. I would like to end up with:
A- 01
A- 03
A-10
B- 01
B- 13
Can I do this? I've tried a few things that I found in other questions, but
have not been able to get it to work. I assume I would use the update
function in a query but I am not getting the syntax right. Thanks for any
help you can give. Sherry
Will you EVER have a three digit page number? Will you EVER have more than one
letter? (Ever can be a very long time... or it can be a coworker calling you
tomorrow morning).
If not I'd suggest the following update query. Back up your database FIRST,
this is high-hazard work:
UPDATE yourtable
SET [Page Number] = Right([Page Number], 1) & "- " & Format(Val([Page Number],
"00")
WHERE [Page Number] LIKE "#[a-zA-Z]" OR [Page Number] LIKE "##[a-zA-Z]"
You should also strongly consider splitting this apparently non-atomic field
into two, a text field and an integer. They can easily be concatenated for
display.
--
John W. Vinson [MVP]
- Follow-Ups:
- Re: Rearrange data in a column, Access 2003
- From: John Spencer
- Re: Rearrange data in a column, Access 2003
- References:
- Re: Rearrange data in a column, Access 2003
- From: John W . Vinson
- Re: Rearrange data in a column, Access 2003
- Prev by Date: Re: GroupByX Sum CounfOfX but show only the ones more than a given
- Next by Date: Re: Crosstab/Transform with an join
- Previous by thread: Re: Rearrange data in a column, Access 2003
- Next by thread: Re: Rearrange data in a column, Access 2003
- Index(es):
Relevant Pages
|
