Comparing dates between

Here is code to determine if a day falls between two specific date-times:

Sub Hello()

  Dim now_its
  Dim yest_was
  Dim row_ptr As Long

  now_its = Now
  yest_was = DateAdd("d", -7, Now)
  
  row_ptr = 2 ' skip header
  While Cells(row_ptr, 1) <> "" ' assumption: no blanks in 1st col
    Dim cur

    cur = Cells(row_ptr, 1)

    Cells(row_ptr, 2) = DateDiff("d", yest_was, cur)
    Cells(row_ptr, 3) = DateDiff("d", now_its, cur)
    Cells(row_ptr, 4) = DateDiff("h", yest_was, cur)
    Cells(row_ptr, 5) = yest_was
    Cells(row_ptr, 6) = now_its
    
    is_after_yesterday = False
    is_before_cur = False
    
    If DateDiff("d", yest_was, cur) = 0 And _
      DateDiff("h", yest_was, cur) >= 0 Then
        is_after_yesterday = True
    ElseIf DateDiff("d", yest_was, cur) > 0 Then
        is_after_yesterday = True
    End If
    If DateDiff("d", now_its, cur) = 0 And _
      DateDiff("h", now_its, cur) <= 0 Then
        is_before_cur = True
    ElseIf DateDiff("d", now_its, cur) < 0 Then
        is_before_cur = True
    End If
    
    If is_after_yesterday And is_before_cur Then
        Cells(row_ptr, 8) = "IS BETWEEN!"
    Else
        Cells(row_ptr, 8) = "NOT IS BETWEEN"
    End If

    row_ptr = row_ptr + 1
  Wend
End Sub

Sample output:

between-dates-test-data

Edit: a working version with more granularity and test output.

Advertisements

Rules-processing template

Here’s one approach to writing up rules for a VBA macro:

Private Enum ColumnNames
  fldGender = 4    ' D
  fldAge = 2       ' B
  fldName = 1      ' A
  fldSSN = 3       ' C
  fldFaveColor = 5 ' E
End Enum

Sub RunModule(args As String)
  Range("A1").Select
  Range(ActiveCell, ActiveCell.CurrentRegion).Select

  For Each r In Selection.Rows
    If r.Row > 2 Then ' skip header
      Cells(r.Row, fldGender) = GetGender(Cells(r.Row, _
        fldSSN))
      Cells(r.Row, fldFaveColor) = GetFaveColor(Cells(r.Row, _
        fldGender))
      ' etc...
    End If
  Next
End Sub

Private Function GetGender(s As String)
  If s = "12345" Then
    GetGender = "F"
  Else
    GetGender = "M"
  End If
End Function

Up in smoke

Access failed today, but it had been quietly failing for weeks. The only indicator was when a reporting process tried to pull records from the .accdb file. The front-end, with its multitude of users, said nothing! So for almost a year it was good, and then on row 15,336 there was silence ever after.

Access tried to auto-repair when I opened it, but the expected entries were gone. In its place was incomplete data, the kind where you know there should be twice as many rows. We kept backups, but they were not granular enough: there were still two days’ worth of bits we’d never see again.

I distinctly remember the deployment phase, because I asked the lead, “Do you want daily reports?” And he said, “No.” I don’t do that anymore. Even if they don’t want the convenience of daily reports, you certainly want the insurance of them. But that doesn’t fix the real problem: what to do when corruption strikes?

Incorrigible input

Somewhere before the deep end, I am writing Fortran code.

       READ (5, 100) COST, DEPREC
100    FORMAT (F7.2, F6.2)
       VALUE = COST - DEPREC
       WRITE (6, 200) COST, DEPREC, VALUE
200    FORMAT (1X, F12.2, F11.2, F12.2)
       STOP
       END

The proper input demands fixed fields themselves. Passing 100 and 10 and the resulting output,

$ ./a.exe
0010000000100
       100.00       1.00       99.00

If the input were read in on a card, it would really be just a line of digits. The assumed decimal places are specified by the first FORMAT(); “assumed” as in does not count toward column positions. The second FORMAT() specifies printing position, and this time the decimal point does count toward column position.

In case of emergency

Need-based development has its ups and downs. On the one hand, you’re usually working on a central issue: it’s on the mind of decision-makers and it’s a pain point. Another good thing is you’re relatively self-directed in what to learn and how to build things. However, one issue is not being called in until things get hairy.

I have a chance to build a database that will collect human conclusions from raw data. I want to design this properly so that I can go back and apply machine learning techniques later. Too often, I think people try to squeeze the whole thing out in one go, and that’s bound to be ad-hoc and messy.

My concept is an app that records a person’s decisions: here is some data; what do you want to do? And we have only a handful of choices. The expected data set will easily grow into the tens of thousands eventually. It’s a gold mine on a platter.

This “late-binding of rules,” wherein the domain comprehension remains with the human while initial development is focused on getting the raw data in front of him in a more palatable way, opens the door to future iterations. It is the space of consolidating data, when DBAs have better things to do.

Sniper

Part of an example freewrap bundle is including a program that will:

  • purge the clipboard,
  • kill Paint and Snipping Tool,
  • hide itself from the tray and taskbar, and
  • close the application it was meant to protect

