Accessing SQL Server Table (Access 2002 sp1)

Topic

New Reply

I am trying to test for a value within a table, but cannot quite get the syntax right. This procedure is part of a report and fires when the report opens. It is designed to suppress the display of a textbox depending upon the value of a field that is not actually a part of the report The table is part of the Qry that the report is based upon. Here is the procedure:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Report_Open(Cancel As Integer)

If dbo_RF_CLASS.CLASS_LEV = “S” Then SLOT_DESC.Visible = False _
Else SLOT_DESC.Visible = True

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

dbo_RF_CLASS.CLASS_LEV is the table/field name. It is a table that is linked to an external SQL database.

In the alternative, is there a better way to conditionally suppress the display of a textbox based upon a field value?

Randall Davis
Wichita, KS

Reply | Quote

Replies

(I’m assuming you’re connecting to SQL Server with either an ADP or a linked table within an MDB.)

Just curious – what’s the data type of the field you’re testing? If it’s char (or nchar) and the length of the field is higher than 1, your value will be “S_____” (plus space characters to fill up the remaining length of the field). If that’s the case then you should either change the field type to varchar/nvarchar or change the test to If Left(dbo_RF_CLASS.CLASS_LEV,1) = “S”.

Hope this helps!

Reply | Quote

I am connecting to SQL Server through ODBC within an MDB.

The data type of the field is char, so the length is only one character. Are you saying that my syntax should have worked? I even added the field to the recordset, with no positive result.

Looking back to my original post, I inadvertantly left out important information: When I run my code, I get a error stating “you have entered an expression that has no value”, with an error code of 2427. when I go to debug, the following code is highlighted:

If CLASS_LEV = “S” Then SLOT_DESC.Visible = False _
Else SLOT_DESC.Visible = True

Reply | Quote

Hi Randall,

I think I see what’s happening. Your syntax appears correct. I think the problem is the Data (as Charlotte pointed out earlier).

Just having the table included with your query (view/procedure) doesn’t mean that it will produce the correct value. You may want to try several things. One of the easiest would be a DLooup() to grab the value. You can still use your IF statement to test:

If DLookup(“Class_Lev”,”tablename”,”criteria”) = “S” Then….

Another solution would be to use ADO to find the record, but DLookup is probably easiest in this case.

Hope this helps

Reply | Quote

Is the field part of the report’s recordset, even though it isn’t bound to a control on the report? If it isn’t, this won’t work at all.

Reply | Quote