convert string to integer in ADOquery SQL

Hi: I have the following query on a form. It reads an MSSQL database over which I have no control. Has worked fine for a year until  a user has decided to add "(1)"  to an invoice number at which point the WHERE clause  "AND Dbo_goorders.InvNo<1" falls over with an error.
Because it is SQL does anybody know how to get a conversion function into the code, in order that it can read the invoice number? Incidentally it may be better to say "InvNo is BLANK" ; OR "InvNo is NULL" but I don't know how to do that, either!
Thanks!

ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('SELECT Dbo_goorders.OrderNo, Dbo_goorders.InvNo, Dbo_goorders.Ordered,Dbo_goorders.Delivered, Dbo_goorders.Invoiced, Dbo_goorders.AccNo, ');
 ADOQuery1.SQL.Add('Dbo_goorders.Customer, Dbo_goorders.Goods, Dbo_goorders.Nett, Dbo_goorders.VAT, Dbo_goorders.SurchargeRate, Dbo_goorders.SurchargeVal, Vvdates."DATE", Vvdates."MONTH", Vvdates."YEAR" ');
      ADOQuery1.SQL.Add('FROM vvdates Vvdates INNER JOIN GOOrders Dbo_goorders ');
  ADOQuery1.SQL.Add(' ON  (Dbo_goorders.Delivered = Vvdates."DATE") ');
     ADOQuery1.SQL.Add('WHERE  Dbo_goorders.DocNo > 0  AND Dbo_goorders.Nett >0  AND Dbo_goorders.state = :SELINV AND Vvdates.Month = :SELMONTH AND Vvdates.Year = :SELYEAR AND Dbo_goorders.InvNo<1 order by Dbo_goorders.orderno');
ADOQuery1.Parameters.ParamByName('SELMONTH').Value := StrToInt(Edit1.Text);
ADOQuery1.Parameters.ParamByName('SELYEAR').Value := StrToInt(Edit2.Text);
ADOQuery1.Parameters.ParamByName('SELINV').Value := (Edit3.Text);
 ADOQuery1.Open;
Richard TeasdaleFinancial ControllerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
When the user adds a non-numeric invoice number, what do you want done?
Where are they entering the invoice numbers like that?

Try this

(IsNumeric(Dbo_goorders.InvNo) = 1 AND Dbo_goorders.InvNo<1)

mlmcc
0
Richard TeasdaleFinancial ControllerAuthor Commented:
Thanks...will try in a minute.
What it actually is, is a report of items uninvoiced; ie invoice number is blank, null or less than one. I recall trying all of these options with only the 'invno <1' actully working. Blank or null would be much better; if it helps there is also an invoice date which should be blank or null until invoiced.!
0
Richard TeasdaleFinancial ControllerAuthor Commented:
Thanks, mlmcc. I am pretty sure that the field InvNo is a text field, but the program did seem to work where InvNo <1 until the user of the software added the brackets. Don't ask me how; I have no idea what is going on! When I ran the query with your code it returned blank; I presume because it was looking for numeric values in the field - there are none. At least it ran - huge improvement on my efforts! As I said I think the clue lies in my rubbish coding - if you could help express "WHERE InvNo is NULL" (or BLANK - not sure what an empty value in the field is) then the problem would be sorted!
0
mlmccCommented:
SO you want to see the records that are "invalid"

Try this

(Dbo_goorders.InvNo Is Null OR Dbo_goorders.InvNo  = " " OR IsNumeric(Dbo_goorders.InvNo) <> 1 OR Dbo_goorders.InvNo < 1)

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard TeasdaleFinancial ControllerAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.