In this month’s issue, Peter tackles handling of quotes,  using the RecordsetClone on a form, violating Windows user interface standards, data entry forms, and how to find the First Monday and Last Friday of a month.

I can’t figure this out. I’ve created a DLookup function and, for the Where clause part of it, I entered “[Department] = Forms!frmDept.txtDeptNumber.” This is supposed to look up the department name for the department number displayed on the form. But it doesn’t work!

You’re not being dumb — or if you are, you’ve got lots of company. This problem is one that turns up very frequently in the Access newsgroups. The problem starts when you enclose the string that makes up your Where clause in quotes. In VBA, whatever’s between the quotes is exactly what’s going to be passed to the function. In your example, what’s going to be passed is:

[Department] = Forms!frmDept.txtDeptNumber

Notice that the value of the field txtDeptNumber isn’t what’s being passed. What’s being passed are the letters F-o-r-m-s-!-f-r-m, and so on. This Where clause fails for a number of reasons, not the least of which is that you probably don’t have a department with the number Forms!frmDept.txtDeptNumber.

The solution to this problem is to build a string with the value for the field in it rather than the name of the field. You do that by making sure the reference to the field isn’t inside the double quotes:

"[Department] = " & Forms!frmDept.txtDeptNumber

Because the Forms!frmDept.txtDeptNumber isn’t inside the quotes, it gets evaluated and the value of the expression is appended to the string. Assuming that the field txtDeptNumber on the form frmDept contains the number 20, you’d get this passed as the Where clause:

[Department] = 20

I don’t know if your Department field is a text field or not. If the field is a text field, then the value must be a string value — that is, enclosed in quotation marks. So, the question now becomes how to embed quotation marks inside a quoted string. There are at least three different ways to do this, but there’s only one of the three that I like.

The first method takes advantage of VBA. It also assumes that the quotation marks must be double quotes (and not single quotes). To embed double quotes (“) in a quoted string, you put them in the string twice. Wherever VBA runs across two double quotes together in a quoted string, it doesn’t assume that the quotes represent the end of the string. Instead, VBA embeds a single double quote in the string. Continuing with your example, you could do this:

