Recent content by Clipper2000

SELECT Field1, sum(field2) as A, sum(field3Percent) as B, sum("enter your expression here") as C From YourTable Group By Field1

Thanks for the info ByteMyzer. It appears that when the query is saved the first time, Access will writes to the system table with the correct syntax. Any subsequent runs will be based on the system table and the fact the query designer revert to the {}. syntax is ignored at that point.

Hi Michael, I have the database being discuss on my PC, and I can say that putting "" around the AS such as "AS" won't work. The syntaxes PHV proposed are correct one. In all honestly, I learned this little "FROM (SELECT .....) AS alias" syntax from PHV in the last few days and for that I...

You are right that the query builder have something to do with it. The correct syntax is: SELECT B.GLM, A.Description, A.Accpac, A.Amount, A.Category, A.Period, A.Year FROM tbl90707 AS A INNER JOIN (SELECT DISTINCT GLM, Accpac FROM [GLM:ACCPAC]) B ON A.Accpac = B.Accpac WHERE A.Accpac <...

This is wild ! Instead of creating the query definition in the query designer, which will not saved properly. The whole thing works if I create the query using DAO like this: Sub CreateQry() Dim db As DAO.Database Dim rst As DAO.Recordset Dim strSQL As String...

Hi there, I used to use FoxPro2 (DOS) long long time ago (over 10 years ago) and find it a lot better than dBase 4 at the time. My preference was and still like the product a lot. I became an Access developer not by choice but by circumstance. You see, a lot of companies have Access in their...

Example CrossTab query: TRANSFORM Sum([tbl90707].[Amount]) AS Amount SELECT [NelNetRollUp].[Total], [NelNetRollUp].[SubTotal], [tblNelNetMapEdulinx].[Account], [tblNelNetMapEdulinx].[subAcct], [tbl90707].[Accpac], [tbl90707].[Description], [tblNelNetRollUpOrder].[RollUpOrder] FROM ((tbl90707...

Personally, I really like coding with VB6 because the door is wide open to ActiveX connections. Will it be worth my efforts to continue upgrading my skills with this coding idiom.

Hi again, By the way, there is a big limitation to attach only a SQL string to the record source such as: Me.RecordSource = rst.Source This is because the source never change, even if you manipulate the ADO afterward, ie, filtering it etc. As a result, PHV's method of attaching a true ADO set...

Hi cmmrfrds, I used Access 2000, I have 2002 but never try it. You are right that in Access 2000 the Report does not have a recordset object. However, I should have thought of Me.RecordSource = rst.Source, its my mistake :). Personally I prefer PHV's version.

Hi, Thanks gentlemen for your excellent answers ! The Me.RecordSource = rst.Source will return a SQL string and as a result, it worked, and this is exactly what I need !

Hi, There is a discussion in the Access Reports forum on how to attach an ADO recordset to the Me.RecordSource property. So far attaching an ADO recordset to the RecordSource property doesn't seem possible in a regular report, it may be possible in ADP. This is because the Me.RecordSource...

Hi payback, I'm assuming this works on ADP, but this doesn't seem to work on a regular Access report. The codes are fine, except that the "Set Me.Recordset = rstf" hangs. It appears that the "Set Me.Recordset = rstf" only works in a regular Form but not a regular report. Perhaps this is why...

Hi, For what it's worth, here's my sugestion: Ditch this query and rewrite (code) it properly in a VBA module, with plenty of comments and explanations. Then use the DAO class to create the query for you by running the module. The difference is this approach make debugging a lot easier few...

Hi GingerR, You are right again. I tried picking it apart by having 2 separate queries and it worked. I am sorry for getting you through with all this. My purpose here is curiosity and testing (or play with) the Access query designer, not necessary JET 4.0 engine. I think JET 4.0 is fine...