extraction

[Rockape]

hi,

Wondering if someone could help me.

I would like to write a vba script which would extract the the previous month's entries in relation to the now function..

in other words i have a list of date related entries and i would like the script to extract data that pertains to the previous month in relation to the current computer date...

ie. if my computer date is say january the script would extract the entries from the table which only pertains to december!!!???

 

[CyberLynx]

You may use code like this:

   Dim DaysInPrevMonth As Integer
   Dim ThePrevMonthStart As Date
   Dim ThePrevMonthEnd As Date
   Dim StrgSQL As String
   
   ThePrevMonthStart = DateAdd("D", -1# * DatePart("D", Date) + 1, DateAdd("m", -1, Date))

[COLOR="DarkGreen"]   'NOT NEEDED BUT FOR YOUR INFO IF YOU EVER WANT TO FIGURE IT OUT![/COLOR]
    DaysInPrevMonth = DateSerial(Year(ThePrevMonthStart), Month(ThePrevMonthStart) + 1, 1) - _
                      DateSerial(Year(ThePrevMonthStart), Month(ThePrevMonthStart), 1)
     
   ThePrevMonthEnd = Date - DatePart("d", Date)
   
   StrgSQL = "SELECT * FROM MyTable WHERE MyTableDateField BETWEEN #" & _
             ThePrevMonthStart & "# AND #" & ThePrevMonthEnd & "#;"
   [COLOR="DarkGreen"]'..................
   '[B]Fire or use the Query String (StrgSQL) any way you like[/B]
   '..................[/COLOR]

.

 

[DCrake]

Simple Software Solutions

Again this is an example of double posting. Because you did not get an answer to the original post you rephrased the question and reposted. Ok you got an answer in this post, but time was taken to offer you a solution to your original post. Please be patient in future there is more than you looking for help.

CodeMaster:

 

[Rockape]

My sincere apologies...

I did not intend to cause any bother.

I was just curious whether there was another way of tackling the problem.

Once again my apologies

Kind Regards