Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Accessing SQL Server Table (Access 2002 sp1)

    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

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing SQL Server Table (Access 2002 sp1)

    (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!

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Accessing SQL Server Table (Access 2002 sp1)

    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.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing SQL Server Table (Access 2002 sp1)

    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:

    <font color=blue> <span style="background-color: #FFFF00; color: #000000; font-weight: bold">If CLASS_LEV = "S" Then</span hi> SLOT_DESC.Visible = False _
    Else SLOT_DESC.Visible = True</font color=blue>
    Attached Images Attached Images

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing SQL Server Table (Access 2002 sp1)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •