We help IT Professionals succeed at work.

convert string to integer in ADOquery SQL

1,133 Views
Last Modified: 2014-08-05
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;
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
Richard TeasdaleFinancial Controller

Author

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.!
Richard TeasdaleFinancial Controller

Author

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!
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Richard TeasdaleFinancial Controller

Author

Commented:
Thanks!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.