3 replies to this topic

[kvarnerexpress]

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

[Galahad]

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

That is, following code:

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

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:

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

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:

SQL = "SELECT * FROM TableName WHERE FieldNumber > 1 ORDER BY FieldNumber ASC;"

SQL = "SELECT * FROM TableName WHERE FieldText LIKE '1' ORDER BY FieldText DESC;"

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

[dul]

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.

[iGuest]

format number in vb6Vb6 Using A Number

I want to print numbers in msword through vb6. The field I want to print has different values in it. I want to print it like this

100 200

20000  500

  500 100

I try to use format function but the result is like this

100 200

20000  500

 

-question by Ahmad Sheeraz Saeed