convert string to integer in ADOquery SQL

Posted on 2014-08-05
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
    LVL 100

    Expert Comment

    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

    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

    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 100

    Accepted Solution

    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


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    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.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now