Recent content by simon03

plog, you are right. That was not the issue. It looks like the issue is what was pointed out in one of the first replies (use of reserved words for fields). I was actually using the reserved word "Month" for a field. What I noticed is that when the algorithm produced wrong calculations...

I guess I have found the error and it's not in the UPDATE per se. After running the update I noticed that the TEMP4 table is not always sorted by date. It appears that the inner join sometime messes up the order of the table. I have implemented this code: UPDATE TEMP4 AS t1 INNER JOIN...

I guess this post summarises quite well and was quoted by other users later as the right approach to my problem: http://www.access-programmers.co.uk/forums/showpost.php?p=1374347&postcount=33 Regarding posting a sample of the DB it is quite complicated at the moment (meaning that the VBA does...

Right. I changed the code to make it more readable here but actually my SQL code looks like this: UPDATE TEMP4 AS t1 INNER JOIN TEMP4 AS t2 ON (t1.Month_ = DATEADD("m",1,t2.Month_)) SET t1.index_=(1+t1.ratio)* t2.index_, t1.ratio = IIF( t2.index_=0 ,t2.ratio, t2.index_) Why am I storing...

Hi, I have a query which reads a table, calculates some values and updates some of the table's fields. The issue that I have is that sometime (and randomly) the update skips one record. The query uses an inner join and looks as follows: UPDATE TEMP4 AS t1 INNER JOIN TEMP4 AS t2 ON (t1.Month...

OPSSSSSSSSSSSSS LOL :D Btw, I guess that the Autowidth option is not implemented in Access and this needs to be done programatically. This is the first attempt that I have implemented: 1) With a RecordSet I count the number of columns in the query 2) Because I know the width of the columns...

Hello, I need a few suggestions about the visualisation of my queries. At the moment my DB is doing is the following: 1) the user makes some selections in a form and run a VBA code (activated by a bottom command) which essentially creates a query at the end of the process; 2) The query is...

You got it! :D Thanks for your help! Simone

Hi, I have this cross table for a query called OUTPUT. The query has four fields (country, indicator, value and date). I want to cross table by date as follows: TRANSFORM Sum(OUTPUT.value) SELECT OUTPUT.country, OUTPUT.Indicator FROM OUTPUT GROUP BY OUTPUT.country, OUTPUT.Indicator PIVOT...

Great! Your suggestion works! Thanks :D

Thanks for your answer but that video does not solve my issue. The proposed implementation of that video implies that each element, that I want to move, is manually selected. That functionality already works in my implementation. What does not work is to move items that have been selected with...

Hello, I have two listbox (SearchResults5 has two columns) and list_asset_add (one column) both have Extended multi-selection active. I have this code that automatically moves the items between the two listbox (it runs after pushing a button): Sub CopiTo_Click() Dim Msg As String Dim i As...

It looks like that the suggestion in post #10 does the trick. Not getting errors anymore Thanks :)

You're right. I should have tested my Query before. I tested it outside the VBA environment and then tested again inside the VBA environment. What I found out is that the problem is solved if I create the query with the UNION statement inside a single string instead of concatenating two. This...

Just an extra information. With the option: strSQL1 = strSQL2 & " UNION ALL " & strSQL1 If I manually sort by name and then by month the query's result (with UNION), I correctly visualise the results as I like. Maybe I can just use this approach (although not very elegant)