Search results

Dear All, I'm having wiered issue with one of my SQL. Below SQL I'm executing from VBA, which is throwing error "Run-time error 3061 - Too few parameters. Expected 1" SELECT tbl_Bankers_Mapping.[GMIS Revenue Producer], SUM([Qtr Annuity Income]) - SUM(NZ([Qtr Annuity Oneoff],0))/3 AS...

Dear All, I'm trying to set the size of the form in Access 2007 using VBA using below code but this is not working. This was working in Access 2003. On Form_Load() I'm calling method PositionForm DoCmd.MoveSize 100, 100, 600, 600 This is not working not sure why. I have set Auto...

Dear All, I'm creating new workbook and worksheet through ACCESS VBA (MS Access 2007). After creating new worksheet I'm extracting data from ACCESS and writing into the sheet. After that I'm creating new sheet and trying to transfer ACCESS query resultset to new created sheet. I'm stuck over...

Dear All, I'm transfering data from one Access Database to another database using TransferDatabase method using below code: Set oDB = Application.DBEngine.OpenDatabase(strNNASourceDatabasePathNName, False, False) Set objApp = New Access.Application objApp.OpenCurrentDatabase...

Hi All, How to extract a portion on based of search criteria from a Column string using SELECT statement. I have a table with below structure: Table A Structure ============= Name Description Transaction Value Name: James Description: Redemption: MMTIME AUD, 3.295%, 25.07.12-01.08.12...

Hi I'm trying to apply conditonal formatting with TrafficLights using below access VBA but getting error "object doesn't support this property or method". I'm new to Access VBA please let me know what I'm doing wrong. Sub setRAGStatusFontColorIcon(strRange As String) Dim objISet As...

Hi, My query is not returning correct result of outer join I tried to write query in different way but no success. Please find below my query: SELECT ProdCat.ProductName, Sum(tbl_Consol_Weekly_Product.RevenueInUSD) AS Revenue FROM tbl_Consol_Weekly_Product, tbl_Bankers_Mapping, (SELECT...

I'm importing data from Excel to Access using Access VBA which works fine that sheet name is first sheet in workbook. However, if multiple sheets exists in the workbook and that particular sheet can be 2 sheet, 3 sheet or any sheet. In that case how to search particular sheet name and import in...

Dear All, I have created Access database as a frontend application and another database as a Backend Database (all the databases are password protected). All tables and queries are stored in Backend Database. I have created Excel Sheet and writing data in this excel file and performing the...

Dear All, I have created Access database as a frontend application and another database as a Backend Database (all the databases are password protected). All tables and queries are stored in Backend Database. My problem is that I need to create a form (Combo filters) with sub-form (List of...

Hi All, I have fronend and backend database. I have all data stored in another database (backend) and that database is password protected. I need to export some selected data into Excel which is created in the same process. Below is the code: Private Sub ExportLeaversList(strWorkbook As...

Hi I'm creating Excel File Dashboard from MS Access VBA and trying to setup the page size to A3 using below code: Sub SetUpPage(ws As Worksheet, Heading As String, Orient As String) On Error GoTo ErrHand Dim myOrient As String ws.Application.ScreenUpdating = False With ws...

Hi All, I have table with following Columns: Banker Status Week1 Week2 Week3 Week4 ...... Week48 Week49 Week50 Budget YearMonth I'm need to extract the value only for few columns dynamically from result set. I have written following code: i = 0 strColumnsSQL = "Week" &...

Hi I want to change the Font Colour on based of some condition e.g. If number is 100% and above then change numbers colour to Green If it's above 95% and Less than 100% then Amber If it's less than 95% then Red And this need to be changed for a range e.g. D1:D20 I'm not sure how to apply...

Hi I want insert the result of below query in one table, any idea how to achieve this? TRANSFORM Last(Nz(RevenueInUSD,0)) AS WKRevenueInUSD SELECT [Banker] FROM tbl_TB INNER JOIN tbl_Weekly_Calendar ON tbl_TB.ValBookDate=tbl_Weekly_Calendar.ReportingDate WHERE [BU]='Asia' And...

Hi All, I am writing data from Access to excel. There are few records with 0 values. How to remove those records? Below the code used to export data from Access to Excel. Dim TempString As String Set objXLApp = CreateObject("Excel.Application") wb = "F:\MyDoc\Weekly MI Automation\" &...

Hi How to get MAX YearMonth Value using SQL query in MS Access 2007. Pls note YearMonth is stored as Text I'm executing below SQL and it's returning all the records from the table Select MAX([YearMonth]) from tbl_Revenue_Assets_Split GROUP BY [YearMonth] Output is coming: YearMonth...

Hi, I have error message on every event I have in a single form. I get below error when I move the mouse (Did not click anywhere). The error message is: The expression On Mouse Move (in this case) you entered as the event property setting produced the following error: statement invalid...

Hi I need to calculate SUM of selected cell range e.g. PB YU Bud PBud Actual TC Sam 120 130 190 130 150 James 130 150 110 120 130 Raj 150 120 120 100 120 ================================= Total 400...

Hi All Is there any way to show list data in tabular format using SQL query in MS Access? Private BankerYearMonthVolatile IncomeJames2012013244James2012023467James2012034667James2012045668James2012052378Andrew201201345Paul2012013456Paul201202-234Paul2012030Paul2012040Paul2012053456 Show...