Search results

Got it. I modified my code to look like this, just in case anyone is interested... Code: SELECT qrySumFreight.plantCode, qrySumFreight.Year, Sum(qrySumFreight.FreightCost) AS FreightCost INTO tblFreightSum FROM (SELECT plantCode, Year, FreightCost FROM qryCorrectFreightCost UNION ALL SELECT...

David, Unfortunately, the code you posted only works partially for my cause. Everything works fine except for the fact that the end result (after unioning the two queries) is not grouped by distinct plantCodes/Year. The code I posted below works just fine, but now I am working on making it a...

I got it, never mind. The template provided does work, I had a GROUP BY command buried in the bracket loop which is why it was failing.

The reason I'm hesitant is because I'm avoiding building unnecessary queries. The database is large as it is, and it gets needlessly complicated as the number of queries increases. On that note, I found on another forum the following solution: Try: SELECT A, SUM(B) FROM ( SELECT A, B FROM X...

Hi everybody, Here's the code that I'm working on in an MS Access query: SELECT Year, plantCode, FreightCost FROM qryCorrectFreightCost UNION ALL SELECT Year, plantCode, FreightCost FROM qryOtherFreightSum GROUP BY plantCode, Year, FreightCost; This works fine, but afterward I want to Sum...

Awesome, your suggestion worked. Cheers. I guess I always assumed that if you didn't explicitly define (Dim) a variable in VB, it would be implicitly defined at compile-time as type Variant. I was wrong.

Hi everybody, I'm using VB and my code is below: quantityRegion = DLookup("[Quantity]", "tblSalesData", "[regionCode] = " & i & " AND [Year] = " & yr) If (IsNull(quantityRegion)) Then 'nothing Else 'bunch of code ... End If Sometimes quantityRegion results in a Null value, and...

Both of these techniques seem to work. Thanks to both of you -- problem solved.

Let's say I have a Microsoft Access table called tblConstants in my database program (which 'conveniently' has a VB back-end). How would I go about referencing a specific field of a particular record (and therefore, a specific cell) using VB code? I am in dire syntax straits here; hopefully a...

Yeah I understand that, that's not really what I'm concerned about. The issue is that I don't know how to edit the file name automatically using code. Oh, I suddenly have an idea: could I create a macro and code something in VB so that the macro is executed on every form load?

Ok. I've got a database and it has ONE linked table in Excel. Let's call it tblTable, for argument's sake. In order to proceed from the database start screen, the user must click a command button, which executes some VB code and opens up the main database window. Now here's what I want to...

Exactly what I was looking for, thanks a million.

I have an Access database linked with VB. Here's what I'd like to do: Use the TransferText method to create a file (which I have done) and save it in the same directory as the Access database. Here's what I'd like to know: Is there is a built-in VB function for retrieving the path name of the...

I have recently taken over the duty of maintaining an Access '03 database linked with VB. The program employs a series of recordset objects and connection objects associated with type ADODB. Thing is, I have absolutely no idea what the distinction is between DAO, ADO, and ADODB. Anyone have a...

Microsoft Office Professional Edition 2003 appreciate the help.

Three DAO References Under Tools -> References, there are three DAO libraries: Microsoft DAO 3.51 Object Library Microsoft DAO 2.5/3.5 Compatibility Library Microsoft DAO 3.6 Object Library I have tried each one separately and I get a run-time error for each one. The enabling of DAO seems to...

New Problem My database is ADO not DAO I think.. if that makes sense. When I enable the DAO tools my database fails, but enabling ADO tools is insufficient for VB to recognize the user-defined custom types: ADO.Recordset ADO.Field How should I write to Excel with an ADO database?

That eliminates the error I was having, now I'll just modify the code a bit. Thanks, trivial solution.

I'm fairly new to Access/VBA but I know Excel like a brother. I have a database in Access featuring a VB-module calculation process that perform operations on queries and tables. I want to write a RecordSet to an Excel file so that each time the calculation process is executed, a new Excel file...