inserting symbols in a field

  • Thread starter Thread starter Anouska
  • Start date Start date
A

Anouska

Hi

I have a field with a series of numbers in it which looks
like this:

1245001234
1245001243
1254001246
1253006878 etc

I have a query to pick out the record which are 1254******

What I need to do is insert these records into a table,
but I want the number to have a semi colon at the front
and a question mark at the end so they will all look like
this in the new table:

;1254001234?
;1254001243?
;1254001246?

Any help would be greatly appreciated

Cheers
Anouska
 
Dear Anouska:

If you wish to view all the rows with a semi-colon and question mark
as you say, why bother storing them that way? You can simply leave
them stored as they are and add the semi-colon and question mark when
you display them. It's hard to imagine what you would gain by
changing the storage of the data in a constant fashion.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Tom,
We are a services club with about 12000 members.
What we have is a database of member names and their card
numbers which is part of our till system. We want to use
the member name and card numbers from this till system in
another database we are creating in order to print
tickets for members off a PC. The member will be able to
swipe their card at the PC and this will print them out a
ticket with their name and membership card number on it
for a promotion. The problem is, when the cards are
encoded they have a semi colon before the number and a
question mark after. And this does not appear in the
numbers in the till system database. So the numbers we
copy into our database need to appear like that or when
we swipe the card it will not match the fields in the
database we create.

Does that make sense?
 
Dear Anouska:

If these extra characters are always in the data when the card is
swiped, is there no opportunity to check them to make sure they're
there, then discard them?

This is not so say it cannot be done the way you suggest. Assuming
all the digits you need between the semi-colon and the question mark
are there already, you could just update the column (making sure it is
wide enough to accept the additional characters:

UPDATE YourTable SET MemberNumber = ";" & MemberNumber & "?"

Change YourTable to the actual name of the table and change
MemberNumber to the actual name of your column.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Tom

I have tried to use the code you gave me in an update
query, but when I run the query it does not update any
rows. Can you check the code you gave me, and if this
should work can you explain its use a bit more?

Thanks
 
Dear Anouska:

The query I provided says to unconditionally change the value in
MemberNumber to the value that was there previously, but with a
semicolon in front and a question mark afterward.

In order for this to work, the field must be a text data type, and
must be long enough to hold the new values.

Try editing one member number of one of the rows in the table manually
to make sure your column is designed to be able to hold the data you
want. Also, look at the longest member number you have. You'll need
room for that many characters, plus the two you want to add.

When you run the query you say it does not update. Does it give any
message indicating why not?

Let me know what happens when you try these things.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Tom,

The field is set to text and is 50 long so it should be
more than long enough. All of the numbers are exactly 10
digits long, which only makes 12 when updated. The query
tries to run and tells me it is going to update 12686
records which is correct, but then when you say OK to do
it, I get the following error:

"Microsoft Access can't update all the records in the
update query.
Microsoft Access didnt update 0 field(s) due to a type
conversion failure, 12686 record(s) due to key
violations, 0 record(s) due to lock violations, and 0
record(s) due to validation rule violations."

I worked out that the key violation was because this
field was the primary key for the table, so I removed the
primary key (we dont need it, is a single table in the
database) and the query went ahead, but when I checked
the data it had changed all of the values to a "0" zero,
instead of what we want.

I apreciate your help
Anouska
 
Dear Anouska:

It would seem you have a relationship defined between this table and
one or more other tables, and one or more of these relationships
include the MemberNumber column. Unless these relationships are set
to Cascade Update, you won't be able to change any of the Member
numbers which have "dependent rows" in these other tables.

There is a query alternative, but it's not simple. The simple
alternative is to set up Casdade Updates, which is done in the same
dialog where you created the relationships.

You see, when there are dependent rows in other tables, you must
update the values in the common columns in those other tables.
Otherwise, the references represented by those common columns would be
broken, and the database's ability to function would rapidly
deteriorate. This is called Referential Integrity, and it's a good
thing that the database is protecting itself against this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Dear Tom:

Currently there are no other tables in the database and
therefore no relationships. Would it be possible to email
you the database to show you what it is (its very small).
We havent done any of the rest of it because we need to
make this work first. Or do you have any other ideas as
to why all the numbers change to a "0"?

Thanks
Anouska
 
Dear Anouska:

Sounds OK. Email attachments must be less than 1 MB in size. Zip it
if necessary. I'm interested to find out what's going wrong here.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top