Posts by ik2zok

    Re: How to Subtract columns in Access 2007


    In Access(Jet) you have not COLUMN as in Excel, but Fields in Tables.
    If your 2 Fields are both FieldType=DateTime you have to use DateDiff(...<F1 for Help>...), here you could chose if you want the difference in second or minute.
    If your 2 Fields are some different Type... pleas think about this and convert them to DateTime, or just for calculation...!

    Re: MS Access VBA to open the Get External Data Wizard and set the options


    TO allow File selection, you should use FileDialog Object.
    Add Office Reference library on your Project, and search on OnlineHelp for FileDialog, you will find some code...


    If you have to use RUNTIME, this could be a problem, if the End users have not Office installed..., in this case the only option will be API... but as last chance...

    Re: Alter order of imported fields without writing line by line.


    Not sure to undertand the meaning...
    If you need to change ORDER Sequence... is available "ORDER BY" SQL Statement...

    Code
    1. Dim strSQL As String
    2. strSQL="SELECT TOP 997 * FROM [CI_DATA] WHERE [Business_Area] ='" & ans & "'"
    3. If OrderByChoisCriteria... Then strSQL=strSQL & " ORDER BY " & FieldName
    4. Set rex = db.OpenRecordset(strSQL)


    If you need to switch Columns, so Fields Order position... you have to build a SQL Statement changing ordinal position...

    Code
    1. "SELECT TOP ... Field1, Field4, Field3 FROM...."

    Re: DLookup Issues - Textbox as Variable Criteria


    The syntax is correct... check for the [User Name] or [Username] because in the first post you used [username] as FieldName, but in the Function your reference was to [User Name].


    Just one more suggestion, don't use SPACE_CHAR in Field(Name)... or Control(Name)... you are obliged to use square bracket.

    Re: DLookup Issues - Textbox as Variable Criteria


    2 Errors and 1 Suggestion:


    1st Error
    On ControlSource Property the SINTAX are the same as in the QBE, similat to VBA but NOT EQUAL.
    In Your Case the [,] must be replaced with [;]


    2nd Error
    In the controlSource Property also the explicit Reference to the Object are the same as in the QBE, you can't refere to shortform [Me]


    The Final Syntax is

    Code
    1. =DLookup("[First Name]"; "tblUser"; "[User Name] = '" & [Text25] & "'")


    1st Suggestion
    Instead of use bad name as [text25] use the correct way to rename Object... [txtName] or [txtJob] .... where [txt] is abbreviation of Control TextBox and 2nd part [Name/Job] help you to recongnize the specification of this control...


    Hope thi help.


    alex

    Re: Add Values to Cascading Dropboxes


    Have you considered using the event NotInList...?
    Set theProperty "Only in list"(sorry this is from Italian translation...) in Data TAB Properties... to No(False), this will Fire the OnNotInList ComboBox Event when you digit a new code... here you cuold try to implement your Code to ADD the New Data...


    On the OnLineHelp you could find this Sample


    Just a suggestion... in your case, with RowSourceType="Table/Query"(not List Value as in Help Example)... to ADD a new Record, you could use a Action Query(Insert Into...) or open Recordset on RowSource and Add a NewData.
    If the RowSource is a Read/Write Source, so if SQL Statement is Writable, you coud also use Recordset Obbject of Combo...


    Finally you need to execute Requery Merthod on Combo...

    Just a code to Compact a MsAccess Database:

    Re: Automated process for copying data from Access to Excel on a regular basis


    So, sorry to all for Reply mistake...


    I Copy and paste my INFO in this new One.


    You have lot of mistakes in to your code...
    1° This is ACCDB not MDB, so the ADO Ole Provider must be ACE not JET..., correct with this.. VB:

    Code
    1. strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    2. "Data Source=" & strPath & strDB & ";"



    2 ° The way you use CopyFromRecordset settings... is quite strange, try with this modification to understand: VB:

    Code
    1. worksheet Worksheets("Raw").Range("A2").CopyFromRecordset adoRst


    Now it work...

    Hope this help...

    Re: Automated process for copying tabular data from Access to Excel on a regular basi


    You have lot of mistakes in to your code...
    1° This is ACCDB not MDB, so the ADO Ole Provider must be ACE not JET..., correct with this..

    Code
    1. strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    2. "Data Source=" & strPath & strDB & ";"


    2 ° The way you use CopyFromRecordset settings... is quite strange, try with this modification to understand:

    Code
    1. ' Post Data to worksheet
    2. Worksheets("Raw").Range("A2").CopyFromRecordset adoRst



    Hope this help...


    EDIT: ik2zok pm'ed regarding replies to moderated posts.

    Re: Duplicate Record in VBA


    This revision should be more efficient:

    Re: Interpolate missing values in MS Access


    If you change the Object Name, you must be sure to change it in all places...
    Didi you change also the Table Name in UDF as code shown below...?

    Code
    1. Set rs = DBEngine(0)(0).OpenRecordset("SELECT * FROM 1G WHERE GID=" & GID & " ORDER BY RP")

    Re: Interpolate missing values in MS Access


    I find this easy solution, probably is possibile also with only SQL statement... but this is one of many solutions...
    So you have a Source Table, [TT] for example... containing all Data as Excel Sheet.
    Build a Query in QBE with this SQL statement:

    SQL
    1. SELECT TT.GID,
    2. getInterpolateValue([GID],100) AS RP_100, _
    3. getInterpolateValue([GID],475) AS RP_475, _
    4. getInterpolateValue([GID],2500) AS RP_2500
    5. FROM TT
    6. GROUP BY TT.GID
    7. ORDER BY TT.GID;


    You can call this Query as you prefer...
    Now you must write the UDF User Defined Function in a standard Module[getInterpolateValue(....)]


    You will obtain

    200,1682333217263740,2215798260154370,297028149070928
    210,1662936235011730,2240557434003770,32735096153983
    220,1675128705565290,2247977284572010,329019955773444

    Re: Need Help with calculating cumulative sum in Access


    I think you can understand that with NO PK or Unique Field used as OrderBy is not possible obtain in a Query the runningTotal as your request, because you don't know where you are each Row.
    So ADD a Counter FIELD named [IDCounter] just to have a test, and try

    SQL
    1. SELECT IDCounter,ID,Prob, DSum("Prob","Tablename","ID=" & [ID] & "AND IDCounter<=" & [IDCounter]) AS RunningTotal
    2. FROM Tablename ORDER BY IDCounter, ID;


    This give you the same result of XLS Table sample you attached.

    Re: Need Help with calculating cumulative sum in Access


    Quote from B.Coleman;751586

    This one deals with dates. I am getting confused.


    There is almost 2 way to do Running TOTAL, this are two easy example that you can adapt
    I assume:
    T1 Main table
    ID is PK Field
    FieldValue is the Field we want RunningTotal



    • SubQuery

      SQL
      1. SELECT
      2. a1.ID,
      3. a1.FieldValue
      4. (SELECT Sum(a2.FieldValue) FROM T1 AS a2 WHERE a2.ID <= a1.ID ) AS RunningTotal
      5. FROM T1 AS a1;


    • Aggregation Function (DSUM)

      SQL
      1. SELECT ID, FieldValue, DSum("FieldValue","T1","ID<=" & [ID]) AS RunningTotal
      2. FROM T1;