Guest herewegoagain Posted September 19, 2002 Posted September 19, 2002 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? Quote
Gazzo Posted September 19, 2002 Posted September 19, 2002 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 Quote
Guest herewegoagain Posted September 19, 2002 Posted September 19, 2002 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..... Quote
Gazzo Posted September 19, 2002 Posted September 19, 2002 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 can't remember exactly which function you would need, and as I don't 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, '%', '') I'm not sure about the syntax of the REPLACE function, you'll have to look that one up. You could stick a WHERE clause on at the end in order to update only one record, until you're confident that it will work. Gazzo. Quote
Guest herewegoagain Posted September 19, 2002 Posted September 19, 2002 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? Quote
Gazzo Posted September 19, 2002 Posted September 19, 2002 Not to sure what you're 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 Quote
Gazzo Posted September 19, 2002 Posted September 19, 2002 Just checked, there is a column type of MONEY. Quote
Guest herewegoagain Posted September 19, 2002 Posted September 19, 2002 thank you you've been great........ Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.