Looking for * in strings.

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

Guest

Is it possible to check for the character * in strings? Im currently using
this but it doesnt seem to be working. I tried 2 approaches so far but both
doesnt seem to be working. Whats the correct way of coding it?

If (InStr(1, Me.NominalValue, Asc("*"), vbBinaryCompare) > 0) Then ...

If (InStr(1, Me.NominalValue, "*", vbBinaryCompare) > 0) Then ...

Thanks in advance.
 
Place the asterisk in square brackets, e.g.
If "Test * this" Like "*[*]*" Then ...

Your 2nd example should also work.
 
hi Allen,

Allen said:
Place the asterisk in square brackets, e.g.
If "Test * this" Like "*[*]*" Then ...

Your 2nd example should also work.
Click to expand...
?InStr("Test*Test", "*") in the direct window crashes Access (A2k3). Is
this a known bug?



mfG
--> stefan <--
 
I pasted
?InStr("Test*Test", "*")
directly into the Immediate Window in A2003.
It returned 5 without a crash, so something else must be going on.

Tested in Access 2003 SP2 with JET 4 SP8, and all the standard libries
loaded, plus ADOX and Office 11.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
stefan hoffmann said:
hi Allen,

Allen said:
Place the asterisk in square brackets, e.g.
If "Test * this" Like "*[*]*" Then ...

Your 2nd example should also work.
Click to expand...
?InStr("Test*Test", "*") in the direct window crashes Access (A2k3). Is
this a known bug?



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

Allen said:
I pasted
?InStr("Test*Test", "*")
directly into the Immediate Window in A2003.
It returned 5 without a crash, so something else must be going on.

Tested in Access 2003 SP2 with JET 4 SP8, and all the standard libries
loaded, plus ADOX and Office 11.
Click to expand...
Using the same setup and a Access 2000, i tracked it down:

- Open Access
- Use Ctrl+G to go to the immediate window
- ?InStr("Test*Test", "Test") crash

- Open Access
- Use Ctrl+G to go to the immediate window
- ?InStr("Test*Test", "Test", vbTextCompare)
- throws a runtime error 13
- ?InStr("Test*Test", "Test") crash

- Open Access
- Use Ctrl+G to go to the immediate window
- ?InStr(1, "Test*Test", "Test", vbTextCompare) works
- ?InStr("Test*Test", "Test") works

- Open Access
- Open a database
- Use Ctrl+G to go to the immediate window
- ?InStr("Test*Test", "Test") works

It seems that the VBA library is not correct initialized at Access startup.


mfG
--> stefan <--
 
On my A2k3 the direct command
?InStr("Test*Test", "*")
Yields the answer
5
which seems quite close :)

+ Jan Pit +
Botswana

stefan hoffmann said:
hi Allen,

Allen said:
Place the asterisk in square brackets, e.g.
If "Test * this" Like "*[*]*" Then ...

Your 2nd example should also work.
Click to expand...
?InStr("Test*Test", "*") in the direct window crashes Access (A2k3). Is
this a known bug?



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

Jan said:
On my A2k3 the direct command
?InStr("Test*Test", "*")
Yields the answer
5
which seems quite close :)
Click to expand...
It seems that the VBA library is not correct initialized.

Can you try the following:

