Hi,
I am really struggling to get this working. Im using an Access Database *.mdb in case that helps.

I am trying to check the database for stock levels, I would like to submit a number via a form, and have the database checked to see if the value is equal to or less than the number submitted. It should be straight forward, but I need help.

This Code works, but defeats the purpose:
rsResults.Source = "SELECT ProductID, SKU, ProductName FROM Products WHERE Quantity like '%" + Replace(rsResults__var1, "'", "''") + "%'"

I have also tried:

rsResults.Source = "SELECT ProductID, SKU, ProductName FROM Products WHERE Quantity < '%" + Replace(rsResults__var1, "'", "''") + "%'"

it doesnt work, neither does:

rsResults.Source = "SELECT ProductID, SKU, ProductName FROM Products WHERE Quantity =< '%" + Replace(rsResults__var1, "'", "''") + "%'"

Please stop me tearing my hair out someone.....
Joe
:cry:

Just to clarify things in case they are unclear.
The first example:
rsResults.Source = "SELECT ProductID, SKU, ProductName FROM Products WHERE Quantity like '%" + Replace(rsResults__var1, "'", "''") + "%'"
Works in as much as it finds an exact match for the number submitted/searched for.
But the whole point is to be able to search for a number and have all the numbers that are equal to or less than it in that field found.

Even if you can help to just find the numbers that are less than the number searched for that will really help.

Hi,
I am really struggling to get this working. Im using an Access Database *.mdb in case that helps.

I am trying to check the database for stock levels, I would like to submit a number via a form, and have the database checked to see if the value is equal to or less than the number submitted. It should be straight forward, but I need help.

This Code works, but defeats the purpose:
rsResults.Source = "SELECT ProductID, SKU, ProductName FROM Products WHERE Quantity like '%" + Replace(rsResults__var1, "'", "''") + "%'"

I have also tried:

rsResults.Source = "SELECT ProductID, SKU, ProductName FROM Products WHERE Quantity < '%" + Replace(rsResults__var1, "'", "''") + "%'"

it doesnt work, neither does:

rsResults.Source = "SELECT ProductID, SKU, ProductName FROM Products WHERE Quantity =< '%" + Replace(rsResults__var1, "'", "''") + "%'"

Please stop me tearing my hair out someone.....
Joe
:cry:

why are you treating quantity like it is a text field?
try this

rsResults.Source = "SELECT ProductID, SKU, ProductName FROM Products WHERE Quantity <= " +rsResults__var1

At last, Superb, Thanks so much.....
Joe

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.