Re: Help with WildCards
Ralph
"Joe" <J...@discussions.microsoft.com> wrote in message
news:4FB04B46-8A24-46CC...@microsoft.com...
> I'm sorry if this question has been asked before, but I did a search and
> didn't find anything...
>
> I'm getting to really hate ms.com (and pretty much all internet search
> engines), but I can't blame them because there is soooo much information
> their trying to make available. It makes searches for people like me
> useless...
>
> Anyway, all I'm looking for is a list of WildCards for ADO (using VB), and
> exactly what they filter. I'm also looking for a list of characters that
are
> not useable in a Query and what substitue is available.
>
> Geez, it sounds so simple... You would think I could find that on the
web,
> but obviously I'm doing something wrong....
You are merely looking in the wrong place.
WildCards characters are associated with the SQL and Commands of the respect
database engines and not with any particular object library. For example, in
MSAccess you would an asterisk '*' and in Oracle a percent sign '%', in a
LIKE statement.
hth
-ralph
Al Reid
"Ralph" <nt_cons...@hotmail.com> wrote in message news:uGCTM1oj...@tk2msftngp13.phx.gbl...
I agree with you in general. However, under what circumstances do you use an asterisk with an access database from VB with ADO? I
use the standard SQL '%' and it works fine. I have tried to use an asterisk with Access97 and 2000, Microsoft.Jet.OLEDB.3.51 and
4.0 AND ODBC and it does not work. In all cases the % does?
--
Al Reid
How will I know when I get there...
If I don't know where I'm going?
Ralph
Just as soon as you type something you remember an exception. <g>
If using DAO (pure Jet) a single character wildcard is '?', and a zero or
more is '*', but if using the OLE DB Jet 4 ODBC provider (ADO) it would be
an underscore '_' and a '%'.
But overall I believe my advice holds. Research the specific database engine
and the version of SQL it uses to determine the correct 'wildcard'.
Expect to discover exceptions.
-ralph
Ralph
"Al Reid" <are...@reidDASHhome.com> wrote in message
news:ORKQZFpj...@TK2MSFTNGP10.phx.gbl...
LOL. Missed you by 2 minutes.
Yes I caught myself. There is a difference between the 'internal' commands
of Access, using DAO, and using ADO.
I was thinking in the broader terms of Oracle, SQL Server, AS/400, etc.
Where even now, when most engines are using a 'standard' SQL you can still
get blindsided by a specific engine's version of regexpr's, scripting,
filters, etc.
I guess the best you can do is compile a list of the most 'common'
wildcards, followed by a series of exceptions. <g>
-ralph
Al Reid
>
> LOL. Missed you by 2 minutes.
>
> Yes I caught myself. There is a difference between the 'internal' commands
> of Access, using DAO, and using ADO.
>
> I was thinking in the broader terms of Oracle, SQL Server, AS/400, etc.
> Where even now, when most engines are using a 'standard' SQL you can still
> get blindsided by a specific engine's version of regexpr's, scripting,
> filters, etc.
>
> I guess the best you can do is compile a list of the most 'common'
> wildcards, followed by a series of exceptions. <g>
>
> -ralph
>
I usually assume ANSI standard SQL and go off searching when it doesn't work.
Ralph
"Al Reid" <are...@reidDASHhome.com> wrote in message
>
> I usually assume ANSI standard SQL and go off searching when it doesn't
work.
>
> --
> Al Reid
>
A sound, and perhaps the only sane policy.
Now, should we mention 'standard' SQL date and time formats? Or let the OP
wander off into that minefield on his own? <g>
-ralph
Al Reid
You're right about this one. Without knowing what database one is using, the date format is almost sure to be wrong.
Do you enclose it in single quotes? Pound signs? TO_DATE()?...
--
Al Reid
"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so." --- Mark Twain
Joe
Access drivers.
I understand the '%' in doing text searches, but does this also work with
numbers? If I remember right one could use '[0123456789]' to search for any
single digit that is between the brackets, but what wild card do you use for
ANY number? I just wish I could find some good documentation on this. I
have even purchased books and they don't go into details on the wildcards. I
guess it's something you just learn while in the industry.
The other half of my question is where can I find information on illegal
query string characters? I know to replace all ' with a double '', but that
is the only substition I know. I have an enormous database to convert and
there are all kinds of characters in it that have been giving me trouble.
Thanks again, and thank you for your reply!
Joe
Ralph
> I'm sorry I didn't specify what DB I'm using. I'm on VB6 SP5, ADO 2.8
using
> Access drivers.
>
> I understand the '%' in doing text searches, but does this also work with
> numbers? If I remember right one could use '[0123456789]' to search for
any
> single digit that is between the brackets, but what wild card do you use
for
> ANY number? I just wish I could find some good documentation on this. I
> have even purchased books and they don't go into details on the wildcards.
I
> guess it's something you just learn while in the industry.
>
> The other half of my question is where can I find information on illegal
> query string characters? I know to replace all ' with a double '', but
that
> is the only substition I know. I have an enormous database to convert and
> there are all kinds of characters in it that have been giving me trouble.
>
> Thanks again, and thank you for your reply!
> Joe
>
For numbers it is the nanogram '#'.
To get info on Access database wildcard characters just type 'wildcard' in
the MSAccess Help (F1).
We mentioned the other 'standard' sql ones, '%' and '_' (Access '*' and
'?').
The other confusing one would likely be range --
[ A-Z ] - any char between and including A and ending with Z
[^a-b] - '^' means NOT so any char not a thru b.
You can also do stuff like this...
Like '[abc]%' - to get anything starting with a, b, or c.
Probably the quickest way to discover what a particular statement is doing,
if you are not sure, is to post it in this newsgroup or in
"microsoft.public.vb.general.discussion". Others might even know of some
better urls.
Like Al pointed out it is tough to give a specific answer without a specific
query to a specific database using a specific provider to go by.
hth
-ralph
Joe
information.
I think I have what I need now, although the '#' doesn't seem to work. I
thank you again for your help!
Joe
Douglas J. Steele
that, regardless of what the Regional Settings have the short date format
to, you cannot use dd/mm/yyyy for your dates.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Joe" <J...@discussions.microsoft.com> wrote in message
news:C9424738-6725-41E3...@microsoft.com...