How to search between two tables in VB6

[cdhill82083]

I am trying to develop an application that searches between a range of numbers in an access database. I am having trouble figuring out how I would write the SQL query for this or what would be the best way to perform this search? I am not very familiar with database programming in vb6 yet, but I am in the process of learning.
Any help is much appreciated.

Thanks :~)

cdh82083

 

[johnwm]

Look up the BETWEEN keyword in SQL Books on Line - it should be your constant comapnion for SQL stuff. Free download available from:

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[cdhill82083]

Thanks johnwm! I will check into this and keep you updated.

cdhill82083

 

[cdhill82083]

Okay, the between operator is not what I need. I'm looking for something more in the way of say:

User inputs a number into txtNum, command button executes SQL statement to find record that matches the following criteria then populates labels with the corresponding data.

strSQL = SELECT * FROM TableName WHERE txtNum >= Field1 and txtNum <= Field2

Am I headed in the right direction? Any help is greatly appreciated.

cdh82083

 

[ca8msm]

That is basically the same as the between function. i.e.

strSQL = "SELECT Table1.FIELD1 FROM Table1 WHERE Table1.FIELD1 Between " & txtLowNumber & " And " & txtHighNumber

will return the same as

strSQL = &quot;SELECT Table1.FIELD1 FROM Table1 WHERE Table1.FIELD1 >= &quot; & txtLowNumber & &quot; And <= &quot; & txtHighNumber

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[johnwm]

Check out SQL BOL as I said before!

You've just got your WHERE clause the wrong way round and as ca8msm says, you also need to concatenate the string with the variable.

strSQL = SELECT * FROM TableName WHERE txtNum >= Field1 and txtNum <= Field2

should read:

strSQL = SELECT * FROM TableName WHERE Field1 < &quot; & txtNum & &quot; and Field2 > &quot; & txtNum

If the fields are defined as string rather than numeric, then your variables will be delimited with ticks (') thus:

strSQL = SELECT * FROM TableName WHERE Field1 < '&quot; & txtNum & &quot;' and Field2 > '&quot; & txtNum & &quot;'&quot;

ca8msm - he's using 2 fields not 2 variables

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[ca8msm]

oops missed that...sorry!

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[johnwm]

I missed it first time round as well!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'