Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    117

    Unanswered: Need Help in SQL queries

    Hello sir,

    I am grabbing datas from Website and make it to be added into the 2 recordset.
    Intially i created 2 recordsets with MS-Access and mentioned all the field type as text.

    1st Recordset: racing
    Field Name : no1
    Field Type : Text

    2nd Recordset: sporting
    Field Name : no2
    Field Type : Text

    Datas in no1: 15/8, 7/2, 6
    Datas in no2: 5/3, 3, 2/3

    My sql query: "Select racing.no1 from racing,sporting where racing.no1 < sporting.no2"

    But this query displays wrong informations. (I think it is considering the datas only as String)

    What i actually need is to consider them as numbers (ie Fractions)
    For eg: take 15/8 from no1, its value is 7.5 (when divided, 15/8)
    take 5/3 from no2, its value is 1.67

    so my query checks for 7.5 < 1.67 and it is false and no value is displayed.

    Pls help me in this coding

    Thankyou sir.

  2. #2
    Join Date
    Feb 2004
    Posts
    214

    You are correct

    in that the expression is not working because you are comparing strings and not numbers. Fractions can not be stored in access as a number. You will have to import them as text and convert them to a number. Please look at your other post and I have instructions on how to convert it to a number.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  3. #3
    Join Date
    Nov 2008
    Posts
    117
    Ok sir now i know how to convert string to fractions.
    Now what i want is how to add that query to VisualBasic.
    Thank you sir.

  4. #4
    Join Date
    Feb 2004
    Posts
    214

    ...

    You don't make it in visual basic. You make it in query design or SQL.
    I take it that no1 and no2 hold the fraction values. These are two seperate queries, one for each table.

    SELECT Racing.no1, Left([no1],InStr([no1],"/")-1) AS [Left], Right([no1],Len([no1])-InStr([no1],"/")) AS [Right], IIf([Right]=[no1],[no1],([Left]/[Right])) AS Exp1
    FROM Racing;

    SELECT Sporting.no2, Left([no2],InStr([no2],"/")-1) AS [Left], Right([no2],Len([no2])-InStr([no2],"/")) AS [Right], IIf([Right]=[no2],[no2],([Left]/[Right])) AS Exp1
    FROM Sporting;
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  5. #5
    Join Date
    Nov 2008
    Posts
    117
    Can u help me more clear and i am getting trying and unable to see the result

    Consider the below query and pls make change in that. In the below coding all the no,no1,no2 are containing only fraction values and i need all that to changed as fraction type before executing query

    Set rs = New ADODB.Recordset
    Dim strSQL As String
    strSQL = " select racing.name from racing,racesport,sporting where racing.no = racesport.no1 and racing.name = sporting.name and sporting.no <= racesport.no2"
    rs.Open strSQL, db, adOpenForwardOnly, adLockReadOnly
    Do While Not rs.EOF
    Set lvwItem = ListView1.ListItems.Add(, , rs.Fields.Item(0).Value)
    rs.MoveNext
    Loop
    rs.Close

  6. #6
    Join Date
    Feb 2004
    Posts
    214

    ...

    I am not the best at vb, so you may need someone else to help you with that. But if you are just wanting to compare the tables, then I have a sample db that I have attached that you can use as an example for your db.
    Attached Files Attached Files
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •