changing column names continued

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Following my earlier question (below)

Is it possible to run a query with a calculation that can change the following

Sample Location
BH 3 = Borehole 03
BH 4 = Borehole 04
BH 5 = Borehole 05
.....
BH 11 = Borehole 11

Next question - I've just realised that some of the queries have the
following names

BH 15 = Borehole 15 works with below code
BH 16 = Borehole 16 works with below code
BH 54/1 = Borehole 54/1 error
BH 54/2 = Borehole 52/2 error

NewCode: "Borehole " & Format( CInt(Mid([Sample Location], 4) ), "00" )

Can I get round this?
 
KIM said:
Following my earlier question (below)

Is it possible to run a query with a calculation that can change the following

Sample Location
BH 3 = Borehole 03
BH 4 = Borehole 04
BH 5 = Borehole 05
....
BH 11 = Borehole 11

Next question - I've just realised that some of the queries have the
following names

BH 15 = Borehole 15 works with below code
BH 16 = Borehole 16 works with below code
BH 54/1 = Borehole 54/1 error
BH 54/2 = Borehole 52/2 error

NewCode: "Borehole " & Format( CInt(Mid([Sample Location], 4) ), "00" )

Can I get round this?
Click to expand...

Kim,

Try this - it should be one line:

NewName: "Borehole " & IIf(Len(Trim(Mid([Sample Location],InStr([Sample
Location]," ")+1)))<2,Format(Mid([Sample Location],InStr([Sample Location],"
")+1),"00"),Mid([Sample Location],InStr([Sample Location]," ")+1))


If there are more variations (ie like 54/1), I think it would be easier to
write a UDF to do this conversion.

HTH
 
Not knowing the name of the field that contains the data BH 3 etc. I will use
the example that is is named BH and the size of the field is 12 and I will
call the new name BH1.
BH1:="Borehole" & right(trim([BH]),9)
This is assuming there is a space between BH and the number.
 
SteveS - You complete STAR - that worked! I really must learn VB - I'm sure
it would help answer some of my questions and understand the answers I have
recieved via the discussion group!

Thank you soooo much!


SteveS said:
KIM said:
Following my earlier question (below)

Is it possible to run a query with a calculation that can change the following

Sample Location
BH 3 = Borehole 03
BH 4 = Borehole 04
BH 5 = Borehole 05
....
BH 11 = Borehole 11

Next question - I've just realised that some of the queries have the
following names

BH 15 = Borehole 15 works with below code
BH 16 = Borehole 16 works with below code
BH 54/1 = Borehole 54/1 error
BH 54/2 = Borehole 52/2 error

NewCode: "Borehole " & Format( CInt(Mid([Sample Location], 4) ), "00" )

Can I get round this?
Click to expand...

Kim,

Try this - it should be one line:

NewName: "Borehole " & IIf(Len(Trim(Mid([Sample Location],InStr([Sample
Location]," ")+1)))<2,Format(Mid([Sample Location],InStr([Sample Location],"
")+1),"00"),Mid([Sample Location],InStr([Sample Location]," ")+1))


If there are more variations (ie like 54/1), I think it would be easier to
write a UDF to do this conversion.

HTH
Click to expand...
 
KIM said:
SteveS - You complete STAR - that worked! I really must learn VB - I'm sure
it would help answer some of my questions and understand the answers I have
recieved via the discussion group!

Thank you soooo much!
Click to expand...

You're welcome.

Yes, by all means, learn VBA. At some point you will want to do something
that can only be done in code (like using recordsets).

Start by reading the code examples posted here. Look at the code created by
the wizards. And of course...try - then ask questions.

Pretty soon you will be able to read and write code like a third grade primer!
 
Back
Top