Little background on project.
I'm pretty new at VB6 and database stuff.
I'm using a MS Access database
Use DAO 3.6 as reference

Ok here is the problem... when I first made my table I set almost every column as a Text type other than the few dates I had.

Now that I have gotten more into my program I have added a ORDER BY in my SQL statements during a query.

Because I have everything set to text it doesn't sort Numbers right. For example

10
110
20
21
25
32
345
37

That is what it would do.

But when I changed my column to Number format in the database. I get a mismatch critiria error with my queries.

Searchmore gets it's value from text1.text it could be a string, number, or date.
Searchfield gets it's value from a combobox

I tried this below to change it from a string to numeric. BUt still got the same error and it points me to my query statement.

Code:
If isnumeric(text1.text) = true then
searchmore = val(text1.text)
else
searchmore = trim(text1.text)
end if

I am guessing I have to make a special SQL statement if the variable searchmore is numeric? Or if the column that I am query is not text?


Code:
Set rs = db.OpenRecordset("Select * from Production where " & Searchfield & " < '" & Searchmore & "' Order by " & Sortby & " " & Sortorder)

Works with a LIKE but not if I query with < , > , <>.


Code:
Set rs = db.OpenRecordset("Select * from Production where " & Searchfield & " LIKE '" & Searchmore & "' Order by " & Sortby & " " & Sortorder)

So what do I need to do to be able to search that Column that is now in Number format and not text?

Thanks Again

 

 

 

Reply

All you have to do, is leave out single quotation marks (')

That is, following code:
will not work, because you put Searchmore variable inside single quotes, and that tells database engine to evaluate it as a string (or text).
What you need to do, is simply leave out those single quotes, like this:
and this code should work fine now.

When you want something evaluated as string (or text), you put it inside single or double quotes. And if you want it evaluated as a number, just leave out the quotes.

Here are two examples, of SQL statements, one for text, and one for number search:
You can use all comparison operators (<, >, <>, =, >=, =<, LIKE, etc.) with both text and numbers. LIKE is however most usefull when comparing strings...

Hope this cleared things up

 

 

 

Reply

Sorting is still difficult. But try to ust you texts to numeric using function StringToIng or something. Then use the sorting function. It could help you.

Reply