Search results

Oops! My original code treats dates as integer values, and threw a wobbly when you specified time portions. I've decided to fix this by fixing the dates in the function Public Function CountWeekDays(ByVal StartDate As Variant, ByVal EndDate As Variant) As Variant Dim lDaysTotal As Long...

Expiry Dates Here is my outline on how I would approach the problem (no code sorry)... You should be able to come up with a function that does what you want fairly easily. It will take two parameters: IssueDate and DateOfBirth and return the ExpiryDate. You'll also need a function that can...

Dates are actually fixed-point decimals under the hood, so your bizarre result comes from subtracting one date from another directly and getting an answer that is nearly zero. Don't worry about any of that... the following should give you what you want (assuming that your race doesn't go on for...

olMailItem olMailItem is an enumerated constant try defining it like this: Public Const olMailItem = 0

The BETWEEN operator is inclusive... Using BETWEEN should give you the correct results, so there must be something odd about your data. I going to guess that BLDate is entered as a timestamp (using the Now() function). Because of this, entries made at lunchtime on the on a given date, say...

late-binding I cant think of a way to do this other than late-binding. Intellisense won't work with late binding, but you should still be able to use the properties and methods. I'm assuming that you are using the Object data-type to represent all the outlook objects. Having said all that...

The holiday question? I wouldn't want to alter the function CountWeekDays, as it is fit for purpose and useful in its own right. I would have another function (say 'CountHolidays') that calculates the number of holiday days between two dates. Then the value you want is achieved by simple...

clarification... doulostheou, DateDiff counts interval-boundaries not elapsed periods, try these: Datediff("n", #04/04/2003 00:01:59#, #04/04/2003 00:02:00#) Minute(#04/04/2003 00:02:00# - #04/04/2003 00:01:59#) The first example below returns 1, whereas the second example returns 0.

Try this sort of thing SELECT A.* FROM Table1 A INNER JOIN (SELECT Table1.AcctID, Max(Table1.EffDate) AS MaxOfEffDate FROM Table1 GROUP BY Table1.AcctID) B ON A.AcctID = B.AcctID AND A.EffDate = B.MaxOfEffDate

raskew, Thanks for the feedback about storage. I thought that VBA went to fixed precision when the "hard" Date type was introduced to MSAccess rather than the Variant (date) subtype, but after running a few tests apparently not. I noticed the MS article is pretty old, and still suggests using...

Yes, as I pointed out the method I outlined does not work well when the dates are more than 24 hours apart like the example in Pat's response... #3/1/2001 1:30# - #2/1/2001 00:45# However, the values '28.03125' and '27/01/1900 00:45:00' are equivalent (assuming that you're measuring dates from...

Redim Preserve can only be used to resize the last dimension in your array, so do it this way around... Dim MyArray() As Variant Redim MyArray(2,0) MyArray(0,0) = "Tracy" MyArray(1,0) = "Hill" MyArray(2,0) = "Programmer" Redim Preserve MyArray(2,1) MyArray(0,1) = "Fred" MyArray(1,1) =...

I should admit this only works well if your dates are less than 24 hours apart

Under the bonnet (hood if you're american) the Date data type is fixed-precision, and therefore shouldn't really suffer from floating point errors. However, the DateDiff function returns an Variant (long integer), and there is some dynamic casting when you divide this by 60 that returns the...

Mile-O-Phile, Your function loops through every date and will be less efficient the further the dates are apart... if you use a mathematical approach you'll get a much more efficient function. ;) This approach counts five for each complete week between the two dates, then adds the number of...

The insert that raised the error in the first place is probably not rolled back until you come out of debug mode (you may even have to close the application). Try re-writing your code within a transaction, include error handling that rolls back the transaction appropriately.

I imagine it's possible using a VBScript batch file instead of DOS

This function will round to various factors of 10. Specify 3 for 1000, 2 for 100 etc. Note: If you are rounding to nearest 1000, then 500 will always be rounded up. Public Function RoundTo(ByVal Value As Variant, ByVal Factor As Long) As Variant Value = Value * 100 'Work in pennies for...

Help authoring is a job in itself If you want professional looking results, and can write HTML you can compile your own .CHM file. Go here for free download... http://msdn.microsoft.com/library/default.asp?url=/library/en-us/htmlhelp/html/hwMicrosoftHTMLHelpDownloads.asp how well this...

This should do it LoopControl = CurrentDB.OpenRecordset("select max(id) as MaxID from transactions")!MaxID