Posts by Mavyak

    Re: Cut Off Spaces When Getting Fields From Recordset


    Did you set a reference to the library I specified?


    Also, after you declare the connection and recordset objects you will need to instantiate them like so:

    Code
    1. Set cnPubs = New ADODB.Connection
    2. Set rst = New ADODB.Recordset


    While this looks almost the same as what you had, doing it this way gets you the method structure in a tool-tip along with drop-down menus of available properties/arguments for the methods.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]

    Quote from Huija
    Code
    1. stSQL = "select ASCII(Right( [ Name], 1)) from tbltest"


    I don't know why, but today he just says that Name is invalid.


    This site induces links in code. When you copied and pasted the SQL I wrote, it added a space between the open bracket and the word "Name". Remove that space so that the field name looks like "[Name]".

    Re: Cut Off Spaces When Getting Fields From Recordset


    The cursor type should come between the connection object and the record-locking property.


    Try this:

    Code
    1. rst.Open stSQL, cnPubs, adOpenStatic, adLockReadOnly


    If you set a reference to Microsoft ActiveX Data Objects 2.8 Library and then declare your recordset and connection vairables like this:

    Code
    1. Dim cnPubs As ADODB.Connection
    2. Dim rst As ADODB.Recordset


    then when you type "rst.Open " a dropdown list of options or at least a tool-tip will display indicating what parameters the Open method of the recordset object requires/expects.


    HTH,


    Mav

    Re: Cut Off Spaces When Getting Fields From Recordset


    Try this:

    Code
    1. stSQL = "select ASCII(Right([Name], 1)) from tbltest"


    This will check to make sure it's a space at the end. Occasionally a tab or null gets appended/stored on the end of the field. If the return value is 32 then it's a space and I don't know why it won't trim off. Here are the other most common hits:
    9 - tab
    13 - carriage return
    10 - line feed
    0 - null


    Other return values can be deciphered here: http://www.asciitable.com/


    HTH,


    Mav

    Re: Setting A String Variable To The Activecell Text


    Looks like you turned your "Else" into a line label. Try this:


    Code
    1. TestCurrentTeam:
    2. If Left(CurrentTeam, 9) = Left(HomeTeam, 9) Then
    3. Goto OK
    4. ElseIf Left(CurrentTeam, 9) = Left(AwayTeam, 9) Then
    5. Goto OK
    6. Else
    7. Selection.End(xlUp).Activate
    8. CurrentTeam = ActiveCell.Text
    9. Goto TestCurrentTeam
    10. End If

    Re: Create 3 Element List From A List Of 7 Words.


    You left off the 7th element. However, the code below should work:


    Replace "Element 7" with the actual name of your element in the code above.


    Enjoy!


    Mav[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Just read the part about not wanting the precise coding. That's actually still okay, because there is a very small piece of code missing above that is causing a few duplicates. Post back if you can't figure it out and I'll reply with the solution.


    Mav[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Disregard the comment about duplicates. I had assumed that:
    Cactus - Rose - Cactus
    Cactus - Cactus - Rose


    was a duplicate because both contain two cacti and one rose. I wasn't aware the order was important. That being said, the code above should work.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Actually, the code is a LOT simpler based on the allowance of duplicates. The basic three loop pattern is still a way to go. The code just needs a bunch of extras removed for you to get the results you're looking for. Let me know if the three loop clue isn't enough to get what you want.


    Mav

    Re: Format Cells With Vba Macro


    Re: Return Records From Access


    Craig,


    Very true.


    suthers,


    I'm sure you will end up with performance issues by stress testing the entire 300k. Especially since an Excel spreadsheet can only handle 65536. Your code should be able to handle a record count greater than 65536 if it's reasonably possible that the query will return that many.

    Re: Return Records From Access


    Also, I believe Access limits the number of values in an IN clause to 1000. That's not confirmed but I'm pretty sure I've run into that problem before. I think it accepts more values if returned by a sub-query like so:


    Select *
    from my_table
    where my_primary_key in(select my_column from my_other_table where x = y)


    But when hard-coding the values as you indicated above, I'm pretty sure you are limited to 1000.

    I regularly create and drop tables/columns/databases in MS SQL Server 2005. The changes are not reflected in the Object Explorer until I manually right click on the affected collection and select "Refresh". Is there a T-SQL command I could add that would refresh it for me. Something along the lines of:


    DROP DATABASE TEST_DB
    SP_REFRESH_DATABASES


    or the like?

    Re: auto Run Constantly



    hth,


    Mav

    Re: Searching Particular Columns


    I think you'll need to change this line:

    Code
    1. Set My_Result = Worksheets("data").Cells.Find(Flight_Number, [A2], xlValues, xlWhole, xlByColumns)


    and this line:

    Code
    1. Set My_Result = Worksheets("data").Cells.FindNext(My_Result)


    by replacing "Cells" with "Range(<your column addresses here>)"


    For example(s):

    Code
    1. Set My_Result = Worksheets("data").Range("F5:F65536").Find(Flight_Number, [A2], xlValues, xlWhole, xlByColumns)


    Code
    1. Set My_Result = Worksheets("data").Range("F5:F65536").FindNext(My_Result)


    hth,


    Mav

    Re: Check If String Contains Only Digits


    Two muffins are sitting in an oven. One muffin turns to the other and says, "Man, it's hot in here." The other muffin replies, "Holy smokes...a talking muffin!"