Search text & trigger command

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

Guest

Hi,
I want to create an "immediate if" function to search some text, and if
there is a n asterisk present, trim the text to put the text to the left of
the asterisk on one line, delete the asterisk, and put the trainling text on
the next line.

I'm doing this in an expression builder for a report. If there is no
asterisk, then the address will print on one line.

I'm thinking (but I know it's waay off):
IIF([Address].text has "*", Ltrim[Address] & Chr$(13) & Chr$(10)
&"Rtrim[Address]", [Address])

Any ideas?
Thank you,
Karl H
 
hi Karl,

Karl said:
I'm thinking (but I know it's waay off):
IIF([Address].text has "*", Ltrim[Address] & Chr$(13) & Chr$(10)
&"Rtrim[Address]", [Address])
Click to expand...
Use the Replace() function, if you have Acc2k or above:

Replace([Address], "*", vbCrLF)


mfG
--> stefan <--
 
I find it easier to do this kind of thing in stages. I might start with
something like this (I'm using a query for testing purposes) ...

SELECT IIf(InStr(1,[Address],"*")<>0, "X",[Address]) AS Expr1
FROM Table1;

Once we have this first stage working, we can start replacing the 'X'
placeholder with the required sub-expression. This gets us the part of the
text before the '*' ...

SELECT IIf(InStr(1,[Address],"*")<>0, Left$([Address], InStr(1, [Address],
"*")-1),[Address]) AS Expr1
FROM Table1;

Now we can add the carriage return/line feed and the part of the text after
the '*' ...

SELECT
IIf(InStr(1,[Address],"*")<>0,Left$([Address],InStr(1,[Address],"*")-1) &
Chr$(13) & Chr$(10) & Mid$([Address],InStr(1,[Address],"*")+1),[Address]) AS
Expr1
FROM Table1;

To convert that from a query to something you can use as the control source
of a text box, change it to ...

= IIf(InStr(1,[Address],"*")<>0,Left$([Address],InStr(1,[Address],"*")-1) &
Chr$(13) & Chr$(10) & Mid$([Address],InStr(1,[Address],"*")+1),[Address])
 
Wow! That gives me goose bumps. It works perfectly.
Thank you so much, Brendan. You're a talented teacher.
Karl

Brendan Reynolds said:
I find it easier to do this kind of thing in stages. I might start with
something like this (I'm using a query for testing purposes) ...

SELECT IIf(InStr(1,[Address],"*")<>0, "X",[Address]) AS Expr1
FROM Table1;

Once we have this first stage working, we can start replacing the 'X'
placeholder with the required sub-expression. This gets us the part of the
text before the '*' ...

SELECT IIf(InStr(1,[Address],"*")<>0, Left$([Address], InStr(1, [Address],
"*")-1),[Address]) AS Expr1
FROM Table1;

Now we can add the carriage return/line feed and the part of the text after
the '*' ...

SELECT
IIf(InStr(1,[Address],"*")<>0,Left$([Address],InStr(1,[Address],"*")-1) &
Chr$(13) & Chr$(10) & Mid$([Address],InStr(1,[Address],"*")+1),[Address]) AS
Expr1
FROM Table1;

To convert that from a query to something you can use as the control source
of a text box, change it to ...

= IIf(InStr(1,[Address],"*")<>0,Left$([Address],InStr(1,[Address],"*")-1) &
Chr$(13) & Chr$(10) & Mid$([Address],InStr(1,[Address],"*")+1),[Address])

--
Brendan Reynolds
Access MVP

Karl H said:
Hi,
I want to create an "immediate if" function to search some text, and if
there is a n asterisk present, trim the text to put the text to the left
of
the asterisk on one line, delete the asterisk, and put the trainling text
on
the next line.

I'm doing this in an expression builder for a report. If there is no
asterisk, then the address will print on one line.

I'm thinking (but I know it's waay off):
IIF([Address].text has "*", Ltrim[Address] & Chr$(13) & Chr$(10)
&"Rtrim[Address]", [Address])

Any ideas?
Thank you,
Karl H
Click to expand...
Click to expand...
 
hi Karl,

Karl said:
Wow! That gives me goose bumps. It works perfectly.
Click to expand...
Acc2k+:

SELECT Field1, Replace([Field1], "*", Chr(13) & Chr(10))
FROM Table;


mfG
--> stefan <--
 
Thanks Karl, but to tell the truth, I'm actually kicking myself that I
didn't think of using Stefan's solution using the Replace function. It's a
lot simpler, and should work just as well as long as you are using a recent
version of Access.

--
Brendan Reynolds
Access MVP


Karl H said:
Wow! That gives me goose bumps. It works perfectly.
Thank you so much, Brendan. You're a talented teacher.
Karl

Brendan Reynolds said:
I find it easier to do this kind of thing in stages. I might start with
something like this (I'm using a query for testing purposes) ...

SELECT IIf(InStr(1,[Address],"*")<>0, "X",[Address]) AS Expr1
FROM Table1;

Once we have this first stage working, we can start replacing the 'X'
placeholder with the required sub-expression. This gets us the part of
the
text before the '*' ...

SELECT IIf(InStr(1,[Address],"*")<>0, Left$([Address], InStr(1,
[Address],
"*")-1),[Address]) AS Expr1
FROM Table1;

Now we can add the carriage return/line feed and the part of the text
after
the '*' ...

SELECT
IIf(InStr(1,[Address],"*")<>0,Left$([Address],InStr(1,[Address],"*")-1) &
Chr$(13) & Chr$(10) & Mid$([Address],InStr(1,[Address],"*")+1),[Address])
AS
Expr1
FROM Table1;

To convert that from a query to something you can use as the control
source
of a text box, change it to ...

= IIf(InStr(1,[Address],"*")<>0,Left$([Address],InStr(1,[Address],"*")-1)
&
Chr$(13) & Chr$(10) & Mid$([Address],InStr(1,[Address],"*")+1),[Address])

--
Brendan Reynolds
Access MVP

Karl H said:
Hi,
I want to create an "immediate if" function to search some text, and if
there is a n asterisk present, trim the text to put the text to the
left
of
the asterisk on one line, delete the asterisk, and put the trainling
text
on
the next line.

I'm doing this in an expression builder for a report. If there is no
asterisk, then the address will print on one line.

I'm thinking (but I know it's waay off):
IIF([Address].text has "*", Ltrim[Address] & Chr$(13) & Chr$(10)
&"Rtrim[Address]", [Address])

Any ideas?
Thank you,
Karl H
Click to expand...
Click to expand...
Click to expand...
 
Hi Stefan,
Thank you, too. At first I didn't see anything but the vbCrLf, which I'd
tried before and it didn't work. I'll give this shorter programmihng a try as
well.
I have "Access 2003 VBA" and "Access 2002 Desktop" and neither give much
description of this function, though VB help within Access 2003 does a fair
job. Can you suggest a more complete reference for me.
Thank you, again!
Karl

Stefan Hoffmann said:
hi Karl,

Karl said:
I'm thinking (but I know it's waay off):
IIF([Address].text has "*", Ltrim[Address] & Chr$(13) & Chr$(10)
&"Rtrim[Address]", [Address])
Click to expand...
Use the Replace() function, if you have Acc2k or above:

Replace([Address], "*", vbCrLF)


mfG
--> stefan <--
Click to expand...
 
hi Karl,

Karl said:
Thank you, too. At first I didn't see anything but the vbCrLf, which I'd
tried before and it didn't work.
Click to expand...
The vbCrLf = Chr(13) & Chr(10) is just a predefined constant for use in
VBA.
When using Replace() in a query, you have to use Chr(13) & Chr(10).


mfG
--> stefan <--
 
Back
Top