not showing correct data

[shizzle08]

im having a problem on my sql command. here is my command

select ProductCode, ProductName, qtyinstock, critical 
    from product where qtyinstock <= critical

but it keeps on showing record that qtyinstock is not less than or equal to critical

see this image http://a4.sphotos.ak.fbcdn.net/hphotos-ak-snc7/482956_437601922940311_1148836844_n.jpg

[Reverend Jim]

What database are you using? If you are using SQLEXPRESS you can download SQL Server Management Studio (free) from Microsoft. You can run the query directly against the database without going through VB, data adaptors, etc. See if you get the same results. If using Access, there is a SQL query window available there as well. If you don't have these tools available I can write you a short vbScript to do the query and show the results in a command line window.

[Reverend Jim]

In case I lose my connection (wireless is spotty at the moment) here is a script you can use to test. Copy and save into query.vbs after modifying as per the comments. Just type the following in a command session from the folder where you saved it:

cscript query.vbs

'------------------------------------------------------------------------------
'
'  Name:
'
'    query.vbs
'
'  Description:
'
'    Query a database table and display all fields in all records returned.
'
'  Notes:
'
'    Modify SERVER, DATABASE and QUERY as appropriate for your system. Depending
'    on your database system (Access, SQL, mySQL, etc) you may have to modify
'    the connection string
'
'  Audit:
'
'    2012-06-29  RevJ  Original code
'
'------------------------------------------------------------------------------

const SERVER   = ".\SQLEXPRESS"
const DATABASE = "mydb"        
const QUERY    = "select * from Attendance"

const adOpenKeySet = 1

'create database access objects

set con = CreateObject("ADODB.Connection")
set rec = CreateObject("ADODB.RecordSet")

'open a connection to the database

con.Open "Driver={SQL Server}"     & _
         ";Server="   & SERVER     & _
         ";Database=" & DATABASE   & _
         ";Trusted_Connection=yes;"

if con.State <> 1 then
    wscript.echo "database could not be opened"
    wscript.Quit
end if

'select and display records from the database table

rec.Open query,con,adOpenKeySet
Dump rec

'close connections

rec.Close
con.Close

'this function displays all records and fields in the given recordset

Function Dump ( rec )

   dim field        'for stepping through field names and values
   dim maxlen       'length of longest field name
   dim name         'field name
   dim value        'field value

   Dump   = 0
   maxlen = 0

   'find the length of the longest field name for output formatting

   for each field in rec.fields
      if len(field.name) > maxlen then maxlen = len(field.name)
   next

   'dump all records in the recordset

   do until rec.EOF

      Dump = Dump + 1

      wscript.echo vbcrlf & "Record:",Dump

      for each field in rec.fields
         name  = field.Name
         value = field.Value & ""
         wscript.echo "  " & name & ":",space(maxlen-len(name)),value
      next

      rec.MoveNext

   loop

   wscript.echo vbcrlf & Dump,"record(s)"
   rec.MoveFirst

End Function

[TnTinMN]

Is it possible that the data type for both qtyinstock and critical are string (nvarchar)? That would explain the results.

If it is then perhaps this will work:

select ProductCode, ProductName, qtyinstock, critical from product where Cast(qtyinstock AS int) <= Cast(critical as int)