Search results

Hi all, somehow I can't calculate the months between 20211231 and 20230228. %let End = %sysfunc(intnx(month,%sysfunc(today()),-4,e),YYMMDDN8.); %let Begin = %sysfunc(intnx(year, %sysfunc(date()), -2, e),YYMMDDN8.); %put &Begin; %put &End; %let count_months...

Hi all, I was trying to select a reporting month column from table temp_trans, it looks like: GPNr 202112 202201 202202 .... 202208 x 1 5 2 ... 3 y 0.4 2 3 8 z 3 1 5 ... 6...

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...

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

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...

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...

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...

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...

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...

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...

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] = " & _ "...

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...

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...

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

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...

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...

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...

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 & "') " &...

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...

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...