SQL Discrepancies, List Boxes, and Still Trying to Get a Date

<< Click to Display Table of Contents >>

Navigation:  Smart Access 1996-2006 > Nov-1998 >

SQL Discrepancies, List Boxes, and Still Trying to Get a Date

Peter Vogel            
While he teaches, Peter gets peppered with many Access questions.
I just converted an Access Form to an ASP using Access 97's convert to HTML feature, but I'm getting a data access error on the SQL statement that makes up the RecordSource. I've included my code; can you tell me what the problem is?
The good news is that this isn't an Internet problem. Instead, you've hit one of the discrepancies between the version of SQL that Jet uses and the official standard. In your code, you have:

Select Category

From CategoryList

Where Type Like "InStore*"

The problem is the asterisk in your SQL Where clause. Access SQL uses "*" as a wildcard to mean "match any character." Similarly, the "?" is used in Access SQL to mean "match any single character." These characters are familiar to old DOS users as the wildcards used with the DIR command. However, the SQL standard uses a completely different set of characters for wild cards. In the SQL standard, the percentage sign ("%") is the "match any character" wildcard and the underscore ("_") is the "match any single character" wildcard.
When you use ADO code in your ASP to access an Access database, your SQL statements are processed according to the SQL standard. As that processing takes place, you're getting the error as the processor discovers that you have inappropriate wildcard characters. There's no solution to this, unfortunately. The SQL standard wildcards don't work with Jet, so if you use them in your query, your query will stop working with Access. Your choices are either to change the ASP code after it's generated or to alter your queries so that they don't work until you convert to HTML.
I'm trying to build a form to help my sister assign guests at her wedding to tables. I've a got a table called tblAssignments with TableId and GuestId. My form (frmTables) shows a TableId and has a list box of GuestNames. I want to double-click on the list to add a record to tblAssignments, but I can't figure out how to get the GuestId from the list box of GuestNames.
There are a number of ways of doing this, but the one that requires the fewest changes to your existing form requires only a couple of lines of code.
In the DBLClick event of the list box, you can write a routine that captures the selected item in the lstGuests list box, picks up the TableId from frmTables, and then uses a single SQL Insert statement to add a record to the tblAssignments table. The code would be something like this:

Private Sub lstGuests_DblClick(Cancel As Integer)
Dim dbs As Database
Set dbs = CurrentDB()
'Here is some code
dbs.Execute("Insert Into tblAssignments "  & _
  "Values('" & lstGuests & ", " & _
  frmTable.TableId & ");")
Set dbs = Nothing
End Sub

However, this code assumes that you're retrieving the current value of the list box lstGuests. The problem is that the list box contains two columns: The first column is the GuestName, and the second column is the GuestId that must be added to tblAssignments. The GuestId column has been given a width of 0" to hide it so that the list box only displays the GuestName. So what you need to retrieve is the value in the second (hidden) column of the list box.
To get a particular Column of the list box, you use the Column property of the list box. The Column property accepts two parameters: the row and the column of the entry in the list box whose value you want to retrieve. The Column property returns the value at that location in the list box. To determine which row the user clicked on, you must use the list box's ListIndex property, which gives the row that the user selected. Since you know that the GuestId is in column 1, the code becomes:

dbs.Execute("Insert tblAssignments Values('" & _

  lstGuests(1, lstGuests.ListIndex) & ", " & _

  frmTable.TableId & ");")

I want my users to select a date on the calendar. On my form, I have five combo boxes that I want to display the date for each workday of the week that the selected date is in. For instance, if the user selects 7/16/98, which is a Thursday, the first combo box should display the Monday for that week, the second combo box should display the Tuesday for that week, and so on.
What you're after here is a formula that, given a date, returns the date for a specific day in the same week. Here's an expression that, given a date, returns the date for the Sunday of that week (it assumes that the date is stored in the variable dteDate):

DateAdd("d", -WeekDay(dteDate) + 1, dteDate)

The expression uses Access's WeekDay function, which, given any date, returns a number representing the day of the week (1 for Sunday, 2 for Monday, and so forth). The formula then uses the DateAdd function to subtract that number of days (less 1) from the date. DateAdd accepts three parameters: a string indicating what units to use (in this case, "d" for days), the number to be added or subtracted, and the date you want to adjust.
Here's an example for 7/16/98 (a Thursday). For 7/16/98, WeekDay returns 5, representing the fifth day of the week (this is affected by the date settings you've made in Control Panel). Turning the weekday number into a negative and adding it to the date in dteDate with DateAdd gives 7/11/98. This moves us back to the previous Saturday, so the formula reduces the WeekDay value by 1 so that the expression returns the date for the preceding Sunday.
In your list boxes you could set each box to the appropriate date by adjusting the number added to dteDate to move the date from Sunday to subsequent days in the week:

Sunday: DateAdd("d", -WeekDay(dteDate) + 1, dteDate)

Monday: DateAdd("d", -WeekDay(dteDate) + 2, dteDate)

Tuesday: DateAdd("d", -WeekDay(dteDate) + 3, dteDate)

Your download file is called SA9811AA.ZIP in the file SA1999-10down.zip

This is found in theon this page