Search results for query: *

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...

Hi GingerR, You are absolutely right and seems your copy of Access is exactly same as mine. Everything runs no problem on first try. The real issue is after I saved the query and try to run it the second time, it fails. The reason why it fails is because now the brackets have changed and...

Hi, These codes should create the necessary tables, make sure there is a reference to ADO Ext. 2.7 DLL Function CreateTable90707() Dim cat As ADOX.Catalog Dim tblSLData As ADOX.Table Dim col1 As ADOX.Column, col2 As ADOX.Column, col3 As ADOX.Column, col4 As ADOX.Column, col5 As...

Thank you GingerR ! So now the question is: is this a bug? and do we have a way to make this query saved properly? I am willing to add additional brackets around them but that just doesn't seem to do it. I'm glad you share my view on this, and hoping someone can help me solve the problem.

Hi, >>> The problem seems to be that, if you attempt to save it with this modified format you get "Error in From Clause". You need to restore the parentheses before it can be saved again. <<< My access saved it without giving me the warning "Error in From Clause", it just save it. Any...

Hi Golm, I have try paste the query into blank database and it saved ok. So if you wouldn't mind, please open a blank database and saved it there to see if it will do the same. Access will not only convert the ( into [ and add a period at the end, it will also add the key word "AS" after. ????