This seems unnecessary, but VBA is a strange place. If you declare an array variable, then set it using Array() then pass the variable into your function, VBA will be happy. Sub test() Dim fString As String Dim arr() As Variant arr = Array(“foo”, “bar”) fString = processArr(arr) End Sub Also your function processArr() could … Read more
SELECT T1.ID, T1.AccountNumber, T1.Date, MIN(T2.Date) AS Date2, DATEDIFF(“D”, T1.Date, MIN(T2.Date)) AS DaysDiff FROM YourTable T1 LEFT JOIN YourTable T2 ON T1.AccountNumber = T2.Accountnumber AND T2.Date > T1.Date GROUP BY T1.ID, T1.AccountNumber, T1.Date; or SELECT ID, AccountNumber, Date, NextDate, DATEDIFF(“D”, Date, NextDate) FROM ( SELECT ID, AccountNumber, Date, ( SELECT MIN(Date) FROM YourTable T2 WHERE T2.Accountnumber … Read more
You should be able to do this with a pretty standard DAO recordset loop. You can see some examples at the following links: http://msdn.microsoft.com/en-us/library/bb243789%28v=office.12%29.aspx http://www.granite.ab.ca/access/email/recordsetloop.htm My own standard loop looks something like this: Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset(“SELECT * FROM Contacts”) ‘Check to see if the recordset actually contains rows If Not … Read more
The binary representation of False is 0000000000000000 (how many bits are used depends on the implementation). If you perform a binary NOT operation on it, it will be changed to 1111111111111111, i.e. True, but this is the binary representation of the signed integer -1. A bit of 1 at the most significant position signals a … Read more
I don’t know why you’re even trying to compare these to cases. null means that some column is empty/has no value, while default value gives a column some value when we don’t set it directly in query. Maybe some example will be better explanation. Let’s say we’ve member table. Each member has an ID and … Read more
The code below will create an outlook message & keep the auto signature Dim OApp As Object, OMail As Object, signature As String Set OApp = CreateObject(“Outlook.Application”) Set OMail = OApp.CreateItem(0) With OMail .Display End With signature = OMail.body With OMail ‘.To = “someone@somedomain.com” ‘.Subject = “Type your email subject here” ‘.Attachments.Add .body = “Add … Read more
Consider the Switch Function as an alternative to multiple IIf() expressions. It will return the value from the first expression/value pair where the expression evaluates as True, and ignore any remaining pairs. The concept is similar to the SELECT … CASE approach you referenced but which is not available in Access SQL. If you want … Read more
It’s mainly a matter of history, effectively ACE has superceded JET: Wikipedia answers your question in great detail. The most relevant sections are: With version 2007 onwards, Access includes an Office-specific version of Jet, initially called the Office Access Connectivity Engine (ACE), but which is now called the Access Database Engine. This engine is fully … Read more
I found that using DAO in a specific manner is roughly 30 times faster than using ADO.NET. I am sharing the code and results in this answer. As background, in the below, the test is to write out 100 000 records of a table with 20 columns. A summary of the technique and times – … Read more