Search results

Gotcha - thanks JHB - so basically I have to make use of a temporary table in order to do this? Rather than doing it all in a query / subqueries?

Is it a split database with linked tables? If you have linked tables, I have found that performance drops considerably the more levels you have to navigate in the directory structure on the network? So if a linked table is located at something like : \\server \folder \subfolder1...

Hi guys, I have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record) I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date So the first step (as I see it anyway)...

Thanks guys CJ, I don't believe I can use IN (for the same reason I can't use Between / And) - the parameter is in the subqueries so there's no way of passing the date across. And even if there was, I fear there would be an integrity issue with examining all the dates in parallel rather than...

Hi guys, I have a reasonably complex query (3 subqueries into 1 main query) which gathers data from various tables into a single dataset based on a specified date. I now need to generate a similar dataset but across a range of dates (a month) for reporting purposes. However, I can't just...

I'm guessing it's because you are not adding the value for that current record to the collection, but adding the field itself? As an aside, when adding items to a collection, I believe you have to add a key as well (but if your code is not erroring that would suggest otherwise...) I thought it...

Hi guys, Probably a dumb question but can't figure it out I have a simple INSERT INTO statement, to add records to my main table from a series of linked tables (text files). I need to run the same INSERT INTO multiple times so I can eventually drop the linked text and just work off my...

You're still not considering what this line is actually doing : .ActiveWorkbook.SaveAs FileName:=strPath & "NewFile.xlsx" You're simply concatenating two strings - but the end result is not a valid path? So if the selected file (sFileName) is "//server/folder/subfolder/file.xlsx" Now you...

27,585 (and counting...) But I like to think my target is to keep the number of lines to a minimum rather than making it larger? If I can do something in 10 lines rather than 20 then I consider that a positive! Ultimately, the success of the DB is how quickly and efficiently it can manage your...

Hi guys, Extension / resurrection of an old thread (here) - an issue which is really frustrating my end users (and, to be honest, myself) The original thread describes the problem but basically, I'm finding that whenever a continuous subform is open, and the user presses the Alt key (in...

Well - for starters, I don't think you (or anybody for that matter) should delve into VBA before understanding how to debug your code. It is nigh-on impossible to isolate problems and identify gaps in your perceived process without the ability to step through it line by line and watch variables...

Are you sure nothing happened? The form may not necessarily refresh, did you check the value in the actual table to see if it has been updated? Putting it in the Click event of a button is perfectly fine Do you have "Option Explicit" at the top of the form's code module and does the code...

You don't want to attach the report to the e-mail? So why are you using this? ObjectType:=acSendReport Did you look at that link I included? Perhaps this is what you are trying to do? ObjectType:=acSendNoObject

This is an example recordset method : Dim dbs As Database Dim rst As Recordset Dim strSQL As String strSQL = "SELECT [Products].* " & _ "FROM [Products] " & _ "WHERE [ProductName] = " & Chr(34) & Me.Combo0.Value & Chr(34) Set dbs = CurrentDb With dbs Set rst =...

You need to use a recordset, or some SQL, to update the record What is the combobox called and what is the name of the field in the Product table to which it refers (i.e. in order that the selected item in the combobox identifies a specific record?)

Take a look at this line : .ActiveWorkbook.SaveAs FileName:="NewFile" & sFileName The path for the new file is being prefixed by the string "NewFile" So if the selected file (sFileName) is "//server/folder/subfolder/file.xlsx" Then you are trying to save it as...

If the EditMessage property is set to True then the e-mail should not be sent? And you can add recipients as CC's by adding them to the CC property? DoCmd.SendObject ObjectType:=acSendReport, _ ObjectName:="order acknowledgement", _...

Assign the string variables with a DLookup? strSource = DLookup("[source]", "[tbl_path]", "[Criteria] = True") strDest = DLookup("[destination]", "[tbl_path]", "[Criteria] = True") Or use a second recordset? Dim rsPath as Recordset Set rsPath = CurrentDb.OpenRecordset("SELECT...

Boom! Figured it out... SELECT tblCurrencies.CurrencyCode, tmp1.SumPay, tmp1.SumPayUSD, tmp2.SumRec, tmp2.SumRecUSD, ((SumPayUSD + SumRecUSD)/(SELECT Sum(tblTransactions.USDAmount) FROM tblTransactions)) AS Proportion FROM (tblCurrencies.... Completely forgot I...

Sum(tblTransactions.USDAmount) is in both of my subqueries? The subqueries are effectively the same, just different WHERE clauses for pays and receipts (I couldn't think of any other way of doing it, I'm sure this is poor) But I'm surprised I can't reference it in the main query as well? And...