sql statement making me go hmmmm

[herewegoagain]

Ok here is the situation....
I have a column we will call COL6MK that is a percent markup of COL6 over COST(which is another column. I have two textboxes on a windows app form that the idea is for the user to enter a number it is greater than(textbox2) and a number it is less than(textbox3). Then they hit search and it is suppose to return these results.
The app does not bomb out but it does not return any results at all which is not correct which is even worse. I tried entering it as 63 and 73 and tried entering 63% and 73% and no luck either way. In the sql database this column is of the varchar variety.
This is the SQL statements I have tried:

sql2 = "SELECT * FROM MARKUP4 WHERE COL6MK BETWEEN %" & TextBox1.Text & "% AND %" & TextBox3.Text & "% ORDER BY NUMBER"

and

sql2 = "SELECT * FROM MARKUP4 WHERE COL6MK > %" & TextBox1.Text & "% AND < %" & TextBox3.Text & "% ORDER BY NUMBER

Does anyone have any suggestions?

 

[Gazzo]

Hi,

if the columns are only holding numeric values then change them from VARCHAR to the correct column type, (int or whatever) then you could use the following statement:

sql2 = "SELECT * FROM MARKUP4 WHERE COL6MK BETWEEN " & TextBox1.Text & " AND " & TextBox3.Text & " ORDER BY NUMBER"

This should work ok,
Gazzo

 

[herewegoagain]

bad news

I left the percent sign in when importing from the text file...is there a quick way to remove them from all 150,000 entries???
By the way thank you for your quick answer.....

 

[Gazzo]

What I would do would be first - create an additional column in your table to store your new values (just incase things go a little wrong) and then you can update it with an update statement and some SQL string functions, then delete your original one and rename you new one.

I cant remember exactly which function you would need, and as I dont have SQL on my machine at home I cannot have a look, but at a guess you could say something like:

UPDATE tbl1 SET newColumn = REPLACE(oldColumn, %, )

Im not sure about the syntax of the REPLACE function, youll have to look that one up.

You could stick a WHERE clause on at the end in order to update only one record, until youre confident that it will work.

Gazzo.

 

[herewegoagain]

thank you!!

I will give this a shot. Otherwise the next update of the db I do I will simply adjust it without percent signs...should hte currency go to in teh currency columns and make those integer?

 

[Gazzo]

Not to sure what youre asking on that one. I would put the COST column to be type MONEY (if you have that type I am getting mixed up here with SQL, Oracle and Access

Gazzo

 

[Gazzo]

Just checked, there is a column type of MONEY.

 

[herewegoagain]

thank you

youve been great........

 

[Gazzo]

no probs, Youre welcome.