Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-11-2019, 01:22 AM   #16
DiogoCuba
On Error GoTo AWF *****:
 
DiogoCuba's Avatar
 
Join Date: Dec 2018
Location: Rio de Janeiro - Brazil
Posts: 127
Thanks: 78
Thanked 3 Times in 1 Post
DiogoCuba is on a distinguished road
Re: Syntax problem on building SQL using wildcard * on a numeric field

I would like to thank you all for the information about the use of wildcards on strings, dates and number fields whe running the queries.

I feel it is very important to know why one should NOT DO something as much as knowing one should DO and you always provide both.

Regards,

__________________
Best regards,
Diogo Cuba

"Stay Hungry, Stay Foolish: you only live once, so make it count and go after what you want no matter what."
DiogoCuba is offline   Reply With Quote
Old 01-11-2019, 03:06 AM   #17
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,916
Thanks: 40
Thanked 3,542 Times in 3,423 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Syntax problem on building SQL using wildcard * on a numeric field

Quote:
However I recall there is some scenario that does not allow a leading wildcard. Can't exactly remember what right now. Maybe the Find or Filter on ADO recordsets?
At one time you could not use leading wildcards in ADO recordsets, but I believe you can now.

As with other responders I avoid leading wildcards like the plague (although I accept there are occasions where they are unavoidable) - most users will know whatever they are looking for starts with and I train them to include a wildcard if necessary. Even starting with an initial letter before including a * will be roughly 26 times faster then just starting with a * - assuming the field is indexed of course, otherwise I guess it doesn't matter since indexed fields will be searched sequentially.

with regards using Like with numbers - yes it works, however I don't know whether indexing is used per se since the number is not stored as separate characters but as its binary equivalent.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Galaxiom (01-11-2019)
Old 01-11-2019, 01:01 PM   #18
DiogoCuba
On Error GoTo AWF *****:
 
DiogoCuba's Avatar
 
Join Date: Dec 2018
Location: Rio de Janeiro - Brazil
Posts: 127
Thanks: 78
Thanked 3 Times in 1 Post
DiogoCuba is on a distinguished road
Re: Syntax problem on building SQL using wildcard * on a numeric field

Quote:
Originally Posted by CJ_London View Post
...
with regards using Like with numbers - yes it works, however I don't know whether indexing is used per se since the number is not stored as separate characters but as its binary equivalent.
Indeed when I first saw Arnelgp“s answer I noticed it was working and it was not necessary to convert that number to a string. The field I was searching was indexed though.

BUT I decided to change the approach to this thanks to Colin“s advise to change that textbox to a combobox plus all the explanation provided by The_Doc_Man and PatHartman convinced me I was going for the wrong game...

Nonetheless I will apply what I have learnt here in another part of my small and first project if necessary.


__________________
Best regards,
Diogo Cuba

"Stay Hungry, Stay Foolish: you only live once, so make it count and go after what you want no matter what."
DiogoCuba is offline   Reply With Quote
Reply

Tags
sql , syntax error , vba access 2010 , wildcard

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Building a where clause using IIF and Like wildcard problem gatonegro75 Queries 3 03-26-2010 11:06 AM
Syntax for null numeric field WinDancer Forms 23 12-15-2008 10:36 AM
Wildcard on a numeric field tcaprice Queries 2 06-29-2005 04:47 AM
Building a wildcard SQL stmnt w/VBA mbath20110 Modules & VBA 6 10-31-2003 08:56 AM
Numeric Field Problem Anauz Forms 4 04-27-2001 07:03 AM




All times are GMT -8. The time now is 06:35 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World