Search results

  1. B

    Update current record with next record value

    Hi all, I want to identify the time frame of a agent's job. For example Agent 081 had a task aaa from 202012-202104, then it switched to task vvv from 202104 until now 202102. I found this code: data want; recno=_n_+1; set Task_times end=last; if not last then set...
  2. B

    Solved Get first observation of dataset

    Hello all, how can I get the first appearance of this dataset via SQL? 202012 0821 Ein 202106 0821 Mehr Thank you in advance. Kind regards, Ben
  3. B

    Solved Method 'Cells' of object '_Global' failed in Excel session opened via Access

    Dear all, from time to time this code doesn't run giving the error message: Method 'Cells' of object '_Global' failed It opens both Excel tables, but it fails to insert a column. Every help is much appreciated. Thank you. Regards, Ben Public Sub Format_BD_Excel() 'Get all Excel files in...
  4. B

    Recordset Operation

    Hi all, I got following issue: There are two tables called Result and Duplicates. Now I want to mark all duplicated in the Result table. Here the content: RESULT TABLE (my goal): Booking Costs Period Dupl edited BC 2089 22018 BC 2089 22018 x AZ...
  5. B

    Run Access VBA code from .bat file

    Hi guys, can someone help me out? Following issue: Wanna open all Access databases under one specific folder and run the same VBA code. The code is ready, the only thing for me is to find out how to start Access from a bat file and run the same code for all databases. The background is that...
  6. B

    Replace a string in vba code

    Hi all, I got following issue: Want to replace a string in my VBA code. Tried this one, however it doesn't replace the string: Public Sub ttt() Dim vbComp As VBComponent Dim mdl As CodeModule Dim i As Integer Dim lin As Variant For Each vbComp In...
  7. B

    Subqueries in Access

    Hello, I try to execute following subquery: DoCmd.RunSQL "SELECT DISTINCT Q_PRICES.Material, Q_PRICES.Product_Class INTO TEST " & _ "FROM Q_PRICES " & _ "WHERE " & _ "(SELECT Q_PRICES.Material " & _ "FROM Q_PRICES " & _ "WHERE...
  8. B

    Pop up form in Access 2013

    Dear all, I got a curious issue here: Set up a pop up form under Options\Current Database\Display Form TEST For some days it doesn't show anymore this TEST form when I open the database. Is there anything I have to change? Do I have to delete this TEST form and create a new one? Thanks...
  9. B

    Mutliplication with str function

    Hi all, I got the following problem. This code doesn't calculate the exact value: Sub TEST() Dim strSQL As String Dim b As Double DoCmd.SetWarnings False b = DLookup("MC", "COMMON_DISTRIBUTION", "YEAR = 2015") strSQL = "Update TNS_HIST Set [TNS_in_TRY] = " & _ "...
  10. B

    Creat an EXE or BATCH file

    Hi all, is there a way to create some sort of an EXE or BATCH file in any Windows Explorer folder, that executes me a specific Excel Macro? My problem is that I got several files split up by Departments, updated every day. So far I have to open each file and update the format. It would make...
  11. B

    Combined Export

    Hi all, I'm getting desperate here. I want to export files these files BP_SUMMARY,BP_fix,BP_var, QT_SUMMARY,QT_fix,QT_var, TEF_SUMMARY,TEF_fix,TEF_var. At first I want to select an export location and then I want to create files like REPORT_BP,REPORT_QT, REPORT_TEF REPORT_BP shall include...
  12. B

    Conditional formatting

    Hi all, how can I add a conditional formatting in ACCESS VBA? Let's say I got a table TEST with a column x. Is it possible to say color line green if x < 5 , color line red 6<x<10 and color line yellow if x > 11? Thanks in advance. Regards, Ben
  13. B

    Change Format in Access VBA

    Hi all, I got a simple problem here. How can I change a number column in a three digit percent one? Tried the following, however it just deletes the content of the column: Public Sub TEST() Dim a11 As Double Dim year As Integer DoCmd.SetWarnings False DoCmd.RunSQL "UPDATE DEPT_FILES SET...
  14. B

    Replace Blank fields with 0

    Hi all, I try to go throgh all columns and replace all blank fields with 0. Somehow this doesn't work: Sub TEST() Dim str As String Dim rs As DAO.Recordset Dim fld As DAO.Field str = "Select * from Quantity_022014" Set rs = CurrentDb.OpenRecordset(str) For Each fld In rs.Fields On...
  15. B

    How to sum up records in Access vba

    Hi all, I want to sum up specific records in a table called TNS. All records with Division = Common should be summed up to the records with Division = AK. TNS contaıns columns Division, 012013,022013...,122013 I tried following without success: Public Sub TEST22() Dim strSQL As String...
  16. B

    DLookup syntax

    Hi all, can somebody please help me? Something is wrong with this dlookup in : strSQL = "Update TNS Set TNS_in_TRY = " & _ " Nz([TNS_in_TRY],0) + " & Str(a) & " * Dlookup([TNS_in_TRY],TNS,Division = 'COMMON' AND Product_Class = 'Scrap_Sales' AND Reporting_Month = '" & t & "') " &...
  17. B

    Map columns dynamically

    Hi all, got the following issue: There are two tables A und B. Table B contains columns like 1FC, 2FC,...., 12 FC standing for forecast sale quantities. Now I want the user to decide how many FC columns he wants, always starting with 1FC. Let's say he wants three FC columns. Now I want to...
  18. B

    Extract variable count of columns

    Hi all, I wanna extract a variable count of columns and transpose it to another table. My source table is called FC containing columns like 1 FC, 2 FC .., 12 FC My target table is called Forecast_Quantities Tried the following: Public Sub TRANSPOSE() Dim rs As DAO.Recordset Dim rsNew...
  19. B

    Table structure change in VBA

    Hi all, I get a table with the following structure: Customer 1st Month 2nd Month 3rd Month ABC 55 63 21 Each month is showing the quantities. The count of columns can be variable for each time. How can I turn this structure into : Months...
  20. B

    How to sum up special column

    Hi all, is it possible to tell VBA to sum up all columns with the column name containing a special string. For example I want to sum up all columns containg 2013 as a column name. Thanks in advance. Regards, Ben
Top Bottom