Move text between fields

  • Thread starter Thread starter Nigel M
  • Start date Start date
N

Nigel M

Part repost:

I had a success with the function I wanted, thanks for the help. I used
an update query as suggested to reset all the Yes/No values in a table
to No, and (if possible) add the current date for each change from Yes
to No into another field.


I want to incorporate a function onto a form to remove the first 11
characters from a given field in the current record, and put the first
10 of these into another field (which happens to be the PK).

In pseudo BASIC code, I need:

id_field = left(10, title_field)
title_field = mid(12, title_field)

Any suggestions? I don't think an update query is suitable, it is needed
in a live record, with no PK at the time.
 
With a little syntax modification, what you have should work in the Click
event of a button.
id_field = left(10, title_field)
title_field = mid(12, title_field)
Click to expand...

Me.txtID_Field = Left(Me.txtTitle_Field, 10)
Me.txtTitle_Field = Mid(Me.txtTitle_Field, 12)

where txtID_Field is the name of the textbox for the id_field and
txtTitle_Field is the name of the textbox for the title_field.
 
Me.txtID_Field = Left(Me.txtTitle_Field, 10)
Me.txtTitle_Field = Mid(Me.txtTitle_Field, 12)
Click to expand...

Fantastic, worked first time! I've never used events before, and I'm so
pleased that my pseudo code was sensible - I think I'll get into this.

Thanks again.
 
Me.txtID_Field = Left(Me.txtTitle_Field, 10)
Me.txtTitle_Field = Mid(Me.txtTitle_Field, 12)
Click to expand...

Something else that would be handy is to extract a postcode (like a US
zip code) from the end of the address. Trouble is, in the UK, there are
several post code formats: LN NLL, LNN NLL, LLNN NLL, LLNL NLL and in
some cases the space may have been omitted.

Essentially then, the postcode could be from 5 to 8 letters, but would
always be preceded by a CR or LF (I'm not sure, may be both).

I'm guessing that there is a Right() method, that you could use with a
number of IF statements (or is there a CASE) but I don't know if this is
better than iteration. Or is there a method that will find the position
of the last occurrence of a character (CR or LF)
 
You could use InStrRev to find the last occurrence of a character. If it's
not far enough in, then you would know that there was a space in the postal
code and look for the second space from the right. Is the postal code on a
separate line by itself? If so, you could use InStrRev to look for Chr(13).
 
you could use InStrRev to look for Chr(13).
Click to expand...

OK, I've got this far (be kind I'm new to VB):

position = InStrRev(Me.Address, Chr(13))
Me.Postcode = Right(Me.Address, (Len(Me.Address) - position) - 1)
Me.Address = Left(Me.Address, (position - 2))

This seems to work, it grabs the last line and puts it in the Postcode.

Only trouble is, when I print, the Postcode isn't at the bottom of the
Address, it prints over the top (or some distance below if I move it
down the form in the Design mode).

My reason for splitting the postcode into a separate filed was to print
it in bold, is there an easier way?
 
The form ought to print as you see it on the screen. Do other forms print as
viewed? Have you set your printer driver's properties to "draft mode", "ink
saving mode", or something similar?
 
If it's in a different textbox, it won't fit as tightly as it will when it's
in the same textbox. About all you can do is place the controls where you
want them and make sure they aren't larger than is needed to display the
text. In this case, the height of the textbox is the dimension that needs
adjusted, if able.
 
Back
Top