[Webinar] Learn how to a build a cloud-first strategyRegister Now


convert string to integer in ADOquery SQL

Posted on 2014-08-05
Medium Priority
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!

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);
Question by:ClaytonGlass
  • 3
  • 2
LVL 101

Expert Comment

ID: 40241100
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)


Author Comment

ID: 40241112
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.!

Author Comment

ID: 40241131
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!
LVL 101

Accepted Solution

mlmcc earned 1000 total points
ID: 40241190
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)


Author Closing Comment

ID: 40241212

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question