Stumpted on Dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm stumpted. i want to extract the year and month part of a date and still
be able to use it in a between statement. DatePart doesn't support "yyyym"
and Format turns it into a string. How do I do it? Thanks.
 
I don't quite get what you are after, but if it is that you are trying to
determine a date is between the month and year of a date regardless of the
day of the month and the month and year of another date, here are a couple of
formulas that may help:
The following returns the last day of the month for a date:
dateadd("d",-1,dateserial(year(date),month(date) + 1,1))
This will return the first day of a month:
dateserial(Year(date),month(date),1)

So, it might go something like this

If dtmCheck Date >= DateSerial(Year(LowDate), Month(LowDate),1) _
And <= DateAdd("d",-1,DateSerial(Year(LowDate), Month(LowDate) + 1,1))
Then
'It is in range.
 
Actually it's like this:

I want the user to be able to pick a year and month from a drop down box,
say to graph all the occurences of something between 200601 and 200603,
withing having to pick or key in the day part of the date. I've found nothing
built in that extracts just the year and month that is useable in a
comparatsion like a between statement. Oh, and the drop down is made of of
all the possible dates in a transaction table, refined down with a "distinct"
operator.
 
Can't you just add the "missing" days behind the scenes? For example,
this returns records from & including August 2004, to and including May
2005:

BETWEEN #8/1/2004# AND (#6/1/2006# - 1)

Does that help?

(Note: dates in #...# format must always be in month-first format.
#6/1/2006#-1 is the day before June 1, 2006, ie., the last day of May
2006.)

TC (MVP Access)
http://tc2.atspace.com
 
First thing, It is going to have to be a string, at least for display
purposes. That you can create in your query, which I assume is the row
source for your query:

CompDate: Format([TransDate], "yyyymm")

Next, are you using a list box with multiple select capability or a combo
for from date and another for to date? You will have to use one or the other
to get a range of dates. Then you can convert the transaction dates to a
string for comparing them. For example purposes, I will assume you are using
two Combos:

strTransDate = Format([TransactionDate], "yyyymm")

If stTransDate >= Me.cboFromDate and <= Me.cboToDate Then
It is in range






CompDates:
 
Back
Top