Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: Complex Stored Procedure in MS Jet 4.0?

    I'm a first time sysadmin for a small company. I'm working with MS Jet OLEDB 4.0. The boss won't buy me SQL Server or even MS Access, so I'm trying to make it all work with Jet 4.0, ADO and VB

    My problem: I have a table 'Applications' which holds data on rental applicants, and a Table 'CommercialAppInfo' which holds one-to-one related records for those Applicants which are companies applying for a commercial lease. I want open an ADO recordset with a dynamically generated field for the full name of the applicant according to these rules:

    (Sorry, forum doesn't appear to accept indentation!)

    If ReportType* MOD 4 <> 3 THEN

    If Len(SpouseFirstName) = 0 then
    DynamicField = LastName & ', ' & FirstName
    ELSE
    DynamicField = LastName & FirstName
    END IF

    ELSE 'A commercial lease Application

    SELECT DynamicField = CompanyName FROM CommercialAppInfo WHERE CommercialAppInfo.RelatedTo = Applications.PKey

    END IF

    *The ReportType Field is an integer field where bits 3 and on are attributes such as roommate or cosigner, and the bits 1 & 2 describe the type of application; commercial, residential or employment.


    Basically, I want the full name to be: {LastName, FirstName[ & SpouseFirstName] | CommercialAppInfo.CompanyName}

    I would like to have this done by a stored procedure that returns a value, as in the following syntax

    Dim loRS as ADODB.Recordsetet loRS = New ADODB.Recordset
    lors.open "SELECT GetName(LastName, FirstName, SpouseFirstName, PKey) AS FullName FROM Applications WHERE ReceiveDate = #3/26/04#"

    Is there a way that I can learn to do such a thing? Many thanks to anyone who is willing to try to help.

  2. #2
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Sorry I can't help you. A stored procedure with no place to store it? My suggestion is to do a search on stored procedures, but perhaps in another section of this forum or try another forum.

    Good Luck!
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  3. #3
    Join Date
    Mar 2004
    Posts
    4

    stored proc

    I'll be storing it in the database with the rest of my stored procs. The issue I am having is that I can only get stored procs with a single SQL statement to work. I've been scouring the net for close to six months on this particular issue. My feeling is that Jet will only allow a single statement in the stored proc, but my hope is that there is some way to do it. I know that this may not be the best forum for this question. There does not appear to be a MS Jet forum, so I am hoping that any MS Access gurus that may frequent this forum will have the knowledge that I am looking for. Disappointingly, when I search this site for 'Stored Procedure Jet', the only result is this post. But I've gotten used to the lack of information for MS Jet, seems like everybody uses SQL Server.
    Last edited by Luke H.; 03-26-04 at 16:00.

  4. #4
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Can you do it with a nested iif statement?

    IIf(expr, truepart, falsepart)

    It gets long and nasty (and may be limited to a certain length?).

    IIf(Len(SpouseFirstName) = 0), DynamicField = LastName & ', ' & FirstName, DynamicField = LastName & FirstName)
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  5. #5
    Join Date
    Mar 2004
    Posts
    4
    You're right, it's long and nasty. Here's the IIf statement that I have:

    Set loRS = loConn.Execute("SELECT " & _
    "IIF(" & fldType & " MOD 4 <> 3, " & _
    "IIF(" & fldSFName & " = '', " & fldLastName & " & ', ' & " & fldFirstName & ", " & fldLastName & " & ', ' & " & fldFirstName & " & ' & ' & " & fldSFName & "), " & _
    "'Comm') " & _
    " AS FullName " & _
    "FROM Applications " & _
    "WHERE PKey BETWEEN 1 AND 200")


    Of course, the Where clause will vary, it's there right now just to limit the number of records returned. Also, I can't find a way to have the false part of the first IIF statement get the Company Name from the related table and return it as the dynamically generated field FullName. Doing it this way, though I think will turn out to be prohibitively ineffecient. You can see why I'd prefer a stored proc!


    However, if you know what SQL code I could put in the false part of the first IIF statement to return the Field from the related Table, I'd love to see it!
    Last edited by Luke H.; 03-26-04 at 17:05.

  6. #6
    Join Date
    Mar 2004
    Posts
    4

    Nasty, Nasty nested iif's!

    Sorry, I lied! I said I couldn't find out how to return obtain the related field and return it. Well, I thought I had the right syntax, but it wasn't working. Turns out, (of course) that I made a syntax error. Here's the code I'll be using for this purpose unless (and I sure hope) someone can show me a more effecient way.

    'modular code....

    Public Const tblApps = "Applications"
    Public Const fldPrimaryKey = "PKey"
    Public Const fldLastName = "LName"
    Public Const fldFirstName = "FName"
    Public Const fldSFName = "SFName"
    'Other fields omitted

    Public Const tblCommercialInfo = "CommInfo"
    public const fldRelatedTo = "RelTo"
    public const fldCompany = "Cmpny"
    'Other fields omitted

    'Dynamically Generated field
    Public Const fldFullName = "FullName"

    Public Enum eReportTypes
    rtResidential = 1
    rtEmployment = 2
    rtCommercial = 3
    rtOther = 4

    rtCreditCriminal = 8
    'All the other attributes omitted
    End Enum


    Public Const BaseReportAttribute = rtCreditCriminal

    Public Const FullNameField = "IIF(" & fldType & " MOD " & BaseReportAttribute & " <> " & rtCommercial & ", " & _
    "IIF(" & fldLastName & " = '' OR " & fldFirstName & " = '', " & _
    fldLastName & " & " & fldFirstName & ", " & _
    "IIF(" & fldSFName & " = '', " & _
    fldLastName & " & ', ' & " & fldFirstName & ", " & _
    fldLastName & " & ', ' & " & fldFirstName & " & ' & ' & " & fldSFName & ")), " & _
    "(SELECT " & fldCompany & " FROM " & tblCommercialInfo & " AS R2 WHERE R2." & fldRelatedTo & " = R1." & fldPrimaryKey & ")) " & _
    "AS " & fldFullName

    Public Function SQLString(psFields As String, psTable As String, Optional psWhereClause As String) As String
    If LenB(psWhereClause) > 0 Then
    SQLString = "SELECT " & psFields & " FROM " & psTable & " AS R1 WHERE " & psWhereClause
    Else
    SQLString = "SELECT " & psFields & " FROM " & psTable & " AS R1"
    End If
    End Function

    calling code....

    Set loRS = goConn.Execute(SQLString(FullNameField, tblApps))
    Last edited by Luke H.; 03-26-04 at 19:42.

  7. #7
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    A syntax error? I've never had those, though sometimes I hear about others having that problem.

    After scanning your second to last post, I was looking for the first comma after the IIF. Looks like you got it sorted. Well done. And sorry I can't give you any help.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

Posting Permissions

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