Recent content by JVFriederick

Skip Your contributions have been outstanding over the years. Thank you very much for what you have done for me, and countless others. JVFriederick

I gave a star because the explanation why SUMPRODUCT function is advantageous is very good, I have not used it frequently in the past, but now plan to change. Particularly the part about > < operators within the formula, something that is otherwise very difficult to do. Thanks Skip!

A formula only technique would be: Assume your 3 values are in A2,A3, A4 In cell B2, enter the following formula =B1&"'"&A2&"', " Copy this formula down for all desired records, don't forget to remove final delimiter on last row. You may experience problems trying to concatenate 5000 cells...

KCUSHING Previously I did not notice you were pulling the date parameters from a query result. This may be cause of troubles. To troubleshoot, I would suggest manually typing each date in cells O2 and P2 and testing your query again. As mentioned in my previous remarks, Excel is problematic...

KCUSHING Just a hunch here, but I noticed the database fields are datetime, which are often problematic when pulling dates from XL sheet. Would suggest you try parameters as (DATETIME > ?-1) AND (DATETIME < ?+1) to get past XL and database compatibility issues. I would not use the BETWEEN...

Caz Did you test sign the PDF yourself and save, then open and view in both Kindle and desktop? Would check the default settings for viewing signatures on desktop. In Adobe Reader (Edit Preferences Signatures) there are quite a few settings to check...... JVF

Salut39 If you want something faster, please post before and after SQL statements, and before and after connection strings. Your original request stated the server location changed, which I incorrectly assumed meant just the IP address. Obviously more than just one thing changed, which...

Salut39 There is a space after the = sign in your new connection string, would check that first. .....DATABASE= dstores;PORT=3306;......... Would then suggest manually creating new connection on another sheet and comparing the strings. Since you need changes to DSN, IP address, and DATABASE...

Salut39 If you have a few connections (<10) the answer by Skip is the way to go. If you have tons, or the data source changes frequently, then some VB code can be handy. The following example uses the "connection description" to store a search / replace string used to make changes to ALL...

Gone Johnson.......

Just when I thought I knew a little bit, Skip teaches me more! The created table is also automatically named (which can be changed to suit), the big payoff for me is that formulas will propagate as data is added (similar to db query object). Thanks for taking the time to mention this. JVF

albop Assuming the range begins in cell A1, the formula in cell D2 would be =IF(A2<>A1,1,1+D1) Also attached example file http://files.engineering.com/getfile.aspx?folder=72cf05f0-2759-4a98-a9cd-8d3f539bfc71&file=TransactionFormula.xlsx

Just a hunch / suggestion...... Why not add single row with formula SUMPRODUCT((YEARSOLD=$B$2)*(STORE="RED")*(SALETYPE="RETAIL")*1)-SUM(B4:B36) That would calculate the number not shown in the fixed list. JVF

Yes, that is the takeaway. If needed, I will use the Round function to 2 decimal places to remove annoying parenthesis. However, this should only be done on final values (not used elsewhere). Otherwise you will introduce rounding errors. JVF

The important part of the kb article..... Another confusing problem that affects the storage of floating point numbers in binary format is that some numbers that are finite, non-repeating numbers in decimal base 10, are infinite, repeating numbers in binary. The most common example of this is...