[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

Using query aliases as parameters for a function

Hi
I have a crazy IF statement in excel that I'm trying to convert to a MS Access query.  I have been able to convert all the other IF statements of this excel worksheet except for this last one, which basically in Excel, searches all the other calculated fields for certain contents in a certain order and becomes the text of that field. In Excel:

=IF(W43="Full Title","Full Title",IF(V43<>"",V43,IF(D43="Refinance","Full Title",IF(P43<>"",P43,IF(S43<>"",S43,IF(R43<>"",R43,IF(Q43<>"",Q43,T43)))))))

All the cells here, like W43 etc, are calculated expressions (IIF statements) that I did in the query.   I've thought about putting a function in, but it keeps giving me errors because I don't think it's receiving the aliases I have for these calculated expressions as parameters.  (This is all in one query)

Function TitleType(strFlex, strPurchase, strFinType, strFact, strFullTitle, strPropertyReport) As String
    Dim Title As String
   
    If Flex = "Full Title" Then
    Title = "Full Title"
    ElseIf Purchase <> " " Then
    Title = "Purchase"
    ElseIf FinType = "Refinance" Then
    Title = "Full Title"
    ElseIf Fact <> " " Then
    Title = "Fact"
    ElseIf FullTitle <> " " Then
    Title = "Full Title"
    ElseIf PropertyReport <> " " Then
    Title = "Property Report"
    Else
    End If
Title = TitleType
End Function

Please help.
0
chrisamuel
Asked:
chrisamuel
  • 6
  • 5
1 Solution
 
harfangCommented:
One important difference between Excel and Access is the way an empty value is treated. In Excel, it's "" (vbNullString), in Access, it's Null. So, when converting:
    FooBar = ""               -->      IsNull( FooBar )
    FooBar <> ""             -->      Not IsNull( FooBar )

So this works:

    = IIf( Flex = "Full Title" , Flex, IIf( Not Isnull(Purchase), "Purchase", IIf( ......

But you have another function in Access that deals with Null values, called Nz(), "null to zero", which can be used instead. The trick is that Nz can be used for "null to anything":

    = IIf( Flex = "Full Title" , Flex, Nz(Purchase, IIf( FinType = "Refinance", "Full Title", Nz( Fact, ....

Well, you get the idea.

Good Luck!

0
 
chrisamuelAuthor Commented:
Well, I don't think that's the problem I'm having. Those names, (Flex, Full Title) are aliases for calculated expressions I've put in other fields of the query, so when I build it like that, it asks me to enter in parameter values. So now I'm trying(just to see if it works) entering all of those expressions for the aliases in that entire expression. Because I can't get the function to work...
0
 
Arthur_WoodCommented:
try this for the body of the function:

If strFlex = "Full Title" Then
    Title = "Full Title"
    ElseIf strPurchase <> " " Then
    Title = "Purchase"
    ElseIf strFinType = "Refinance" Then
    Title = "Full Title"
    ElseIf strFact <> " " Then
    Title = "Fact"
    ElseIf strFullTitle <> " " Then
    Title = "Full Title"
    ElseIf strPropertyReport <> " " Then
    Title = "Property Report"
    End If
 

you are NOT using the values of the arguments that you are passing to the function .

AW
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
chrisamuelAuthor Commented:
I keep getting a "Wrong number of arguments used with function in query expression 'TitleType()'.  Also I did this
Function TitleType(strFlex...
Dim Title As String
  'and this at the bottom
Title = TitleType
End Function

But you did not...Do I not need to do this?
0
 
chrisamuelAuthor Commented:
"you are NOT using the values of the arguments that you are passing to the function ."

Shouldn't I be? I'm trying to enter as parameters the values I received in those calculated expressions named Flex, Purchase...etc
0
 
chrisamuelAuthor Commented:
Well now I got so frustrated that I made that query a Make Table query and am using another query to calculate off of that. But lo and behold I am not getting the right results...the title field is coming up blank when there truly are values in them.  Can you guys tell if there's anything wrong with this statement:

Title: IIf([Flex]="Full Title","Full Title",IIf([Purchase] Is Not Null,[Purchase],IIf([FinType]="Refinance","Full Title",IIf([Fact] Is Not Null,[Fact],IIf([FullTitle] Is Not Null,[FullTitle],IIf([PropertyReport] Is Not Null,[PropertyReport]," Check Query"))))))
0
 
harfangCommented:
Well, you can simply a bit with Nz, but as far as I can tell, there is no obvious syntax error. There might be logical errors, but you would have to provide your table somehow...

Title: IIf([Flex]="Full Title","Full Title",Nz([Purchase],IIf([FinType]="Refinance","Full Title",Nz([Fact],Nz([FullTitle],Nz([PropertyReport]," Check Query"))))))

As for the other comments:

> "you are NOT using the values of the arguments that you are passing to the function ."

This referts to the function declaration. If you start with:
    Function TitleType(strFlex, strPurchase,
You declare the parameters as "strFlex", "strPurchase", etc.... Later in the function, you simply use "Flex", "Purchase", which do not refer to the same thing. They might be defined otherwise (through the query the form is based on), but these are NOT the parameters to the function.
Hence, "you are NOT using [...] the arguments".

> "But you did not...Do I not need to do this?"

Sorry about that, this is another error. Once you see one error, it's difficult to keep checking for others :)
Your line:
    Title = TitleType
Should have been:
    TitleType = Title

You have just spent many lines creating the variable "Title", which sould then be returned to the function, not the other way around :)

Well, enough here. If you like, compact the database, remove any confidential information and make it available somewhere. I'll take a look at it.

Good Luck
0
 
chrisamuelAuthor Commented:
If you have time....

www.geocities.com/chrisamuel@sbcglobal.net/FactRep.zip

qryFact2 runs the main query to find values off two Apps table and posts to Table ValuesQuery and then Query1 runs to find Title type.
0
 
harfangCommented:
I'll look at it in about one hour...
0
 
harfangCommented:
Well, I owe you an apology. Maybe even two :)

First of all, I should have taken your sample seriously. You wanted to test agains a single space, e.g. <> " ", whch I read as a test against the null string, i.e. <> "". The null string is normally not allowed in a field, only the value Null is. Furthermore, it is difficult to store a single space in a text field, because the interface will alwas run Trim() against it. One way to get spaces in fields is to run an update query, which you do in the database...

Second, I should have spotted the mistake of "Title = TitleType" instead of "TitleType = Title" in your function. After applying AW's correction, your function should have worked, called like this:
    TitleFund: TitleType_BOTH([Flex],[Purchase],[FinType],[Fact],[FullTitle],[PropertyReport])

You might have forgotten one parameter (wrong number of arguments), but after that, it would have worked. For reference, here is the function, with a second variant based on the (wrong) assumption that the fields would be Null, and a third that works whether you use single spaces or Null values for empty fields

In your function you declared parameters with the "str" prefix, which would imply string parameters. In the other variants, I did change that to "var" (Variant) in order to accept Null.

----------------------------------------------------------------------------------------
Option Compare Database
Option Explicit    ' (never remove this line! :)

' Original function, with one line added
Function TitleType_ORI( _
    strFlex As String, _
    strPurchase As String, _
    strFinType As String, _
    strFact As String, _
    strFullTitle As String, _
    strPropertyReport As String _
    ) As String
   
    Dim Title As String
   
    If strFlex = "Full Title" Then
        Title = "Full Title"
    ElseIf strPurchase <> " " Then
        Title = "Purchase"
    ElseIf strFinType = "Refinance" Then
        Title = "Full Title"
    ElseIf strFact <> " " Then
        Title = "Fact"
    ElseIf strFullTitle <> " " Then
        Title = "Full Title"
    ElseIf strPropertyReport <> " " Then
        Title = "Property Report"
    End If
   
    TitleType_ORI = Title
   
End Function
 
' Identical function for Null values and not " "
Function TitleType_NULL( _
    varFlex, _
    varPurchase, _
    varFinType, _
    varFact, _
    varFullTitle, _
    varPropertyReport _
    ) As String
   
    Dim Title As String
   
    If varFlex = "Full Title" Then
        Title = "Full Title"
    ElseIf IsNull(varPurchase) Then
        Title = "Purchase"
    ElseIf varFinType = "Refinance" Then
        Title = "Full Title"
    ElseIf IsNull(varFact) Then
        Title = "Fact"
    ElseIf IsNull(varFullTitle) Then
        Title = "Full Title"
    ElseIf IsNull(varPropertyReport) Then
        Title = "Property Report"
    End If
   
    TitleType_NULL = Title
   
End Function
 
' A variant working for both cases
Function TitleType_BOTH( _
    varFlex, _
    varPurchase, _
    varFinType, _
    varFact, _
    varFullTitle, _
    varPropertyReport _
    ) As String
   
    Dim Title As String
   
    If varFlex = "Full Title" Then
        Title = "Full Title"
    ElseIf Len(Trim(varPurchase)) Then
        Title = "Purchase"
    ElseIf varFinType = "Refinance" Then
        Title = "Full Title"
    ElseIf Len(Trim(varFact)) Then
        Title = "Fact"
    ElseIf Len(Trim(varFullTitle)) Then
        Title = "Full Title"
    ElseIf Len(Trim(varPropertyReport)) Then
        Title = "Property Report"
    End If
   
    TitleType_BOTH = Title
   
End Function
----------------------------------------------------------------------------------------

As for the expression in the query, since the values are either filled or " ", it should read:

Title: IIf([Flex]="Full Title","Full Title",IIf([Purchase]<>" ",[Purchase],IIf([FinType]="Refinance","Full Title",IIf([Fact]<>" ",[Fact],IIf([FullTitle]<>" ",[FullTitle],IIf([PropertyReport]<>" ",[PropertyReport]," Check Query"))))))

So finally, this looks again very much like your original Excel expression.

Finally, I suspect that you do not want to keep the update query as intermediate step, so here is the global query producing the title directly:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblApps1.*, IIf(([State] In (Select FactStates From tblFactCriteria) And ([PurposeCd]>800 And [OrigAmt]<250000 Or [PurposeCd] Between 200 And 799) And [OrigAmt]<250000),"FACT") AS Fact, IIf([State] In (Select PRStates From tblPropertyReportStates) And [FinType]<>"Refinance" And [PurposeCd]>=200 And [OrigAmt]<250000,"Property Report") AS PropertyReport, IIf([FinType]="Refinance" Or [State]="FL" Or [State]="VT" Or [OrigAmt]>=250000 Or [State]="OR" And [OrigAmt]>250000,"Full Title") AS FullTitle, IIf([LoanProgId]=745 And [LoanProgId]=746 And [OrigAmt]>50000,"Full Title") AS 125FT, IIf([LoanProgId]=745 And [LoanProgId]=746 And [OrigAmt]<50000,"Property Report") AS 125PR, IIf([PurposeCd]<200,"Purchase") AS Purchase, IIf([LoanProgId]=691,"Full Title") AS Flex, IIf([Flex]="Full Title","Full Title",Nz([Purchase],IIf([FinType]="Refinance","Full Title",Nz([Fact],Nz([FullTitle],Nz([PropertyReport]," Check Query")))))) AS Title
FROM tblApps1
WHERE (((tblApps1.CreationDtTm) Between [Enter Start Date] And [Enter End Date]));

Paste it into a new query, and you will have both the intermediate values and the final "Title"

Cheers :)
0
 
chrisamuelAuthor Commented:
Actually, I hadn't had much experience with writing functions (my first one!) so when I first didn't see what I was supposed to I just (trigger happy, I guess) started changing lines around in the function to see if it would make a difference. I actually did (originally) have it has Title Type = Title.  I'm just so relieved it works, thank you so much for taking a look at that.  I haven't said much because I'm still studying and trying to absorb the rest of what you wrote for future reference.  
Also,---->
 "Well, I owe you an apology. Maybe even two :)

First of all, I should have taken your sample seriously. "   HAHA, this line made my day...if only I could get the rest of the men in my lives to say that.
0
 
harfangCommented:
It's easy in a chat room, it can be almost impossible in private life... but I'm glad I made your day!
Cheers:)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now