- Open Access (don't open a mdb)
- Use Ctrl+G to go to the immediate window
- ?InStr("Test*Test", "Test")

Without opening a .mdb this forces Access to crash, i can reproduce it
on A2k3 and on A2k.

mfG
--> stefan <--
 
Stefan Hoffmann said:
hi Jan,


It seems that the VBA library is not correct initialized.

Can you try the following:

- Open Access (don't open a mdb)
- Use Ctrl+G to go to the immediate window
- ?InStr("Test*Test", "Test")

Without opening a .mdb this forces Access to crash, i can reproduce it
on A2k3 and on A2k.
Click to expand...

Why would you worry about whether you can use an expression without having a
database open?

References exist in the database: each MDB file specifically points to the
VBA library.

In previous versions of Access, it wasn't possible to go to the Immediate
Window without having a database open. I'm actually surprised that new
versions of Access allow it.
 
hi Douglas,
Why would you worry about whether you can use an expression without having a
database open?
Click to expand...
I can't rely on a frame work, which is not able to load its main
libraries in a correct manner.
References exist in the database: each MDB file specifically points to the
VBA library.
Click to expand...
MDB files and references are not the problem here.

The VBA library is loaded before that, cause i can use
VBA.Strings.InStr$() in the immediate window without opening a MDB file.
The reproducible crashes indicates boundary conditions i'm not aware of,
they are not documented as far as i know.
This may be okay for an ordenary user, but i like to call me a software
developer. It is not okay for me, as it seems to be a real bug.

I need to do some more testing with the VBA SDK, may be it is documented
there.
In previous versions of Access, it wasn't possible to go to the Immediate
Window without having a database open. I'm actually surprised that new
versions of Access allow it.
Click to expand...
Notably 1.0 - 97?


mfG
--> stefan <--
 
This doesn't make any sense, Stefan.

You cannot use Access unless you have a database loaded: it's not intended
for that purpose. It would be the equivalent of trying to use Word without a
document open, or Excel without a workbook open.

Exactly what are you trying to do that you feel it's necessary to have VBA
functionality available to you without having an MDB open?
 
I can't rely on a frame work, which is not able to load its main libraries
in a correct manner.
Click to expand...

You can use instr unless you open a mdb file. This has NOTHING to do with
searching for a asterisk.

in the debug window, you can't even use

? instr("abc","b")

However, you *Can* use some functions, like

? date

This is simply due to the fact that SOME functions are native to ms-access,
and others are the result of a reference.

There are references to the VB libraries(s) of code that simply can't be
resolved unless you set them. To set those references, you MUST load a mdb
file, and then the references can be resolved. So, it is not a question of
loading the libraries, but a question of do you want ms-access on startup to
load 15 million libraries all at once....and take VERY long time to do so?
Further, the memory requirements of such a silly concept would be REALLY
horrible...

It makes ABSOLUTE no sense for ms-access to load every single library on the
planet, and THEN let you decide if you need these libraries. Surely, this is
not what you are suggesting?

IF you are suggesting that the error should not occur, hum...sure, that
might be of some value, but not much. Since references are attached to the
application part (mdb), then it would not make sense to load these
libraries....especially ALL OF THEM!!!

It is not a question in incorrectly loading libraries...it is concept that
it would be REALLY REALLY dumb to load everything on the planet....
 
hi Albert,
You can use instr unless you open a mdb file. This has NOTHING to do with
searching for a asterisk.
Click to expand...
So, would you appreciate a new thread?
in the debug window, you can't even use

? instr("abc","b")
Click to expand...
But i can use

? instr(1, "abc", "b", vbCompareText)

And after that the following works:

? instr("abc","b")

The problem here is:

InStr seems to be an overloaded function. These functions can be called
at the same time, but they have different boundary conditions, which are
_not_ documented.


mfG
--> stefan <--
 
hi Douglas,

Douglas J Steele works:
This doesn't make any sense, Stefan.
You cannot use Access unless you have a database loaded: it's not intended
for that purpose.
Click to expand...
This is correct, but not my problem here. May be i haven't made it clear:

InStr seems to be an overloaded function. These functions can be called
at the same time, but they have different boundary conditions, which are
_not_ documented.

They have serious side effects.
It would be the equivalent of trying to use Word without a
document open, or Excel without a workbook open.
Click to expand...
Never used an Excel function in Access?

Public Function ExcelMin(ParamArray Args()) As Double

Dim objExcel As New Excel.Application

ExcelMin = objExcel.WorksheetFunction.Min(Args())

End Function
Exactly what are you trying to do that you feel it's necessary to have VBA
functionality available to you without having an MDB open?
Click to expand...
A test revealed a serious side effect of an overloaded function, how can
this be ignored?


mfG
--> stefan <--
 
Can you provide a realistic example of what kind of errors you're seeing?

The function not working properly in the Immediate Window doesn't
particularly concern me. Only if it also doesn't work in code would I be
concerned.
 
You can use instr unless you open a mdb file. This has NOTHING to do with
So, would you appreciate a new thread?
Click to expand...

No, not at all. The wording of your subject suggested that the problem was
you can't search for a * in a string.

In fact, the problem is you can't search ANY character in a string without
first loading a mdb file. Not really a big deal, but it would have been MUCH
more useful to point that you can't search for any character (else, one
would have to conclude this is some type of delimiter problem...which it is
not).

Not a big deal here..but the problem was not clear until discovered that
instr() for ANY character did not work...
But i can use

? instr(1, "abc", "b", vbCompareText)
Click to expand...
InStr seems to be an overloaded function. These functions can be called at
the same time, but they have different boundary conditions, which are
_not_ documented.
Click to expand...

I have absolute no idea as to why the fact of InStr being a overloaded
function is relevant here? I don't think the failue is due to any boundary
issue
at all...

Hum, it might overloaded function, but that would not explain in ANY way as
to why the 2nd
use of the function works without the extra parameters....

On the other hand, since you supply a vbTextCompare, perhaps the method
simply sets some type of code page for the language used, and thus sets up
the type of string compare to use.

Remember, most code modules for their first line have

Option Compare Database

So, the fact of a overloaded function really don't matter here much ....

It very simply might be a matter of setting the code page, or the type of
string comparison to use. Once set..then the function can operate. This
seems like a far more likely reason as to why it works the 2nd time....

What this means is that this might not be a issue of libraries not being
loaded, but in fact that the code page, or string comparison method is NOT
set unless you have a mdb loaded. From the a2003 help on instr you get for
the text compare option the following

<quote>
compare : Optional.

Specifies the type of string comparison. If compare
is Null, an error occurs. If compare is omitted, the Option Compare setting
determines the type of comparison. Specify a valid LCID (LocaleID) to use
locale-specific rules in the comparison.
</quote>

So, it seems it is documented that if you omit the compare setting, then the
one you set in the module is used....but, how can that occur, since you have
no code modules loaded?

At the end of the day, it simply means that some functions and methods will
operate differently in ms-access depending on the MANY MANY settings that
ms-access has. I don't think it goes without saying that these settings need
to be set correctly before some functions can be used. How strings compare,
or even things like how sql will be processed is a option in the
tools->options.

So, trying to use ms-access as a automation (com) object sounds like a risky
idea unless you open up a mdb.....
 
hi Albert,
No, not at all. The wording of your subject suggested that the problem was
you can't search for a * in a string.
Click to expand...
My fault, it is the subject from the original post. While testing his
problem, i stumbled open this issue, which on my behalf is far more
interesting.
Not a big deal here..but the problem was not clear until discovered that
instr() for ANY character did not work...
Hum, it might overloaded function, but that would not explain in ANY way as
to why the 2nd
use of the function works without the extra parameters....
Click to expand...
The syntax is documented as

InStr([Start, ]String1, String2[, Compare])

In all programming languages i know, the first optional parameter can
only be realized with overloading these functions:

InStr(Start, String1, String2[, Compare])
and
InStr(String1, String2[, Compare])
On the other hand, since you supply a vbTextCompare, perhaps the method
simply sets some type of code page for the language used, and thus sets up
the type of string compare to use.
Click to expand...
There is another nice thing you can try:

? InStr("abc", "b", vbCompareText)

This will result in a runtime error 13, unkown type.

This indicates that some libraries not loaded.
So, trying to use ms-access as a automation (com) object sounds like a risky
idea unless you open up a mdb.....
Click to expand...
Yes, we have to draw this conclusion.


mfG
--> stefan <--
 
stefan hoffmann said:
hi Albert,

There is another nice thing you can try:

? InStr("abc", "b", vbCompareText)

This will result in a runtime error 13, unkown type.

This indicates that some libraries not loaded.
Click to expand...

I wouldn't assume that some library isn't loaded because of that.

You're correct that the function is overloaded. Realistically, though, all
it's doing is looking at the data type of the first argument passed. If it's
numeric, it's assuming that a Start position is being passed, If it's text,
it's assuming that a Start position is not being passed, in which case it
only accepts two arguments (String1 and String2).

Help is quite explicit about this: "The start argument is required if
compare is specified."
 
Back
Top