By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 3,338 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

How do I Select Records from the Month 12 Months Hence

P: 1
I am trying to create a query that pulls information that is 12 months out. I need this to change every month. The syntax I have now will only pull exactly 12 months out, i.e. today is 5 Dec 11 and it only shows the data from 05 Dec 12. I need the data for the entire month. Any help would be appreciated.
Dec 5 '11 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 15k+
P: 31,494
You give us very little to work with (Have you even read any of the notes indicating how to ask decent questions). I've changed the title to what I guess you mean to ask, as that also was invalid (Let me know if it's incorrect and what it should have been if so).

To answer I'll assume some typical and obvious names for your table layout and you will need to interpret them to suit your actual setup. I'll also assume you are looking for a filter (WHERE clause) to specify which records to process and which to ignore.

The following code is somewhat clumsy but is internationally independent (so won't get confused by Regional Settings - NB. These are not SQL Date literals so they will not adhere to the ANSI-92 SQL standard for those - See Literal DateTimes and Their Delimiters (#)) :

Expand|Select|Wrap|Line Numbers
  1. WHERE ([RecDate] Between CDate(Format(DateAdd('m',12,Date()),'1 mmm yyyy'))
  2.                  And     CDate(Formad(DateAdd('m',13,Date())-1,'d mmm yyyy'))
Dec 5 '11 #2

P: 3
You have to build the WHERE clause in your SQL statement. To get this:

Expand|Select|Wrap|Line Numbers
  1. SELECT item, datefield FROM table WHERE ((datefield >= #12/1/2011#) AND (datefield <= #12/31/2011#));
I used the following:

Expand|Select|Wrap|Line Numbers
  1. Dim varMyDate As Variant
  2. Dim strSearch, strDate  As String
  3. varMyDate = Date
  5. varMyDate = DateAdd("d", -(Day(varMyDate) - 1), varMyDate) 'Set the day at the first of the month
  6. Debug.Print varMyDate
  7. strDate = Format(varMyDate, "Short Date")
  8. Debug.Print strDate
  9. strSearch = "SELECT item, datefield FROM table WHERE ((datefield >= #" _
  10.             & strDate & "#) AND (datefield <= "
  12. varMyDate = DateAdd("m", 1, varMyDate) ' move the date to the first of the next month
  13. varMyDate = DateAdd("d", -1, varMyDate)  ' move the day back one to the end of the current month
  14. Debug.Print varMyDate
  15. strDate = Format(varMyDate, "Short Date")
  16. strSearch = strSearch & "#" & strDate & "#));"
  18. Debug.Print strSearch
Dec 5 '11 #3

Expert Mod 15k+
P: 31,494
Scott, I appreciate the helpful impulse, but there are a number of problems with your suggestions :
  1. The question implies a query (QueryDef) is required rather than a SQL string, which is awkward (to say the least) to use if a display is required. Action queries and filters are generally more suited to creating SQL using VBA as you have here.
  2. Expand|Select|Wrap|Line Numbers
    1. Dim strSearch, strDate  As String
    You should understand that in VBA this code creates strSearch as a Variant type variable and not as a String type.
  3. Using the format (X <= Y) AND (X <= Z) is a clumsy way of saying (X Between Y And Z), and is particularly less desirable in situations where X is more than a simple field reference.
  4. Date literals in SQL, as you use in your illustration, should not be used in the way you have. They must be entirely unambiguous in Jet SQL, and that is not necessarily the case when you leave it to the Regional Settings to determine that format for you (You would have understood this already had you taken the time to follow the link attached in my earlier post (#2)).
  5. Nothing wrong with your logic though. The code would certainly produce the correct dates.

NB. I mention these, not in a spirit of criticism, but because people may read it without realising some of the drawbacks of following that guidance.
Dec 5 '11 #4

P: 3
Alrighty. I'll take your word for it. I'm about a week or so into VBA for Access. I've been using '07, and DAO recordsets for just about everything. The search string builder I copied from a working sub and just changed around - mainly I replaced a chr$(34) with a hash inside the quotes (the chr$() was copied from where I had to insert quote marks inside of a search).

As for the format - true, but you can change it to Format(date, "mm/dd/yyyy") to get the date string in the desired order. Probably should have done that.

All that I know is that building a search string and throwing it inside of a OpenRecordset method seems to be working - both for strings and dates as search terms. When I get around to learning better, I'll use that.

Besides, the clever bit was the DateAdd part; everything else was just context.
Dec 6 '11 #5

Expert Mod 15k+
P: 31,494
Scott W:
Besides, the clever bit was the DateAdd part; everything else was just context.
Ooops. Now you mention it, that only shows a span of a month, instead of the twelve months required. That said, the code does illustrate a good understanding of manipulating dates using DateAdd().

Hopefully, like the rest of us here, you were able to learn from simply attempting to help. Good for you :-)
Dec 7 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.