Recent content by DALeffler

  1. D

    Advanced MID Formula

    Try: MID([PRODUCT DESCRIPTION],INSTR(TRIM([PRODUCT DESCRIPTION])," ")+1,INSTRREV(TRIM([PRODUCT DESCRIPTION])," ")-(INSTR(TRIM([PRODUCT DESCRIPTION])," ")+1)) The number you need is the last space position in the string minus (the first space position + 1). hth,
  2. D

    COUNT Records in a Field

    Try this expression in a blank query field that has your table/query as the record source: MyCount: IIf(IsNull([CUST REF]),0,Len([CUST REF])-Len(Replace([CUST REF],",",""))+1)
  3. D

    Using an Or Statement in a Variable for Query

    It's because Access will not parse a text variable, outside of a Set statement, no matter the syntax. If a variable is assigned a string - MyVar = "2 or 3" - and a query is asked to return records where a field is equal to that variable, the query will return records where the field is equal to...
  4. D

    Trouble bringing in a calculation from main report to use in subreport

    What is the following controls' name? Then your subreports control source would be: =Reports!rptFinishedProductCostWithContainers!qryFinishedProductContainers.Report!MyMainReportControlName+ [TotalUnitCost] provided TotalUnitCost is is exposed by the query for the subreport. (and I have no...
  5. D

    Middle value of 3 columns, excluding zeroes

    Your logic doesn't seem to be consistant. Which doesn't seem to follow, as far as I can see, with this: Is what you really were trying for this? If Column 1 is zero and Column 2 and 3 are not zero, give me min of 2 and 3 etc... If you pass the values of the 3 columns to a VBA function when...
  6. D

    Help With Array!!

    Your code is declaring a 3 dimensional array: ReDim myArrVar(0, 0, 0) but then only references the first dimension in the assign statements: myArrVar(intI) = IndexDate myArrVar(intJ) = IndexName myArrVar(jintK) = AvgDiff when you need to always reference all three dimensions whenever your...
  7. D

    Expression Too Complex - Passing 29 Parameters, is that too much

    Please attach a sample demo db for the rest of us, if you can... :) I was able to pare your posted function down to this: Function ThirdQtrEnerMth13to24(dThisMonth As Date, ParamArray lngKW() As Variant) As Long Dim I As Integer 'lngKW() is zero based... I = Month(dThisMonth) - 1 'function...
  8. D

    Complex Report Sorting

    You're very welcome - glad to help (Yippee!!!:)) The data really needs to be normalized. Most of the coding hoops we're jumping through wouldn't be necessary except for the normalization breaks. Good luck & thanks for the clear questions.
  9. D

    Replace funcion

    You can also nest 'em: x = "A.A'A^A+" ?replace(replace(replace(replace(x,".",""),"'",""),"^",""),"+","") AAAA hth,
  10. D

    Complex Report Sorting

    First, the Union query has to include the "permitsout" field in the output. Then in the not visible textbox in the detail section of the report, try the following as the control source: =Iif(RigNum = 1, Iif(IsNull([permitsout]) = True, 0 ,1),0) If that don't get it, I'll need to see a...
  11. D

    Complex Report Sorting

    Have you tried "=IIf(IsNull([RigName1]),0,Iif(IsNull([permitsout)],0,1))" for the control source for the permitsoutcount?
  12. D

    Complex Report Sorting

    You can use the same method to look at other fields. If you had a field for "permitsout", try this: Create a text box in the detail section of the report, name it "PermitsOutCount". Set the control source to "=IIf(IsNull([permitsout]),0,1)" Set the Visible property to "No". Set the Running...
  13. D

    Complex Report Sorting

    Create a text box in the detail section of the report, name it "RecordCount". Set the control source to "=IIf([rignum]=1,1,0)" Set the Visible property to "No". Set the Running Sum property to "Over All". Creat another text box in the footer of the report. Set the control source of this...
  14. D

    Complex Report Sorting

    To get the query to ask for parameters, I modified the test data table ("WellRigs") to this: ID WellName RigName1 RigSeq1 RigName2 RigSeq2 PrgmYr District 1 XYZ Myrig 1 AnotherRig 2 1984 a 2 SMITH Myrig 3 HisRig 2 1984 b 3 French Myrig 2 1984 a 4 Green HisRig 1 MyRig 1 1984 b 5...
  15. D

    Complex Report Sorting

    I agree completely that the data needs to be normalized. But in an effort to keep the current db you have functional until the data can be normalized, you might be interested in the following. I copied the data from the word table into an Access table called "WellRigs" like so: ID WellName...
Top Bottom