Search results

Hi Bob - Your post #3 at this thread struck home. I've been playing with a current application which tracks expenditures (but not payments). Looking for the most expeditious method of emulating Quicken, which displays both debits and credits, and a running total. Have you come across any...

Hi, welcome to the forum - Not sure why you'd want to represent a datefield as a textfield since, in the process, you lose the capacity to use date-related functions as well as the ability to correctly sort by date. Having said that, lookup the cstr() function. Example: x = date() ? x...

Hi - Try playing with the following logic: x = datediff("d", #12/28/08#, #1/3/10#) ? x 371 y = switch(x > 365, 365, true, x) ? y 365 HTH - Bob

Hi and welcome to the forum - The function in question originally posted here: This function revolves around full months. Unfortunately, the DateDiff() function will return a month so long as the months differ. What's delivered doesn't necessarily represent a full month. Example: ...

Hi - If you already have a date which includes time, use the DateValue() function to return just the date (and the TimeValue() function if you need just the time). Example: HTH - Bob

Hi - Using a function which incorporates the Switch() function (which I personally prefer) is another way around the problem. Public Function MultiIffs(dterecd As Date, days As Integer, compliance As Integer) As String If IsNull(dterecd) Then MultiIffs = "Not Met" Else MultiIffs...

Hi - This will provide a starting point. You'll probably want to convert the sub to a function: Public Sub Jones(datein As Date, dateout As Date) Do While datein < dateout Debug.Print datein datein = datein + 1 Loop End Sub Example: Call Jones(#4/28/10#, #5/2/10#) 4/28/2010 4/29/2010...

Hi - Try this, changing table/query and field names as appropriate: SELECT Query4.ExpDte, DatePart("m",[ExpDte]) AS Expr2, DatePart("d",[ExpDte]) AS Expr3 FROM Query4 WHERE (((DatePart("m",[ExpDte]))=4) AND ((DatePart("d",[ExpDte])) Between 5 And 18)); HTH - Bob

Hi - Take a look at the RandLott02 function in this post. See if it might help. Best wishes - Bob

Hi - Welcome to the forum. Consider the DateValue() function to convert your text date to a true date which you can then sort on. Example: x = "4/24/10" y = datevalue(x) ? y 4/24/2010 To show that y is in fact in numerical date format as stored by Access. ? cdbl(y) 40292 HTH - Bob...

Hi - I don't see anything in your code, other than the following: SELECT EmpCode, Name, DateOfBirth, RetirementDate FROM RetirementDates_sq WHERE GivenDate = RetirementDate; - that in anyway mentions GivenDate. It would be helpful if you'd post a sample db. Best Wishes - Bob

Hi - This series of steps, plugged into a query, will return each section regardless of the size of the sections: a = "ACCT-101-501" b = left(a, instr(a, "-")-1) c = Mid(a,len(b)+2, len(a)- instrrev(a, "-")) d = mid(a, instrrev(a, "-")+1) ? a ACCT-101-501 ? b ACCT ? c 101 ? d 501 HTH - Bob

Hi - To get full months you could use something like this: NumMonths = DateDiff("m", startdate, enddate + (day(startdate) > day(enddate)) The + (day(startdate) > day(enddate)) portion is a Boolean statement which will yield -1 if True, 0 if False. HTH - Bob

Try ? TOJ. Bob

Hi Unicon - Did you test this out. Using the same sample dates from my previous post, it'd look like this: startdate = #3/12/05# enddate = #4/5/10# ? datediff("m", startdate, enddate)/365 0.167123287671233 What is that supposed to accomplish? Puzzled - Bob

Hi - Here's an example you can play with: startdate = #3/12/05# enddate = #4/5/10# x = datediff("m", startdate, enddate) ? x 61 y = x\12 & " year" & iif(x\12>1 or x\12=0, "s ", " ") & (x mod 12) & " month" & iif(x mod 12>1 or x mod 12=0, "s ", " ") ? y 5 years 1 month HTH - Bob

Hi - I, too, am confused re DOB (see Post #9). - DOB is a static data element and should be a field in your table. - Same goes for StartDate. AgeAtStart should be a calculated field (re a query). Should you, for whatever reason, determine that either DOB or StartDate is incorrect and make...

Hi - Take a look at the DateValue() function., e.g. x = now() ? x 3/18/2010 5:22:36 PM ? datevalue(x) 3/18/2010 Bob

Glad to help out. Bob

Hi - Try adding an additional field to your query: Weekday([DateField]) In the criteria cell: Not In ("17") 1 being Sunday and 7 being Saturday. HTH - Bob