UK Horse Racing's Data Analyst Tool

Programming Example 2

Introduction

This is based upon a question asked by a member using the DA Tool: how do I filter on dates? Specifially only on races in the months of June to February.

A very good question and it's simple enough with a few extra lines of code and worthy of a place here because this is one step ahead of the other coding examples here.

Filtering On Month

To achieve this what we have to do is to get the months in numeric format so that we can search for months which are less than equal to 2 (February) and greater than equal to 6 (June).

The first thing is that we need to define our variables.

A tip: as a rule if I think that I am going to use soemthing that I may have used before then I find it easy to copy the code to where I want it rather than work out the statements tht I need. It's lazy, sure, but it saves time. And in this case I am sure that I would have sone something like this before and I consider that, if I had, then if I would have stored the month number into a variable then it would have been called nMonth and that's what I searched for and there was an previous use in the Mission-08 class.

So, have a quick look in the Mission-08 class and see how the nMonth variable is defined and used before going on any further.

The first thing to do is to define the variables we are going to need and to place them in the block after the comment:      ' **** This run's declarations

Dim sDate As String, nYear As Long, nMonth As Long

This clearly shows where we are going to place the string value of the date and then the numeric values of the year and month.

Now, do recall we do three things to a variable here: we first define it, we secondly assign a value to the variable and, thirdly, work with it. We have done the first part, now for the second part where we fill the data.

We know that the date is stored in column BG of the CSV file and so we’ll put that into the sDate variable. Then we have to get the month out of there (and in this Mission-08 class we also get the year which I will also illustrate here):

sDate = Trim$("" & asEntry(GetColumnNumber("BG") - 1))
nYear = Year(sDate)
nMonth = Month(sDate)


The first of these three lines we already know about. We’re simply getting the date from column BG and dropping it into the sDate variable.

The next two lines are getting the year and month out of the date into the two numeric variables that you have defined above. The functions Year() and Month() are two inbuilt functions in VBA which, quite simply, extract the year and month numbers from a date string. More information on these and other functions can be found on the internet on pages such as this: http://www.techonthenet.com/excel/formulas/year.php

Right, so we now have the month information safely in the variable nMonth and all we have to do in the block of If statements is to do the following:

If nMonth <= 2 Or nMonth >= 6 Then

Don’t forget to put in the corresponding End If after the block of If statements.

And that’s it.