Posts by bnix

    Re: if date

    Welcome to the Forum!

    This one way you could do it using VBA.

    As i said this is only one way, you could do it with a UDT (User Defined Function) or you could also take a formula approach.

    Re: recordset output in Excel

    One way you could try;

    1. TRANSFORM Sum(price) AS SumOfPrice
    2. SELECT delivdate, Sum(price)
    3. FROM <your_table>
    4. GROUP BY delivdate
    5. PIVOT priceindex

    I put some of your data into an Access DB and it worked for me. It will add the summed column, but you can do with that as you wish.

    Re: Worksheetfunctions in VBA

    I do not think you can access the XNPV and XIRR using WorkSheetFunctions, you can access the NPV and IRR using it (or at least it does not show up in the intellisense).

    If you want to use the XNPV and XIRR in your VBE you can set a reference to the atpvbaen.xls and then you should be able to use the XNPV and XIRR. as

    1. Dim variable
    2. variable = XNPV(rate, values, dates)

    I have not used these functions very much so I may not be the best one to speak on this matter.

    Re: Adding leading Zeros to values in a cell

    Well, you can do a formula as one way then.

    Add a column adjacent to the data you are trying to change
    In the first cell add =0&A1, where A1 is the first row of data you are trying to change.
    Either copy the formula, then highlight the entire column where you just placed the formula and select paste.
    Fill down as far as needed with the formula.

    Re: Adding leading Zeros to values in a cell

    Welcome to the forum!

    One way would be to select your column with the data.
    Then Right Click and select "Format Cells"
    Select the Custom Option in the Categorie List
    In the "Type" text box enter "0###"
    Click OK

    That should do it for you. I enterd 3 "#" characters, but you can enter in as many as the length of your data is needed.

    Re: Speeding up my loop

    In addition to what Bill has mentioned you may want to look at setting the calculation to manual prior to running the code, If calculation is set to automatic that might cause some lag.

    Re: Procedure is taking too much time to execute

    Since you are going to be coding in the piece to remove the temporary qdf's you might try doing a MAKE TABLE instead of a query, then looping through that. I cannot say for certain it will speed it up tremendously, but it is worth a shot.

    You might also break up the actual recordset into two sets using the H and O criteria, so you would only be working with a subset of data.

    Re: Access VBA - XML Limitiations(?)

    I doubt you would have much control over a built in Schema, could be wrong though. You could set up a procedure to create the XML output yourself that would give you a little more flexibility.

    Re: Link Table Suddenly Stopped Allowing New Records

    Welcome to OZ!

    There might be a few causes but we really need more informaiton. Are you getting any error messages when you try to add a new record? Have you checked for any constraints that you may have placed in the database that is not allowing a new record?

    Re: SQLOLEDB - Connecting to SQL Server, doesnt return AbsolutePosition

    It has been my experience that when that occurs it is usually a cursor and/or lock type issue as carlmack has mentioned.

    In addition to setting the client side cursor, if there is not an updating that is going to occur in your procedure I would probably use a forward only cursor with a read only lock. If nothing else, setting it to forward only and read only should at least let you know if it is returning accurate info.

    Re: bug - macro crashes when run, doesn't crash if save first

    Collections are useful if you are needing to reference and add/delete data that will need to be referenced in other places, otherwise an array will probably suit your needs and are faster.

    It looks like you are using your collection object to add items to a either a combo or list box and I see where you remove a few items from your collection. In my opinion an array would probably work fine. This is not a rule or guideline, just one approach to it.

    Re: bug - macro crashes when run, doesn't crash if save first

    As Jack has mentioned if you are able to post the workbook that would be helpful as a first step.

    A few other items of note is that if you can break down your procedure into smaller more manageable pieces that might help in detecting the problem. Also making use of the WITH...END WITH statement could help in the readability.

    Another is that collection objects typically run slower, so it could be bogging down the machine...I must precede that statement with I have only scanned your posted code and at the moment do not see the reason behind needing a collection, it very well may be needed in other places. Not trying to pick, just trying to make you aware of memory resources..when using the DIM keyword you still need to use a data type declaration unless you are implicitly declaring your variable as a Variant data type.

    1. Dim i, j As Integer 'i is a variant
    2. 'Dim i As Integer, j As Integer # both variables are integers
    3. Dim t1, t2 As Integer 't1 is a variant
    4. 'Dim t1 As Integer, t2 As Integer

    Again, not trying to pick, just pointing out things that I was not always aware of until someone did the same for me.

    While this may not be the case, I have run into problems when trying to set control properties that are not visible or enabled at run-time prior to showing a form, again may not be the case, but it does help eliminate causes.

    Re: Writing SQL

    I agree with Will, I do not see where you identify your Division field, however, this is what I come up with when I put a db together with just sales and a division field

    This is what is in my table

    Division Sales
    1 $100.00
    2 $200.00
    3 $50.00
    4 $300.00
    5 $25.00
    6 $400.00
    7 $500.00
    8 $600.00
    9 $700.00
    5 $30.00
    3 $25.00

    1. SELECT TOP 4 Avg(Sales), Division
    2. FROM Test
    3. GROUP BY Division

    and this is the resulting query

    Average_Sales Division
    $27.50 5
    $37.50 3
    $100.00 1
    $200.00 2

    Re: Incorrect syntax near the keyword 'GROUP'

    Do not include your aggregrate functions in your grouping clause. You just need to have the columns that are not being aggregrated in your GROUP BY clause.

    1. sqlstring1 = "SELECT product,deliverymonth,tradebook,tsperiod,anpregion,execution,marketarea," & _
    2. "sum(VolTdy) AS SumOfVolTdy,sum(MtMTdy) AS SumOfMtMTdy " & _
    3. " FROM qry_PwrPos WHERE deliverymonth > '02/01/2006'" & _
    4. " HAVING product='Power' and anpregion='ERCOT' and marketarea='ERCOT Sellers Choice' and tradebook='Asset' " & _
    5. " GROUP BY product,deliverymonth,tradebook,tsperiod,anpregion,execution,marketarea"

    Re: Copy Tables from one DB to another DB through Code


    It sounds like a better solution would be to only save the unique records on the front end. I do not know how the data is getting in, but when the user enters/saves data for an entry, you could simply do a check on the ID and WeekCommencing fields, if they already exist do not add the new rows, if they do not, add the new row.

    Of course, I do not know if the other data is needed for historical data or not.

    Re: Copy Tables from one DB to another DB through Code


    No need for the recordset if you are using the DELETE statement.

    Just use the cn Object to execute the statement