How do I Select Records from the Month 12 Months Hence

[Rena Wilson]

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.

[NeoPa]

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 (#)) :

WHERE ([RecDate] Between CDate(Format(DateAdd('m',12,Date()),'1 mmm yyyy'))
                 And     CDate(Formad(DateAdd('m',13,Date())-1,'d mmm yyyy'))

[Scott W]

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

SELECT item, datefield FROM table WHERE ((datefield >= #12/1/2011#) AND (datefield <= #12/31/2011#));

I used the following:

Dim varMyDate As Variant
Dim strSearch, strDate  As String
varMyDate = Date
 
varMyDate = DateAdd("d", -(Day(varMyDate) - 1), varMyDate) 'Set the day at the first of the month
Debug.Print varMyDate
strDate = Format(varMyDate, "Short Date")
Debug.Print strDate
strSearch = "SELECT item, datefield FROM table WHERE ((datefield >= #" _
            & strDate & "#) AND (datefield <= "
 
varMyDate = DateAdd("m", 1, varMyDate) ' move the date to the first of the next month
varMyDate = DateAdd("d", -1, varMyDate)  ' move the day back one to the end of the current month
Debug.Print varMyDate
strDate = Format(varMyDate, "Short Date")
strSearch = strSearch & "#" & strDate & "#));"
 
Debug.Print strSearch

[NeoPa]

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. 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.

[Scott W]

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.

[NeoPa]

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 :-)