 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / August 2004Help with WildCards
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.... > I'm sorry if this question has been asked before, but I did a search and > didn't find anything... [quoted text clipped - 10 lines] > 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 > > I'm sorry if this question has been asked before, but I did a search and > > didn't find anything... [quoted text clipped - 22 lines] > hth > -ralph Ralph, 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?  SignatureAl Reid How will I know when I get there... If I don't know where I'm going? > > > I'm sorry if this question has been asked before, but I did a search and > > > didn't find anything... [quoted text clipped - 28 lines] > 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? 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 > LOL. Missed you by 2 minutes. > [quoted text clipped - 10 lines] > > -ralph I usually assume ANSI standard SQL and go off searching when it doesn't work. SignatureAl Reid How will I know when I get there... If I don't know where I'm going? <snipped> > I usually assume ANSI standard SQL and go off searching when it doesn't work. 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 > > "Ralph" <nt_consulting32@hotmail.com> wrote in message > news:uZSynMpjEHA.2340@TK2MSFTNGP11.phx.gbl... [quoted text clipped - 10 lines] > > -ralph 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()?... SignatureAl 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 Ha. 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 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 > Ha. > [quoted text clipped - 10 lines] > > -ralph > I'm sorry I didn't specify what DB I'm using. I'm on VB6 SP5, ADO 2.8 using > Access drivers. [quoted text clipped - 13 lines] > 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 I understand it is difficult to answer a question when you don't have all the information. I think I have what I need now, although the '#' doesn't seem to work. I thank you again for your help! Joe > > I'm sorry I didn't specify what DB I'm using. I'm on VB6 SP5, ADO 2.8 > using [quoted text clipped - 42 lines] > hth > -ralph How are you attempting to use # as a delimiter? One thing often forgotten is that, regardless of what the Regional Settings have the short date format to, you cannot use dd/mm/yyyy for your dates. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > I understand it is difficult to answer a question when you don't have all the > information. [quoted text clipped - 50 lines] > > hth > > -ralph |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.