Recent content by Bogzla

do you have the same problem if passing the query as a text string? strSQL = "SELECT.......;" Rst.Open strSQL, cnn, , , adCmdText if so can you post the code for QRY_DuplicatesTopLevel?

I'm a little confused as to why you need the HAVING line - it seems to me that this query will implicitly only return results where there is at least one entry for .Agent from QRY_DuplicatesTopLevel, perhaps this is confusing matters? In fact I'd be tempted to try something like: SELECT...

maybe try: rst.Open "QRY_AgentsToSendDupeReport", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdStoredProc ?

hmm, I guess that does make sense for less complex queries that call many records. I don't suppose there is a way to change the order in which the SQL is evaluated? I have been trying to stay away from storing too many queries within Access but it's starting to look like I might have to...

Yeah I figured it must be something to do with the way the SQL is processed in Access... (I don't know much about the low-level stuff) i got a (kind of) work-around because the query is called from excel, and the records displayed in a worksheet, I just hide the rows where both values are zero...

Sorry, my bad (RTFQ...) I think you'll need to use a subquery, try something like this: WHERE InvoiceNotes.ID IN(SELECT TOP 3 InvoiceNotes.ID FROM InvoiceNotes WHERE invoicenotes.invno = invoices.invoice_no ORDER BY InvoiceNotes.ID DESC;) the subquery *should* return the top 3...

try: select TOP 3 invoice_no, inv_date, totamnt, projman, invoicenotes.note, invoicenotes.notedate from invoices left join invoicenotes on invoicenotes.invno = invoices.invoice_no should display the first 3 records only. you'll probably want to add an 'ORDER BY' line to the end to make sure...

so, I have created a working SQL statement that summarises data from a bunch of different tables (it's a stock monitoring application so it all has to do with levels of stock, numbers of parts processed etc...): SELECT tblPPIn.BatchID, tblPartDescriptions.DrawingNumber AS [Drawing Number]...

So I'm using a fairly complex query with a couple of sub-queries as expressions: SELECT tblPPIn.BatchID, tblPPIn.Qty AS [Qty In], IIf(IsNull((SELECT Sum(tblPPProcessed.QtyProcessed) AS SumOfQty1 FROM tblPPProcessed WHERE (((tblPPProcessed.BatchID)=(tblPPIn.BatchID)));)),'0',(SELECT...

My solution For anybody that is interested, I figured a way to access textbox values by using OLEobjects: Dim str As String Dim o As Object For Each o In ActiveSheet.OLEObjects If Left(o.Name, 7) = "TextBox" Then str = o.Name Debug.Print str str = o.Object.Value...

Hi Big Pat, one thing you could do is format cells>number>custom and type 00"/"00"/"00 into the 'Type' box this will display a typed value of 020507 as 02/05/07, for example. This would be a visual thing only, however, the value in the cell would remain 020507, it would just be displayed to the...

good point... I've modified the code to be, erm, better written... Dim str As String Dim shp As shape For Each shp In ActiveSheet.Shapes If Left(shp.Name, 3) = "txt" Then str = shp.Name Debug.Print str str = shp.TextFrame.Characters.Text Debug.Print str End If Next...

extract values from text boxes (Solved) EDIT: I figured out a way to do this, scroll down to the last post if interested... so I have some text boxes on a worksheet, and I want to loop through and extract the data from each of them.. I could get it by string = string &...

just wanted to add - i think it's the With objNotesField .APPENDTEXT "This e-mail is generated by an automated process." .ADDNEWLINE 1 .APPENDTEXT "Please follow established contact procedures should you have any questions." .ADDNEWLINE 2 End With bit you'd need to use, if you...

I found this page really useful: http://www.bygsoftware.com/Excel/VBA/email_from_lotus_notes.htm I have used the code and got it working perfectly from excel... I have to admit I've not used it with access but I guess it should work hth Bogzla