Search results

Thank you very much again @Pat Hartman . This is the result of putting all data (all years, which is 38 years) in just one array (each year has 18K rows of data) and copying the data to an excelsheet. This is the code I used to load the data to an array. This goes relatively fast. 'load all...

Thank you very much @Pat Hartman for all this documentation, very interesting! Arrays is a subject that I’m not that familiar with, that’s why I appreciate your support so much. I’m also convinced I need a two dimensional array – for each year 1 array (as you stated in your post #8) The code...

Thank you @Pat Hartman for your advice I'm very interested in the idea of loading all values in arrays. If I'm understanding it correctly, one array contains the variables (year, varA, varB, varC, varD) for one specific year. I have to loop through the recordset (which contains all years) and...

Thank you for your reactions, Minty & Pat. The base query is as follow: queryTemp2 = "SELECT tabF.ref, " _ & "tabR.NL , tabR FR , tabR.niveau, " _ & "tabF.rub, " _ & "tabR.[rub NL], " _ & "tabR.[rub FR], " _ &...

Thank you Arnelgp and Minty for your reply. There is/was no problem of multiple instances of the Excel Object with the original code. To see the effect, I changed some code as suggested by Arnelgp, but there was no improvement. What did make a big difference was not copying the entire...

Hi, I have an Access program that basically creates Excel-files based on a query on an Access table. The program uses loops and does the following: - it queries each loop another year - it transfers each loop the results of that query in a temporary excelfile X (using DoCmd.TransferSpreadsheet...

Thank you Arnelgp. I've changed ; to , and indeed, it's working now. Where I live (Belgium), ";" is the default separator when Belgian regional settings are applied in Windows. That's why it worked on my previous desktop / laptop. On my new laptop, other regional settings (British) were...

Hi, I’m having an issue with my application since I use it on another laptop. It’s an application in Access that creates Excel-files (export data and do some formatting). The program works well with Access 2019 (desktop) and Office/Access 365 (all 64-bit) (old laptop). My new laptop has also...

Thank you, when I see it like this, it is a quiet simple solution.

Thank you CJ I've tried the code, but I think there something missing and I can't see what. I would expect less records than the orginal table, and it isn't the case. Also, I suppose I have to add a where clause somewhere with type = A and type = B or something like that to exclude other...

Hi, I have a table with following fields / data: Year ; district ; type ; value 2000 ; 00001 ; A ; 10000 2000 ; 00001 ; B ; 5000 2000 ; 00002 ; A ; 20000 2000 ; 00002 ; B ; 10000 2001 ; 00001 ; A ; 15000 2001 ; 00001 ; B ; 10000 2001 ; 00002 ; A ; 25000 2001 ; 00002 ; B ...

Not really, I would like to have an empty column with the correct datatype, which can be filled in later. I thought a one-step solution was possible, but I understand from The_Doc_Man it has to be done in 2 steps. Thank you all.

indeed, works well. just one more question: what if I want no initial value for this variable in the first place? I tried : SELECT *, CDbl(null) AS Test INTO table2 FROM table1 but that doesn't seem to work.

okay, it becomes clear, format() always returns text. Say I have the following SQL: strsqlb = "SELECT * INTO table2 FROM (select * from table1);" what do I have to change in this syntax if I want to add one extra column to table2 (let's call it test1) which datatype is a double?

Hi, I'm using this sql syntax to append data into a new table (+ some additional columns 'test1' and 'test2'): strsqlb = "SELECT *, format('25','currency') as test1, 0 as test2 INTO table2 FROM (select * from table1);" I would have expected that in field 'test1', the format of the data is...

Thank you for your suggestion. It didn't help. I found the issue: it has nothing to do with which MS Access version is installed or VBA, but with the Windows regional settings. Mine were set for English(Belgium), which caused the issue. Changing the format to English(United States) solved the...

really bizarre. I tried it again this morning but still the same behaviour: after pressing enter, computer says no : "The expression you entered contains invalid syntax. You may have entered an operand without an operator". The focus of the cursor is then on [fld1] or on "test1,test2" - which...

I found out that Access 2016 is a little quirky. When I input the line field3: SplitFile(3,[fld1],",") it doesn't work in Access 2016, but when doing it in Access 2010, no problem at all ! So arnelgp, I suppose you do not run Access 2016?

sorry arnelgp, i have to "unsovle" my issue. on your last attachment, I see you succesfully entered 3 columns in the query with the function, which works very fine. (that's when I marked the issue as solved). something very strange is happening now: when I delete one column, e.g. the last...

gracias arnelgp