"[Department] = """ & _

    Forms!frmDept.txtDeptNumber & """"

Doing that would pass this to the Where clause:

[Department] = '20'

I find this plethora of double quotes hard to read. I almost always get the number of quotes wrong, which usually generates a syntax error and reduces me to painfully counting characters to find where I’ve gone wrong. However, this is probably the most standard solution (most languages support the “two quotes together count as one embedded quote” rule) and so would port well.

The second solution takes advantage of SQL and assumes that the quotes can be single or double quotes. Microsoft’s implementation of SQL, for instance, lets you use double and single quotes for enclosing your strings. However, the ANSI standard specifies that strings are to be enclosed in single quotes (‘). Microsoft’s use of both quotes is another example of the Redmond giant’s “embrace and extend” policy. And, of course, if you did use double quotes in all your programs and therefore would have to rewrite them all if you wanted to shift database vendors — well, maybe you should just stick with Microsoft.

To get back to your problem, in order to take advantage of this feature of Microsoft SQL, you could use single quotes inside of the double quotes to delimit strings within your Where clause:

"[Department] = '" & Forms.frmDept.txtDeptNumber & "'"

The resulting clause passed to the DLookup function looks like this:

[Department] = '20'

Because the Where clause is eventually processed by SQL, this use of single quotes is acceptable. While I like this solution (it’s the one I would use), it’s not 100 percent standard and might not port well.

The third solution (and the one I like the least) is to use the Chr function to add quotes to your string. The Chr function returns the ASCII character that corresponds to the numeric value passed to it. So, for instance, passing 34 to the function causes it to return double quotes. Your code now looks like this:

"[Department] = " & Chr(34) & _

  Forms.frmDept.txtDeptNumber & Chr(34)

My personal feeling is that this code is longer and harder to read than the previous solutions, but does have the advantage of being a common (and, therefore, well- known) programmer’s trick.

I’m creating an audit file that users will be appending records to. Once a record is entered, the user isn’t allowed to go back and change it for any reason. I’ve written a bunch of code but there always seems to be a way to get around it. Is there a simple solution?

There is a no-code solution to this problem. If you set the form’s Data Entry property to True, your form will open displaying a blank record. Users will only be able to add records using your form. In order to further prevent your users from changing the records that they’ve entered in the current session, also set the Allow Deletions and Allow Edits properties to No. The form won’t generate any error messages when your users try to change or delete records, but these settings will prevent them from making changes.

I need to be able to determine the last Friday and the first Monday of a month. Is there an easy way to do this?

I don’t know about easy. This is one of those questions that results in people sending in lots of better answers than I can come up with. In the meantime, here’s my suggestion.

If you use the WeekDay function, you can determine what day of the week any given date is. For instance, this call to WeekDay returns a 3:

    WeekDay(#12/31/96#)

A quick look at the calendar shows that the third day of the week, Tuesday, was the last day of 1996. Using 3 as Tuesday assumes that you use Sunday as the first day of the week. You can specify which day to use as the first day of the week by passing a second parameter to the function. VBA provides a set of predefined constants for this (in Access Basic, you’ll have to use the actual numbers). If you want to use Monday as the first day of the week, you’ll call the WeekDay function like this:

WeekDay(#12/31/96#, vbMonday)

If you do, the function returns 2 as the last weekday of 1996, because Tuesday is the second day of the week if Monday is the first.

The next thing to do is find the last day of the month. My favorite trick to find this is to subtract one day from the first day of the next month. So, the last day of February 1996 can be determined by:

   Dateadd("d",-1,#03/01/96#)

This, correctly, returns 02/29/96. Be warned that this isn’t the fastest way to find the last date of the month, it’s just the simplest. If you’re doing a lot of date calculations and need a very efficient routine, you might want to check out Bill Shadish’s “Dr. VB” column in our sister publication, Visual Basic Developer.

With what I’ve covered so far, you can determine the weekday of the last day in February with:

   WeekDay(Dateadd("d",-1,#03/01/96#), vbSunday)

A routine to walk backwards through all the dates in February until the first Friday is found would look like this:

  For intCount = -1 to ­7 Step ­1

If WeekDay(Dateadd("d", intCount, #03/01/96#), _

vbSunday) = vbFriday Then

Exit For

End If

Next intCount

The routine to find the first weekday in a month would start with the first day of the month and walk forward.

Notice that I’ve specified Sunday as the start day. If you don’t do this, you’ll pick up the first day of the week as specified in Windows’ internationalization settings and might get an inappropriate answer.

With all this knowledge, I put together the routine in Listing 1 to find the first or last date in the month for any day of the week. You pass the routine a month, a year, the predefined constant for the day you want to find, and a direction (“First” or “Last”). The routine gets the date for the first day of the appropriate month, and then walks backwards or forwards until it gets a match on the day you requested. To find the last day Friday of January 1998, you would call the routine like this:

FindWeekDay(1,98,vbFriday,"Last")

To find the first Monday in February, you would use:

FindWeekDay(2,98,vbMonday,"First")

Listing 1. A routine to find the date for the first or last specified weekday in a month.

Function FindWeekDay(intMonth As Integer, _

intYear As Integer, intFindDay As Integer, _

strDirection As String)

Dim intCount As Integer

Dim intIncrement As Integer

Dim intWorkMonth As Integer

Dim intWorkYear As Integer

Dim dteWorkDate As Date





If strDirection = "First" Then

intWorkYear = intYear

intWorkMonth = intMonth

intIncrement = 1

Else

If intMonth = 12 Then

intWorkMonth = 1

intWorkYear = intYear + 1

Else

intWorkMonth = intMonth + 1

intWorkYear = intYear

End If

intIncrement = -1

End If

dteWorkDate = DateSerial(intWorkYear, intWorkMonth, 1)





Do While WeekDay(DateAdd("d", intCount, dteWorkDate), _

vbSunday) <> intFindDay

intCount = intCount + intIncrement

Loop

FindWeekDay = DateAdd("d", intCount, dteWorkDate)

End Function

And Yet There is More

See  Positioning a Record on a Form for the rest of this Access Answers article