Search results

That's what's in the current code above, which works, however I am trying to code by the ethos "the best way to handle errors is to avoid them" i.e. I know exactly where my error will be, so why trigger the error handler for ANY error if I can catch the specific error myself before it causes a...

I have created a Ribbon button to switch the "MaintainConnection" property of the (first) pivot table's OBDC on the active worksheet. This throws an error when the pivot table's source is local, I assume because the property doesn't exist. Currently I have the code set to exit when any error...

Thanks CJ. That's a nice little bit of code, however the reason for the ODBC as a direct PivotTable source is to avoid storing the mass of data in the Excel file. I bit the bullet and replaced all instances of "Nz(..." with "IIF(IsNull(..." as per your suggestion. It took some time since the...

Hello, I am trying to connect an Excel 2010 workbook to an Access 2010 database (2007 type) query to use as a PivotTable source, using Data > Connections > Add... . I can establish a connection to the db itself but I noticed that a few queries do not show up in the table/query selector wizard...

Ha, I knew I would get stick for using calculated fields. I guess I'm an Excel user through and through :P If there is no blaringly obvious fix I'm reluctant to try and find one, since I have a stable and working workaround. Plus the less I use calculated columns, the better, right? Update...

Good idea with using long wordy dates for unambiguity, I hadn't thought of that since I rarely use them in normal applications. However... Using CDbl() to convert my date, stored as VBA type Date, into a double is exactly what I was looking for. I hadn't come across those conversion functions...

It's the getting the date, stored as UK format, into the field that is the problem. I know I can do it by doing UK -> US -> insert -> UK but that seems unnecessary to me when I know all date/time is stored as a double. To me it's analogous to having an input box for £s, then having to convert it...

I have some VBA code that (essentially) takes a user input for a date, and uses that date in an update query SQL. However the problem I'm having is that my date is in local UK format, but when SQL runs it converts it to US format e.g. 5th Jan 2017 is inputted and stored as 05/01/2017 as Date...

Hi all, I have a working database that I built. Recently (on a copy) I added some more functionality to a table that stores vehicle model codes i.e. factory code that designates model, engine, gearbox, trim level etc. The long model code that is stored in the table is a concatenation of...

I solved this in the end by creating a function that opens the Excel file (invisibly) and tries to select each defined sheet. Each successful operation is recorded and then the results are checked at the end. Here is the function I built: Public Function WorksheetChecker(strFilePath As String...

This is probably the simplest and easiest option; it did come to mind but I had the thought of "not ANOTHER table!". Damn the unlimited number of tables that I can create... Makes sense I think, but still sounds relatively complicated for the function :/ Can this be done invisibly within...

So I have a feeling I'm probably over complicating this but... I have some code that imports a certain Worksheet from an Excel file into an Access table. This Excel file is generated from a preset report in another department, and they can potentially change the report preset so that the...

My clearing queries set all the records in the NewSC field to Null. It works accurately in my tests and I see no (theoretical) reason why it wouldn't work. Thoughts? and yes, performance is very fast, so much so that I had to put a MsgBox at the end to confirm that the actions had been...

Wow, thank you The_Doc_Man, that turned out to be exactly what I had in mind! I now have a very nicely laid out 'Xlate' table that contains the parameters for my re-categorisations (much better than the first one I made anyway, before I reached out for help). I replaced the WHERE (...) = ""...

Hello, I've been trying for a couple of weeks to solve an issue with the design of a new database in Access 2010. I'm new-ish to database design but have been an end-user of Access for many years (mostly A'97 though). Essentially I am duplicating a "database" that has been created, stored and...