Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database not sorted (MS Outlook & VBA)

    I have a script in my MS Outlook and will display the information in the Work4Number table. When I use the pull down to display this data it does not show the newest additions to the list and it is out of order. HOw can I get this correct?

    'Retrieve Category info from table
    Set rst1 = db.OpenRecordset("Work4number")
    Set ctl = Item.GetInspector.ModifiedFormPages("Message").Con trols("cboClient")
    ctl.ColumnCount = 2
    ctl.ColumnWidths = "40; 20 pt"

    'Assign Access data to an array of 2 columns and 500 rows
    CategoryArray(99, 2) = rst1.GetRows(500)
    ctl.Column() = CategoryArray(99, 2)

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

    Re: Database not sorted (MS Outlook & VBA)

    Records in a table aren't stored in any particular order. If you want to return them in a specific order, create a query that sorts the records the way you want and open a recordset on the query, or open a recordset on an SQL statement:

    db.OpenRecordset("SELECT * FROM Work4Number ORDER BY AppropriateFieldName")

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database not sorted (MS Outlook & VBA)

    Select [dbtask.tasknum], [dbtask.date], [dbtask.sent], [dbtask.Reqby], [dbtask.txtstatus], [dbtask.jobtype], [dbtask.form], [dbtask.operator1] FROM dbtask where dbtask.operator1 <> "Not Assigned" and dbtask.txtstatus <> "Finished" and dbtask.group = "S"

    To change the question just a little - I want to be able to select from dbtask.group where it = "S" or "M".

    I know this is a simple question,,,just tired and want to finish this up.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database not sorted (MS Outlook & VBA)

    Try this:

    <pre>Select [dbtask.tasknum], [dbtask.date], [dbtask.sent], [dbtask.Reqby], [dbtask.txtstatus],
    [dbtask.jobtype], [dbtask.form], [dbtask.operator1] FROM dbtask
    where (dbtask.operator1 <> 'Not Assigned') and (dbtask.txtstatus <> 'Finished') and
    (dbtask.group not (In('S','M')));
    </pre>


  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database not sorted (MS Outlook & VBA)

    Getting a syntex error in query expression from "where (dbtask.operator1......)));
    Select [dbtask.tasknum], [dbtask.date], [dbtask.sent], [dbtask.Reqby], [dbtask.txtstatus], [dbtask.jobtype], [dbtask.form], [dbtask.operator1] FROM dbtask where (dbtask.operator1 <> 'Not Assigned') and (dbtask.txtstatus <> 'Finished') and (dbtask.group not (In('S','M'')));

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Database not sorted (MS Outlook & VBA)

    Are you perhaps dealing with unpopulated values (i.e., Nulls) in dbTask.Operator1? Your SQL doesn't allow for that and you can't compare Nulls to anything. That means you also need to specify the dbTask.Operator1 is not Null and dbTask.txtStaus is not null and dbTask.group is not Null, or any of those conditions that might actually occur.
    Charlotte

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Database not sorted (MS Outlook & VBA)

    I assume it worked before you changed the very last part of the query. Is that correct? With the same data?

    Before:
    <code>Select fields
    FROM dbtask
    where dbtask.operator1 <> "Not Assigned" and dbtask.txtstatus <> "Finished" and dbtask.group = "S"</code>

    After:
    <code>Select fields
    FROM dbtask
    where (dbtask.operator1 <> 'Not Assigned') and (dbtask.txtstatus <> 'Finished') and (dbtask.group not (In('S','M'')));</code>

    You wrote:
    <hr>I want to be able to select from dbtask.group where it = "S" or "M".<hr>
    Perhaps change that last part to:

    <code>(dbtask.group In ('S','M''))</code>

    (Just a guess. I don't write many queries.)

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Database not sorted (MS Outlook & VBA)

    One small change:
    <code>(dbtask.group In ('S','M'))</code>
    There were too many quotes in there!
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database not sorted (MS Outlook & VBA)

    Not sure what you mean by Nulls, that is your right there are some records that are blank but did not think about that before. Will look that up and see how to deal with the nulls.

    Thanks.

  10. #10
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database not sorted (MS Outlook & VBA)

    At least two thoughts have arisen in the ugly light of morning (in these parts)...

    Rory mentioned the extra apostrophe in the In() function. If I did that, I apologize.

    Also, what type of object is dbtask. I cracked open the Outlook VBA Editor, and didn't see much illumination as far as groups and tasks were concerned.

    Here's my point: a task could possibly belong to more than one group, at least in theory. If this is a possibility, you might need to interrogate all the groups associated with dbtask to verify the absence of 'S' and 'M'. If a task may only belong to one group, the original SQL (without the extra apostrophe) should work.

  11. #11
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database not sorted (MS Outlook & VBA)

    Did some looking around and I think that Outlook VBA Editor is not powerful enough to handle this. I need to see if I can convert this to a VB6 format. As for the ))) or )), I tried both and I still get the same syntax error. I am still looking into the Nulls in all the fields (operator1, txtstatus) as there are some. As for the S&M, the full range of options are C,S,M and the rare blank. The combinations I will be looking for later are:

    Report 1: C
    Report 2: S
    Report 3: SM

    Reports 1 & 2 are working, but I have not checked to see if the blank field will make a difference. Report 3: Is my problem child.

    What I am going to do is close this message and rebuild Report 3 using VB 6 software, and make the report into a display using datagrid to show the report online
    only.

  12. #12
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database not sorted (MS Outlook & VBA)

    Following for the notion of "I can run, but I can't hide", SQL is going to raise its head one way or another. Whether VBA or VB6, the SQL is going to have be addressed.

    If we break the SQL down to sections, you have:

    <pre>Select [dbtask.tasknum], [dbtask.date], [dbtask.sent], [dbtask.Reqby],
    [dbtask.txtstatus], [dbtask.jobtype], [dbtask.form], [dbtask.operator1] FROM dbtask
    </pre>


    The fields appear to be correctly identified. All the fields are from one table, so no joins enter into play

    Drum roll for the where condition:

    <pre>where
    (dbtask.operator1 <> 'Not Assigned') and
    (dbtask.txtstatus <> 'Finished')
    </pre>


    So far, so good. Equal number of left and right parentheses. I used apostrophes instead of quotes. Either one will do.

    Here comes the nasty part:

    <pre>and (dbtask.group not (In('S','M')))
    </pre>


    As an alternative to the group field, you could also try:

    <pre>and (dbtask.group = 'S' or dbtask.group = 'M');
    </pre>


    A Not(In( )) is occassionally helpful when I have a long list of exceptions for which to test.

    Again, whether you go VB6 or VBA, the SQL is the beast to be tamed.

  13. #13
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database not sorted (MS Outlook & VBA)

    Tried this format and it seems to be working: and ((dbtask.group = "M") or (dbtask.group = "S"))


    This seems to be working and so far I see all the informaiton I was expecting. Thanks to everyone for helping on this.

  14. #14
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database not sorted (MS Outlook & VBA)

    ok, Foot in mouth time.

    Charlette: How do I test for a null entry?

  15. #15
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database not sorted (MS Outlook & VBA)

    You've seen the first part before:

    <pre>Select [dbtask.tasknum], [dbtask.date], [dbtask.sent], [dbtask.Reqby], [dbtask.txtstatus],
    [dbtask.jobtype], [dbtask.form], [dbtask.operator1] FROM dbtask
    </pre>


    Slight modification:

    <pre>where (
    ((dbtask.operator1 Is Not Null) and (dbtask.operator1 <> 'Not Assigned'))
    and
    ((dbtask.txtstatus Is Not Null) and (dbtask.txtstatus <> 'Finished'))
    and
    ((dbtask.group = 'S') or (dbtask.group = 'M'))
    );
    </pre>


    You should not need to test that dbtask.group is not null. If it is null it is not S or M...

Posting Permissions

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