Search results

OK...by chance, I figured it out. I had to add WHERE [Costs]>0 to the existing code.

I even changed the Count([Name]) to Count([ID]) and got the same results...I reviewed the records to confirm that [Costs] did not have any NULL (and even added that to the criteria. I ran a pivot table from my Excel version of the data as a check point and the Costs match, but the count is 384...

Here's my SQL...I removed parens for clarity SELECT [Book Yr], Count([Name]), Sum([Costs]) FROM [tblName] GROUP BY [Book Yr] HAVING [Book Yr]>=2003 AND Sum([Costs])>0 ORDER BY [Book Yr];

I'm not getting my Query to work right, please help. :confused: I'm using 3 Fields [Book Yr], [Costs] and [Name]...what I'm looking to get is a breakdown/Group by Book Yr of the Count of Name and the Sum of Costs where [Costs] > 0. I am able to group by the year and and get the correct sum of...

I've been able to extract info using SQL/ADO, but since this query provides me with a single field, I just export it to the specific cell in excel that I need. Thanks for the help!:D

Maybe I'm not looking in the right area, but doesn't that command transfer from excel into access?

Do you know if it's possible to populate a excel variable/cell with the "Sum" from the query using VBA/ADO/SQL? I have the following code that I use to get "Count", but don't know how/if I can get the Sum w/o extracting all the records and calculating a Sum and then deleting the extract...

Agreed. The table is actually populated from a Lotus Notes db via an ODBC connection...currently all the fields are Text. ;)

They're text fields. I'm hoping that I would be able to use the SQL in excel/ADO to populate a variable w/the sum/number. ;)

Yes, there are 2 fields.

I have a database and I want to create a query (using SQL) that just gives me the Sum of 1 field for the records in question. For Instance, I want my query to sum the total of [Amount] for all records that [DateYr1] = 2008 and [DateYr2] = 2010 and [Amount] > 0. Is that possible? I'm not...

An example of what I am trying to do it comparable to putting together a schedule for a sports team, where there is a table of all the opponents which includes their location. Then on the individual team schedule form, when you select the various opponent(s), the location of the competition...

On a form, the user has the ability to pick and entry from a related table...which has an associated location/city. I would like there to be an combobox that would give the user the option to select the associated location or 1 or 2 other fixed locations. How can I incorporate that? Thanks!

I believe the problem lies here... .Append "Date1", adDate With ![Date1] Set .ParentCatalog = cat .Properties("Nullable") = True End With In the example I had used as my example, it included the following after...

Here it is... Sub CreateDB_and_Table() Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim sDB_Path As String With Application .DisplayAlerts = False .ScreenUpdating = False End With sDB_Path = ActiveWorkbook.Path & Application.PathSeparator & TARGET_DB...

Thanks, but I don't have the code available, it's a work project. However, I have been looking at a printout of the code and believe my issue is that I need to set my ADOX.table = Nothing. I'll find out tomorrow, I guess. :D

Hello, I have Access/Excel 2007 and am using excel macros with ADO to create an .accdb file and loading a table from excel. I am then running my queries via the macro. I'm not real fluent with this process and the problem that I am running into is that when the .accdb file is created the...

I have an access 2007 database that has a handful of queries that are date range driven. Currently, I have to open the db and put the dates in a form...then the queries provide me the data for that data range. Is it possible to use excel to connect to the access db and set those dates via a...

Yes, I can create a report based on a query...then I only have that query's fields to choose from.

When I create/Blank Report I have my "Field List" to choose from...however, the only fields data I have to pick from are the fields from my table. I am not able to select the fields from the Queries.