Search results

  1. I

    Excel Offset + Match in Access?

    You are a magician! Your query works like a charm :D Thank you also for your comments, I am aware of issues with hard coding and need to address them as well. Funny enough the end goal is to make your query to become a moving stock cover :) I just need to figure out some other bits and pieces...
  2. I

    Excel Offset + Match in Access?

    Both files in a zip file now :) Cell A4 provides opening stock for current w/c (23/05/2016) = 15823 Columns C to M provide Forecast for each week Columns N to X provide Cumulative Forecast The Stock Cover calculation is fairly complicated, so I hope my below explanation makes sense, if not...
  3. I

    Excel Offset + Match in Access?

    Hi I attached the database and Excel file. Sadly I cannot use DLookup, I tried and failed :banghead: Below is Excel formula, which can be found in attached Excel file. As you can see it is not a simple one. I am trying to replicated this in Access...
  4. I

    Excel Offset + Match in Access?

    Hello!!! I got a fairly complicated Stock Cover calculation in Excel that I am trying to replicate in Access. Unfortunately this calculation in regular Excel uses a combination of Offset and Match to get right values from matrix tables. I was doing some reading online but so far I was...
  5. I

    code needed to check if value from one table can be found in another table

    Of course you are right. Schoolboy error. Thanks a lot for all your help.
  6. I

    code needed to check if value from one table can be found in another table

    Hi I just tried but it does not work. I actually checked all three joins and neither of them provides desire result. If I use LEFT JOIN then I just get all records from A and matchin from C but it does not provide info whats the difference if any. So if I do DCount then I would always have some...
  7. I

    code needed to check if value from one table can be found in another table

    Hello I have VBA code that imports Excel files from a specific folder into table A and then appends some data from table A to table B. I need now to amend this code so after importing into table A it would check if values from column 1 in table A are in column 1 in table C and if not...
  8. I

    UPDATE query multiple rows

    Many thanks for your suggestions, I am trying now something different that seems to be easier but for some reason dates are not updated. Any suggestions please? CurrentDb.Execute "Update [tblMonthly] set [Date] = #20130701# where [File] = 'A';" CurrentDb.Execute "Update [tblMonthly] set [Date]...
  9. I

    UPDATE query multiple rows

    Hi I am trying to run below to update multiple records in the same column and get error message saying characters found after end of SQL statement. I tried to remove ; but then get a syntax error. Please help UPDATE [tblMonthly] SET [Date] = #20130701# where [File] = 'A'; UPDATE...
  10. I

    How to populate value from a specific record?

    Many thanks for your valuable feedback. However I still struggle how to identify the right record. In Excel I would say for example read value from D4 if D1 = DH but how to achieve this in Access?
  11. I

    How to populate value from a specific record?

    Can anyone help please? :)
  12. I

    How to populate value from a specific record?

    I use a simple code (below) to import text files from a folder. All works well but now I need to populate a value from particular record for all records from this file. I have attached an example of my table after importing all text files. I need to add another column and based on this...
  13. I

    BAT file does not work if Access is already open

    Hello! I use below simple command line to start a macro in my Access database. Usually all works well. I start my bat file, command is executed and macro does its job. However it always fails if I have a session of Access already opened even if it is a completely different database. If I use...
  14. I

    Concatenate multiple rows without VBA

    I did but maybe I got syntax wrong? I used the code you provided below and then used & to concatenate results but when I selected Max order number I got a number i.e. 6 if there were six digits in my orders instead of proper order number.
  15. I

    Concatenate multiple rows without VBA

    Please, please anyone any suggestions? :D This little error holds up my entire project :banghead: I was looking high and low and cannot find any other solution that would not involve VBA code/function. So I really need to make this work.
  16. I

    Concatenate multiple rows without VBA

    This is exactly the same query you kindly provided previously. It works well for the table from my initial post: Customer Name |Order Number Smith |O101 Brown |O102 Smith |O103 Green |O104 Brown |O105 However if you add on another Smith it stops...
  17. I

    Concatenate multiple rows without VBA

    Hello, I just discovered an error in this code and I wonder if you would be able to help me? It seems to multiple rows as per attached print screen. I would hope to see only first row which have all relevant dates. Is there any way of fixing it? It works fine if there are any two entries...
  18. I

    Concatenate multiple rows without VBA

    Thanks for the above, I have done couple .bat files in the past that had simila code in them. I found it very handy and useful option. Just a question, is there any name for the syntax you used in your query to concatenate strings? I kind of understand what it does but I would like to read...
  19. I

    Concatenate multiple rows without VBA

    Using VBA Excel would be very difficult. This report gets data from six different files, which are combination of CSV, TXT and XLS. Unfortunately I cannot push the data because the brief was that end users should have only exposure to Excel file without the need to use Access at all. Basicially...
  20. I

    Concatenate multiple rows without VBA

    I would love to go with VBA solution as it would be much easier and cleaner. However as I mentioned in my first post, I need to be able to link this query to Excel file. I tried couple different VBA codes and each time I get error message in Excel saying: Undefined function * in expression...
Back
Top Bottom