Search results

Thanks Arnel. I had tried various options with 'CDate', but never thought of simply formatting the date - and this worked first time!

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

Update! Although Access crashed when I clicked on 'No' , when I opened the new database up again all the tables are there - so the data is accessible! I just need to play around with this a bit more and see if I can get it working a bit more smoothly, and hopefully this will let us do what we...

Hi HiTechCoach, This looks like exactly what I want, but I can't quite get it to work. The original database is password protected. When I use the utility to convert the database, it prompts me for the password, then creates a new database. So far, so good. But when I try to open the new...

Unfortunately the client is a global multi-national. The file in question is produced from one of their billing systems, which serves many 10s of millions of customers. I do not know why it is produced in Access format, as the system in question must run on mainframes, and the application must...

That's not true Ranman. The latest Access apps will NOT open Access 97 files. See the link in the post above, or just google it - plenty of people with the same problem. Cheers, StepOne

Thanks, I don't want to convert - just open. If I try to open this file using Access 2016 I get a message "Cannot open a database created with a previous version of your application." Can you explain how I can open it? Thanks, StepOne

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

I had not noticed this, and just tried it myself - all the options are there as before. Don't know what the issue is, but have you checked that your file is an accdb, not an mdb. Shouldn't make any difference anyway, but I wonder whether it is a compatibility issue.

Hi Ranman, So, first I append a record to the Header table, with some known details - e.g. Invoice Date, Company Name, Company ID. This will create an Autonumber reference. I then write a query to append to the Details table, where the Foreign Key uses a DLOOKUP formula to get its value from...

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

What happens if you comment out the 'set olApp = Nothing' ?

Hi jdraw, Thanks, your solution will work, although I was trying to do it without creating a table as a middle step. Would it not be possible just to use a SELECT query as the middle step and run your second query on the first ? I will give that a go, although I assume for some reason it...

Hi llkhoutx, You can't query a query to get the top 10 per handset. Unless you can show me a way of doing this ? Thanks, StepOne

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

I think you need to put 'End With' after the end of your 'With' statements.

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

Can you give the entire code from the 'SELECT.... ' statement to the 'END SELECT' ? Basically you will need to add a fourth option 'Bonus' which sets Temp_Val to 3. You'll then need to add another IF statement below what you already have ; If Temp_Val = 3 Then Hold_Sum = Hold_Sum + 2 (Proc =...

Thanks for the replies. Spike - the process definitely needs re-designed. It has grown from a single spread-sheet, over the last 15 years, through several different developers (none of which have documented or commented anything!). I estimate 12 months to design and develop a new system, and I...