Update query : Access 97

  • Thread starter Thread starter Céline Brien
  • Start date Start date
C

Céline Brien

Hi !
I would like, in on query, to update
1-1 for 2-1
1-2 for 2-2
1-3 for 2-3
1-4 for 2-4
1-5 for 2-5
A month later, with the same Query, I would like to update
2-1 for 3-1
2-2 for 3-2
2-3 for 3-3
2-4 for 3-4
2-5 for 3-5
and so on until
11-1 for 12-1
11-2 for 12-2
11-3 for 12-3
11-5 for 12-4
11-5 for 12-5
Many thanks for your help,
Céline
 
Céline,

Based on your examples, I think this will work... run an Update Query to
update [YourField] to...
Val([YourField])+1 & Right([YourField],2)
 
Hey Steve,

Looks like a problem when the old value is a 2 digit number. Have to
use Instr to find the hyphen, don't you think?

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

I took Céline at her word... that's why I said "based on your examples",
and her examples show only digits 1 to 5 to the right of the hyphen.
 
Dear Steve:

I'm looking at the example given, where there are 1 or 2 digits left
of the hyphen.

I think your code is going to go from 10 to 110, rather than 11. Or,
in the example, going from 11-1 to 121-1. It is the code:

& Right([YourField],2)

That sometines needs to be, in effect:

& Right([YourField],3)

based on where the hyphen is. When there are two digits left of the
hyphen, you don't want to append the second of these two digits,
right?

Or am I missing something?

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

Either you are missing something, or I am :-)

Using my suggested expression:
Val([YourField])+1 & Right([YourField],2)

If [YourField] = "1-4"
Expression = "2-4"

If [YourField] = "2-4"
Expression = "3-4"

If [YourField] = "11-4"
Expression = "12-4"

Yes? No? Maybe Baby Blue?

As far as I know, Right([YourField],2) always returns the rightmost 2
characters, regrdless of the length of YourField :-)
 
Dear Steve:

Now I see it. My mind was thinking Mid([YourField], 2). You were
completely correct all along, asuming the hyphen is the next to last
character.

Well, at least we kept it among friends.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi !
Thank you so much for your enthusiastic help !
I am going to try your solution and come back to you.
Céline

Steve Schapel said:
Tom,

Tom said:
Now I see it. My mind was thinking Mid([YourField], 2).
Click to expand...

Too many pecan and cinnamon buns can do this to your brain <g>
Click to expand...
 
Back
Top