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 said:
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.
