Search results

VBA is not normally used to visualise data. It’s always good to use the tools that were intended to achieve a partical outcome.

Same here. I have used Access with Power BI but mostly I use .xlsx files stored on a sharepoint and even better sharepoint lists (as forms) I really like Power Bi. It's easy and intuitive on the one hand. On the other hand, it can be very powerful with DAX and/or Python/R scripts.

Which is what I want (ie. export withOUT formatting) - so it's good. The macro action does it WITH formatting, wich I didn't need. Just looking at the links that you suggested.

Thanks. I'm exporting to .xlsx - I assume you meant I CAN export without formatting, didn't you?

Hi, I've created a set of queries that I'd need to export every month. I've started looking at macros and have found that I could export a query using a series of ExportWithFormatting actions A couple of questions: - Is it possible to export it without formatting? I can't see an equivalent...

Ok. Thank you everyone. I've tried out your suggestions. Some of them didn't work or I didn't understand them- What I ended up doing is creating a make table query with a parameter (so I get a dialog box to input "R04" or whatever period it is. Thanks again.

The Period and YearCode fields are in a table called 'tblVariables', which literally consists of the two fields and 1 record ("R04" and "1920" respectively) The source table is tblSales (field names are in my screenshot) Expected results are in my screenshot as Output Qry. I'm not...

No, but your question made me realise that I'd have to in order to keep track which records were there in previous periods. I'll have to create a make table query out of the query showing records for a particular period.

Thanks - I'll look at the links. Wouldn't it be good to have a place where you can assign a few variables and have access to them from anywhere in the tables? haha - I know I'm missing the point of how a relational database system work..... and am probably approaching it from a wrong perspective.

Apologies for not being clear. I've got the impression I'm making it sound more complicated than it really is. Screenshot of sample data: https://pasteboard.co/IOh9rb7.png So each month I replace the source files. The person reading my output query wants to know which rows are new this...

Thank you for your reply. I realise it is non-standard as I couldn't find a reference on the net. The two variables (Period and YearCode) are not part of the source tables but need to be present in the output queries. I just want to automate things. Previously what I'd do is to create one...

Hi, I have a table with a couple of variables. The table is structured as follows: Period | YearCode | ------------------------- R04 | 1920 The only thing that'll change here is me occassinally changing 'R04' and '1920' to some other values. I want to be able to change it in one...

Lovely. Thank you.

Hi, I've got a field with records as follows: 17/18RO 18/19RO 17/18RO 16//17RO It's all consistent: 2 digits / 2 digits RO I'm trying to extract just 1718 or 1819 or 1617 in a query (basically the 4 digits) I've tried val([field]) but it only returns the first 2 digits. Please advise...

Thank you. Will try it.

Hi, Last month some of you were kind enough to help me with some date genreation. Public Function fncInWeekDay(ByVal dte As Date, strWeekDay As String) As Boolean Const holiday_tbl As String = "tblHolidays" Const holiday_field As String = "HolidayDate" Dim lngCount As Long...

Works fine. Thank you

Hi I am trying to exclude value 40 in a query. Most records in this field are blank, some have numerical values. I would only like to exclude 40 from those values keeping all the blanks and other values. When I do: <>40 It also removes all the records with where this field is blank keeping only...

Thank you both. Just reporting that I have finished the task taking bits/ideas from your files. Over the last week I have been refining/amending it and populating it with real data. Thanks again.

I've noticed that if a course takes place two days a week, there are 2 rows in qryCourseCodes. Is that necessary? As you write above, if you select a course and then View course, it displays a report of all the courses, not just the chosen course. Would it be possible to display only the...