Search results

Hi, I have a table with a date field, and a quantity field. I am trying to get a running total, so that I can calculate the date that a particular level is breached. I am using DSUM, but it doesn't work as it seems to convert the dates to US format. Here is the SQL; SELECT...

Hi, We regularly receive Access '97 format mdb files from one of our customers. We have to process the data in these files, and to do this we open them on an old pc, running Access 2003, convert them to 2003, and then open them in Access 2016. We want to retire this old pc but we are unable...

Hi, I have invoice data held in 2 tables called 'tblInvoiceHeader' and 'tblInvoiceDetails'. tblInvoiceHeader will have one line per invoice, with multiple lines in tblInvoiceDetails. I now need to transfer the data from those tables into 2 existing tables in our current application for...

Hi All, I have been stumped by this top 10 query. I have a table of phone calls. The critical fields are ; Handset (the mobile number being used to make the call) DialledNumber TotalSeconds (number of seconds the call lasted). I want to write a query which will give me, for each handset, the...

Hi, I have used a custom function (ConcatRelated - from the Allen Browne website I think) to concatenate text fields in a Group By query and it works great ! Now I need to extract that query from Access and paste into Excel. So, I am doing this from EXCEL vba. First I setup my SQL string and...

Hi, I am running a Group By query and want to get one of the text fields to concatenate (rather than pick First or Last). So, for example, if the data looked like this ; Project Job JobType Cost 1 1 Red 45 2 1 Red...

Hi all, This is a follow up to my post from last September. We have an Access process which we run every day (several times) but for some large data sets we get the above error - System Resource Exceeded. The process is split into 3 main subroutines - each of which calls a large number of...

Hi folks, I have written a VBA procedure (in Excel VBA) to open an access database using the DAO opendatabase method, and then loop through a list of SQL queries, using .copyfromrecordset to copy the query results onto separate tabs in the Excel workbook. This works great on my pc, and, to...

Hi, I have some VBA code sitting in Excel, which opens an Access application, and calls a VBA procedure in the Access database. Code as follows ; Dim acApp As Object Set acApp = CreateObject("Access.Application") acApp.OpenCurrentDatabase (m_strDBCopyPath & m_strDBCopyName) acApp.Visible =...

Hi, I have a Report Template spread-sheet which uses some Excel VBA code to change the data range of a chart, and then copy the worksheet containing the chart to a new, blank, workbook. The code which updates the data range is as follows ; ActiveChart.SeriesCollection(1).Formula = _...

Hi, I might become a regular poster on here as I have a new job which is supporting a miscellany of Access and Excel applications, something I have not done for a few years now.... One thing I have inherited is an Access database which we use on a daily basis to run reports for customers...

Hello, I have a query which outputs the value of a txtbox on my form. So in the query I have a field which looks like this ; Approver: [forms]![frmMain]![txtApprover] This works fine when I run the query, but when I use docmd.outputto to export it to Excel, the Approver column is blank. I...

Hello, I have a form displaying a list of people, with selection criteria in the form header which lets the user search for records. The underlying recordset is based on a Group By query and therefore cannot be updated. The trouble is that I want the user to be able to somehow select a...

Hello, I have continuous list form with a list of staff details to allow people to choose a staff member to edit. I want the user to be able to click anywhere on a staff record to open up the frmStaffEdit form. Originally I had a button in the details section to open frmStaffEdit so it...

Hi folks, I have searched, but not found an answer to this question. I have a form with no 'Details' section - just half a dozen fields in the form footer all of which are SUMs of the fields in the underlying query. The trouble is that the fields appear blank until I either click on one of...

Hi all, I see several examples of Median functions on the forum - but they all calculate the median of an entire dataset. I'm looking for a version that would allow me to run a subtotal query with median values. To be specific, I've got a table with Job Title, Grade and salary. I want to group...

Hi there, I'm about to embark on writing the front end to a data warehouse which will consist mainly of reports being run where the user has set the criteria dynamically. As far as I can see there are at least 3 ways to do this ; 1. Use criteria in the reports query which look up field values...

Hello, I have a question about Access runtime. We have some users at work who don't have Access on their machines, but we would like them to be able to use databases we have created. My suggestion was to purchase Visual Studio Tools for Office, which will give us Access Developer Extensions and...

Hello all, I have a table with 4 required fields, and a form to add new entries to the table. While testing, if I don't enter a value in one of the required fields, and press the standard 'X' close button at the top right of the form, I get an error message "The field xxxxx.xxxxx cannot contain...

Hi I have two tables - one which holds call data for telephone calls, the other of which is a lookup with dialling code information. I want to link the first 5 digits of the dialled number with the area code in the lookup table. Fine so far - but the resulting recordset is not updateable. Is...