SQL Help, working with like and where

[bighavlo]

I am writing a program to search contracters and job numbers
If you misspell the name it doesn't find anything. You can search by last name, contractor, job number, and job site.

When switching to search for job number I want the files to be in order by job number so I have the following code:

Private Sub AdojobnumAdo()
frmcontract.Adobuilder.RecordSource = "select * from_ _ tbljobinfo order by fldjobnum"
frmcontract.Adobuilder.Refresh
End Sub

Now I just want it to return the closest job number to it
Example if I search for job number 1255 and there is no job 1255 it returns 1254.

Please Help - Thanks Matt

 

[CCLINT]

SELECT TOP 1 *
FROM tbljobinfo
WHERE fldjobnum < 1255
ORDER BY fldjobnum DESC

Enclose the 1255 in single quotes ('1255') if it is a Text field

 

[DeltaTech]

Might be better if &quot;WHERE fldjobnum <= 1255&quot;. That way if there is a Job 1255, that will be returned.

 

[CCLINT]

Yes! This was meant. My oversight during typing.

 

[bighavlo]

I used a bad example, try this one:

Say i am looking for the contractor name &quot;Best Built Homes&quot;
My user mispells it as Best Bilt Homes
or doesnt't use Capitalization.
My Problem is that if the user is looking for &quot;Best Built Homes&quot; and the user types one of the following:
best built homes
best bilt homes
best built

I want it to return as if I used Like &quot;Best%&quot;
the problem is I need to use quotes inside the sql statement and I get an error because I need to use quotes to tell it the following

frmcontract.Adobuilder.RecordSource = &quot;select * from_ _ tbljobinfo order by fldjobnum&quot;

The Error shows up when I add another set of quotes

frmcontract.Adobuilder.RecordSource = &quot;select * from _ _
tbljobinfo where fldjobinfo like &quot;txtbuilder.text%&quot;

I can make a string where the contractors name is dropped to 4 spaced (Best) and then concatenate it with % so that it searches for Best%. I just can't get it to work.

Would I be better off creating a data grid and having the user select the appropriate contractor??

You would think VB could search no matter what the case of letters and based out of a text box. Can this be done???

Thanks much - Matt

 

[billybobk]

I can't tell exactly what you are trying to do, but in vb, when writing queries, use the ascii value for your single quotes, especially around variables. (Single quotes are easy to use around literals, but not variables like txtText.text).
------------------
Dim sQ as String
sQ = chr$(39) 'ascii value for single quote
strQuery = &quot;Select * from tblJobInfo where fldJobInfo &quot; & _
&quot;like &quot; & sQ & txtBuilder.text & sQ
----------
Now you have single quotes around your textbox text.
hope this helps!

--Bill
Beeeeer....Mmmmmmmmmm.
-Homer Simpson

 

[billybobk]

I should add that queries ARE NOT case sensitive, as you surmised.

--Bill
Beeeeer....Mmmmmmmmmm.
-Homer Simpson

 

[CCLINT]

>I used a bad example
Yes, a very bad one. This is a completely different thing now.

See:
thread709-598295

and make sure you use &quot;OR&quot; and not &quot;AND&quot; in the criteria, otherwise you will always return zero records if one of a multiple search criteria word is misspellt or not found.
Please note that this will then return all combinations of the serach criteria words.

 

[CCLINT]

>I should add that queries ARE NOT case sensitive, as you surmised.

Depending on the provider's functionalitly, you may be able have case sensitive searchs....even with JET MDB...

 

[billybobk]

CCLINT: I've never myself seen a case sensitive provider, but wouldn't you agree that, most likely, he needn't worry about it? Out of curiousity, where have you experienced this?

--Bill
Beeeeer....Mmmmmmmmmm.
-Homer Simpson

 

[CCLINT]

>he needn't worry about it

I've never needed case sensitive searches.

>case sensitive provider
Some providers can do this, if the db is built from scratch that way, with this option.

Otherwise, Provider use functions such as the JETVBA InStr() function with a binary compare.

 

[bighavlo]

Thanks guys for all your help
- Matt

 

[bighavlo]

Tried it and can't get it to work
I should let you know I have no idea how to do anything with a query in vb. any help or links to this info. -

Thanks much again - Matt

 

[billybobk]

What kind of database? What is the exact text of your error messages?

--Bill
Beeeeer....Mmmmmmmmmm.
-Homer Simpson

 

[billybobk]

If it is Access, I wrote an FAQ called &quot;ADO basics....&quot; under Databases in the FAQ section. Do a simple query like &quot;Select fldJobNum from tblJobInfo&quot; and assign it to a textbox with a scroll bar or a listbox like this: (using the rest of the code in the FAQ, of course, to connect!)
--------------------------
Set txtJobNum.DataSource = rsMyRecordset
With txtJobNum
.DataField = &quot;fldJobNum&quot;
.Refresh
End With
-----------------
if this gets you data, then we'll nail down the query.



--Bill
Beeeeer....Mmmmmmmmmm.
-Homer Simpson