Search results

Hi DBGuy, can you send me links to the examples please? Access won't even let me create a report without the PIVOT .. IN statement completed for some reason

Hi, I have got a crosstab query working very nicely showing the column headings I want. However, when I go to create an Access report from it Access tells me that I have no PIVOT fields defined. Below is the query. I added the bit in red so that I couold gnerate a report but a) I really...

Thanks. I'll have a look at ADODB. Is ADODB generally better than DAO or do both have their pros and cons ( I suspect I know the answer! :) ). here are the results. I thought you'd like to see the results of your advice! :)...

Hi MajP thanks as ever for your input but to me looping through the records individually makes sense. If not I would have to do some sort of variable query (the max number of team members differs potentially by race and gender) and then a modulus calculation to it to determine if the number had...

Hi Majp, I get that. TBH, the coding logic isn't really that important it's more about theoretically how the begintrans/commit/rollback work and more specifically what rollback does to the current record indicator. So the first question is: Does Rollback reset the current record indicator? I am...

Well, good news. I have the logic at least running and yet to be tested fully but it seems to be generating something akin to sensible results . My last challenge now is how to implement Rollback and Commit. I looked around and got a bit confused by seemingly conflicting examples. In the end...

Thanks MajP. Yep, you're right a crosstab query will work just fine! Hope to have a sample report by the end of the weekend. Just looking at Commit and Rollback as there are a couple of tables I will be updating in parallel.

Thanks MajP. Once I have calculated the results the report is hopefully straightforward as you say. I know what I want to achieve which is half the battle. Just getting used to different tools and I have never coded Pivot tables although have used them quite happily a long time ago! Let me...

Thanks DBGuy and especially Leigh Purvis. I'm such a nerd I would like to know why it doesn't work with db.openrecordset but it does with docmd.openquery but another time! Yes, MajP, you can do it in code but then you have to potentially repeat the code wherever you use that query and that...

Hi, I'm getting the above error on the second line in my VBA code below. Can anyone explain what I am doing wrong please? I added the first line as a test just to see if the query works from within VBA and it does work fine. It seems to be suggesting you cannot run a query using the...

Hi Adam, I had no intention of storing VBA code in a table just an array of values. I think that was clear from my original post. "is it possible to store an array of values in a single field and row in an Access table?" You seemed to suggest a solution with your x(25,10) variant but that I...

OK, so now I am completely confused. Adam (aka vba_php guy) you posted: What did you mean by this? I can easily have a record per team per race with a pair of values for each but it looked like your suggestion above would allow a single field in a single table. I'm only using 'n' to indicate...

theDBGuy, the select statement allows it but it shows nothing in the column. Perhaps you just can't do it although in VBA [Forms]![frmSelectRace]![togHistReports].value shows the expected value of 0 or -1. Anyways, I've taken a different route but if there is an answer it would be good to know...

I want to avoid multiple reads on the database hence wanting an array in a single field on a single row. VBA_PHP, your solution looks to be what I want. But I'm not clear how I would implement this. Would there be one field called 'x' of type variant of length 26 x 10 i.e. 260 which I would...

Hi, I want to create an array in an Access table but it doesn't seem that it is possible. Do I really have to manually create all fields in the 'array' as individual fields? BTW, I want the array to be of 26 x 2 (integers). One pair for each letter of the alphabet. Each letter can have 2...

Thanks for the messages guys. There's nothing wrong with the SQL as it works with CInt(0) 'False' or CInt(-1) 'True'. It doesn't work without CInt() and I would expect that as the datatype of a form field is Variant. The datatype of the RaceEventClosed field is Yes/No in the access table i.e...

Hi, I'm running Access 2013 on Windows 10. I have a form with a drop down which selects races that have not been 'finalised' (i.e. the results verified as correct, it doesn't really matter though. The point is that this is a an access database Boolean Yes/No type field with values as I...

Good point. Thanks for the reminder. :)

Well, the report is calculating things like position overall, position within gender and position within gender category as well as various other things which are just difficult to do on a line by line basis in SQL. Since they will want to print the report anyway it makes sense to save the data...

Hi, I am running Access 2013. I am writing a Race Timing database and when it comes to printing the times for a race I have a report which saves all the runtime data to a table so that when summarising the results later for e.g. a series of races (and also for historical purposes) the data is...