Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
-
Need to Capture MIN CLAIM ID - SQL Server
This would be more for the sql server forum and I would imagine they will want a bit of detail about table structures etc Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in...- xlbo
- Post #2
- Forum: Microsoft SQL Server: Reporting Services
-
Excel SQL query not working with dates
Phv has it but please note that you should never use select * as if the table changes at all it could wreck whatever is relying on it. For the sake of a fee seconds to script out the field names, it is well worth it to abou future issues Rgds, Geoff We could learn a lot from crayons. Some are...- xlbo
- Post #4
- Forum: VBA Visual Basic for Applications (Microsoft)
-
How can I do a rolling 12 months calculation
F1 is your friend ;) Offset( Base position Rows down to move Columns across to move Range depth Range width ) Counta(f:f)-13 is the start cell in your Rolling 12 range 0 is the number of columns to move across 12 is the depth of the range 1 is the width of the range Rgds, Geoff We could learn...- xlbo
- Post #6
- Forum: Microsoft: Office
-
checking field length in CSV file
If you have access to a sql database, and you have a lot of rows to deal with, a quicker option would be to import from your semi colon delimited text file into a sql table which is set to be char rather than varchar values for each column. This will automatically pad the loaded data to the...- xlbo
- Post #30
- Forum: Microsoft: Office
-
Left Padding data
Cat .skin. Many ways! =rept("0",13-len(a1))&substitute(a1,".",".") Untested Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box. Please read FAQ222-2244 before...- xlbo
- Post #6
- Forum: Microsoft: Office
-
How can I do a rolling 12 months calculation
=sum(offset(f1,counta(f:f)-13,0,12,1)) Untested Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box. Please read FAQ222-2244 before you ask a question- xlbo
- Post #4
- Forum: Microsoft: Office
-
Vlookup not working #REF error
Also - unless you intentionally want to change the lookup range, use $ to make the range relative rather than absolute source!$A$2:$C$10470 Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but...- xlbo
- Post #4
- Forum: Microsoft: Office
-
VBA conditional Formatting Excel 2003
Just as an fyi - this is probably the best thread discussin conditional formatting in VBA (>3 conditions): thread68-223068 Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in...- xlbo
- Post #5
- Forum: Microsoft: Office
-
ERROR [07002] [Microsoft][ODBC Excel Driver] Too few parameters. Expected #
SQLScholar - It's an excel driver thing... ebell1 - what happens if you try: SELECT [Task_Type], [Status_Change_Date],[SourcingEvent] from [ContractsWorkflowReport$] Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and...- xlbo
- Post #3
- Forum: Microsoft SQL Server: Reporting Services
-
import excel sheet1 to access table
Loading to an access table using Microsoft OLE DB Provider For Sqlserver ??? Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box. Please read FAQ222-2244 before...- xlbo
- Post #2
- Forum: Microsoft SQL Server: Integration Services (SSIS)
-
What can anyone tell me about InfoPath?
Not used it in a fair while (4 or 5 years) but the version I used was horrible. It's not intuitive and difficult to configure. If you are looking to go down the route of eForms I would suggest just using ASP.NET instead Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are...- xlbo
- Post #2
- Forum: Microsoft: Office
-
Get rid of NaN for percentage calculation
You need to filter out the morethan1day values where TN = 0 on both sides: =sum(IIF(Count(Fields!TN.Value)= 0, 0,1-(Sum(IIF(Count(Fields!TN.Value)= 0, 0,Fields!MoreThan1Day.Value)))/Count(Fields!TN.Value))) Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and...- xlbo
- Post #11
- Forum: Microsoft SQL Server: Reporting Services
-
Access 2010 Iif Query
Not sure if the syntax would be different for Access (probably) but this is how I would write it in SQL: UPDATE SampleCombined SET [Not on Product Look-Up] = 'Yes' FROM SampleCombined a INNER JOIN (SELECT UniqueRef FROM SampleCombined WHERE excl_prod != 'Yes' and no_lookup = 'Yes') b on...- xlbo
- Post #3
- Forum: Microsoft: Office
-
Excel 2010 using ISNA in and IIF to do something
another thing to note is that if you have a large data set, you may be better off testing for NOT(ISNA) Due to the way excel calculates, you are better off setting the true part of the IF formula to the most common occurence If you expect more NAs than matched values then leave as is. If you...- xlbo
- Post #6
- Forum: Microsoft: Office
-
Excel 2010 using ISNA in and IIF to do something
just FYI, you do not need the =TRUE part =IF(ISNA(VLOOKUP(B80, Base!D:AZ,44,FALSE))=TRUE,PERSONAL.XLSB!FindName(B80,"BASE"),(VLOOKUP(B80, Base!D:AZ,44,FALSE))) can become =IF(ISNA(VLOOKUP(B80, Base!D:AZ,44,FALSE)),PERSONAL.XLSB!FindName(B80,"BASE"),(VLOOKUP(B80, Base!D:AZ,44,FALSE))) as...- xlbo
- Post #5
- Forum: Microsoft: Office
-
Import/export data from database to another
Please rephrase - that doesn't make sense Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box. Please read FAQ222-2244 before you ask a question- xlbo
- Post #14
- Forum: Microsoft SQL Server: Integration Services (SSIS)
-
OT: I could be wrong...
ditto all above - hope you managed to have a medicinal snifter of something nice :) Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box. Please read FAQ222-2244...- xlbo
- Post #7
- Forum: Microsoft: Office
-
Collection Lists & Summaries
Hi Jonsi - to remove the need for overstating formula ranges, have a look at dynamic named ranges Lets say your pivot table is on Sheet2 and starts with a page field in A3 Let us also say you want to sum columns B, C & D you can create 3 dynamic names: dr_ColB...- xlbo
- Post #5
- Forum: Microsoft: Office
-
Excel time in milliseconds
very close: MsgBox Format(Now(), "dd/mm/yyyy hh:mm:ss:ms") Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box. Please read FAQ222-2244 before you ask a question- xlbo
- Post #2
- Forum: VBA Visual Basic for Applications (Microsoft)
-
Collection Lists & Summaries
Best bet is to use MS Query internally on the workbook. you can set it to work to pick up a criteria to apply to teh data range e.g. weeknum = 5. that way you keep your sheet1 data in tact and only grab what you need onto sheet2 for reporting Rgds, Geoff We could learn a lot from crayons. Some...- xlbo
- Post #2
- Forum: Microsoft: Office