This can be implemented in AutoIt easily. The other side is

exec /long/freewrap/path/sniper.exe &

wm protocol . WM_DELETE_WINDOW {
  exec taskkill /f /im sniper.exe
  exit
}

There was little interest because bandying the actual files around was already giving half the keys to the kingdom.

Catching backspace

A common use case is to backspace into the previous form to re-submit similar data more quickly. This invalidates the idea of keeping a hidden variable between HTTP requests, though. The number would never be incremented. I couldn’t figure out how to force a redirect with JavaScript, so I just used a longer query string and this code:

<script>
document.onkeydown = ProcessBackspace

function ProcessBackspace() {
  var inpKey = event.keyCode;
  if (inpKey == 8) {
    alert('Please click ?var1=x&var2=y link instead.');
  }
}
</script>

Canvas before <canvas>

Brent Welch’s Tcl/Tk book is awesome. His examples include a CGI web server, custom widgets, an asynchronous clock in C, and a scrolling canvas. Any technical book teaching a language ought to have elements like these.

Tcl/Tk enabled me to create a draggable canvas in no time flat. This can be built into a document management application, where the files are protected by freewrap. I’ve finally reached both ends of the book, and now I have the rest of the tome to really learn deeply.

When I reached the conclusion that Tcl/Tk and perl would be my two primary “portable” languages, ActiveState was thinking the same thing: they sell two major offerings for those languages. They have a cloud offering too, so I wonder where Tcl will fall in there.

Notes on building software

After downloading the source of a package, I noticed some repetitions. Here’s the workflow while I was building freewrap 6.6.3:

File extraction:

$ tar -tzf package-1.4.tar.gz
$ tar -xzf package-1.4.tar.gz

The first is to see if the compressed file will explode all over the home directory or if is organized from its own root folder. If the former, then make a folder for it first. Then change into the directory and run the second command.

Documentation:

$ ls
$ less README
$ less win32/README
$ vim configure

I list the directory contents to look for any useful files or a Windows-specific folder. Check out the READMEs and related documentation (FAQ, INSTALL, etc) for configuration options and output paths, especially PREFIX and EXEC_PREFIX type variables.

Building:

$ ./configure --disable-shared --disable-64-bit --enable-threads
$ ./configure --prefix=/home/ettis/local
$ make
$ make install
$ make clean

Here’s the meat of building a package. Configuration sets up variables to reflect your machine. Issuing `make’ spills new files into the build directory. When you’re satisfied, `make install’ shacks them up in your filesystem.

You may not likely get a clean build before `make install.’ In those cases, you will need to fix the problems. After trying something, do `make clean’ followed by `make’ again. Some hints when `make’ fails:

  • What assumptions are made in the Makefile ?
  • Which dependencies can I ignore ?
  • What variables do I need to set ?
  • Do I want a static library ?
  • Will the tool I need be created before `make’ fails ?
  • Check the SourceForge forums
  • Check older versions of the software documentation and cross-reference

Exultation:

Did you just build your own .exe from source? Congrats! Now you can examine people’s source code, rebuild it and possibly contribute back.

Let’s build freewrap 6.6.3

Some notes where I follow the PDF for building freewrap 6.6.3:

  • I renamed “C:\Tcl” to “C:\Tcl—–” to avoid PATH pollution while building Tcl.
  • MinGW setup: Make sure to get the package that installs gcc; this is not part of the default selection.
  • MinGW: /usr/local refers to the same directory as /local
  • I did not have to use –prefix or –exec-prefix
  • For building Tcl and Tk, I used ./configure with –disable-shared, –enable-threads and –disable-64-bit. The third option because my target machines are 32-bit, and I didn’t want to complicate the build process.
  • zlib: remove the _d tests. They are not needed because we are not using the DLL.
  • zip31c: make fails with undefined crc32, but not before zip.exe is created. That’s all we need; copy that over as per PDF instructions.
  • Tktable builds with threads by default; I was worried because I hadn’t specified –enable-threads.
  • tcllib and tklib are real files that you will need to get; search for them.
  • tklib certificate error from Dev’s Xchange, so here’s the URL: http://core.tcl.tk/tklib/home
  • Invoke /usr/local/bin/tclsh86s.exe on the installer.tcl files for tcllib and tklib, not the ones in your extracted source directory. Otherwise, magic folders will appear in your work directory.
  • Ready to build freewrap.exe? I created a directory /home/ettis/fwbuild/tcl8.6/win32 and copied tcl8.6.2/ and tk8.6.2/ there. I’m a single user building for one platform, but the author’s choice reflects a multi-platform organization.
  • Also copy zip31c/ to the same place, because BUILD_DIR and ZIPOBJDIR.
  • You will need a sample .ico file; see Internet. I put mine in freewrap663/build/win32 because that’s my target platform.
  • Be sure to name the .ico file “freewrap” and not “freewrap.ico”. If the latter, the actual filename will be “freewrap.ico.ico” and make will complain of not finding a target for freewrap.ico.
  • Update generic/libraryCopy.tcl for new versions of the software. For example, make will fail on itcl4.0.0 when you installed itcl4.0.1. Edit the hardcoded values.
  • Any time you mess up, type “make clean” before “make”.