Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Checkup (Access 2000)

    I have a query consisting of the columns orderid, quantity and affiliate.I want to build a code that checks up if there is an order where the quantity is 0,and if so to give the information about the number of the order and the number of the affiliate.
    My sql is the following :
    Dim SQL as string
    SQL = "SELECT orders1.orderid, [order details1].Quantity, Customers1.afiliate FROM (Customers1 INNER JOIN orders1 ON Customers1.Customerid = orders1.customerid) INNER JOIN [order details1] ON orders1.orderid = [order details1].OrderID;

    And the conditions i want to enter are the following :
    If [order details1].Quantity = 0
    MsgBox " Attention ! No quantity in the order Nr ...... and affiliate Nr....
    Can you help me build the check up ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checkup (Access 2000)

    You can add the condition to the query, open a recordset and loop through the records:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String

    strSQL = "SELECT orders1.orderid, Customers1.affiliate FROM (Customers1 " & _
    "INNER JOIN orders1 ON Customers1.Customerid = orders1.customerid) " & _
    "INNER JOIN [order details1] ON orders1.orderid = [order details1].OrderID " & _
    "WHERE [order details1].Quantity = 0"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    Do While Not rst.EOF
    MsgBox "Attention! No quantity for Order nr " & rst!OrderID & _
    " and Affiliate nr " & rst!Affiliate, vbInformation
    rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checkup (Access 2000)

    Thank youfor your reply.Would you please have a look at my database? I somehow cannot manage it, perhaps i have given you wrong details
    regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checkup (Access 2000)

    You have no records for which Quantity = 0. There is a record for which Quantity is null (blank), but that is not the same as 0 (zero). If you also want to test for null values, you can do the following:

    strSQL = "SELECT orders1.orderid, Customers1.affiliate FROM (Customers1 " & _
    "INNER JOIN orders1 ON Customers1.Customerid = orders1.customerid) " & _
    "INNER JOIN [order details1] ON orders1.orderid = [order details1].OrderID " & _
    "WHERE Nz([order details1].Quantity,0) = 0"

    (The rest of the code remains the same)

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checkup (Access 2000)

    Thank you, it is fine, works excellent! Can i substitue the number affiliate with words, for example if rst!Affiliate = 1 to read 'Berlin' if rst!Affiliate = 2 to read Frankfurth ' etc. If not, it is OK, the code is excellent anyway

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checkup (Access 2000)

    You'd need to create a table with two fields: Affiliate and City (or whatever you want to name it). You can then add this table to the query.

Posting Permissions

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