Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / March 2008

Tip: Looking for answers? Try searching our database.

function

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
patti - 24 Mar 2008 16:15 GMT
Can someone explain what is happening with this modGeneral module? ? I have
hopefully just pulled out the necessary info.

Report “on open” event:

Public Function fnPopulateTable_2()
   Dim strSQL As String
   ......
strSQL = "SELECT…..fnGetPO([ON Order PO Info].[m1 po],[ON Order PO Info].[m2
PO],[ON Order PO Info].[m3 po],[ON Order PO Info].[m4 po]……

modGeneral:

Public Function fnGetPO(po1, po2, po3, po4) As String
   Dim strPO As String
   
   If Not IsNull(po1) Then
           strPO = po1
       Else
           If Not IsNull(po2) Then
                   strPO = po2
               Else
                   If Not IsNull(po3) Then
                           strPO = po3
                       Else
                           If Not IsNull(po4) Then
                                   strPO = po4
                               Else
                                   strPO = "None"
                           End If
                   End If
                   
           End If
   End If
   
   fnGetPO = strPO
End Function

What is the mod looking at for “IsNull”?

Thanks.

patti
Douglas J. Steele - 24 Mar 2008 16:28 GMT
The function accepts four values. If there's a value for the first
parameter, the function returns that value. If the first parameter has a
Null value, but there is a value for the second parameter, the function
returns that value. If thefirst and second parameters both have Null values,
but there's a value for the third parameter, the function returns that
value. If the first, second and third parameters all have Null values, but
there's a value for the fourt parameter, the function returns that value. If
all of the first, second, third and fourth parameters have Null values, the
function returns the string "None".

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Can someone explain what is happening with this modGeneral module? ? I
> have
[quoted text clipped - 41 lines]
>
> patti
patti - 24 Mar 2008 16:45 GMT
Thanks for the speedy reply.

Where is the function looking for the value?
Is it looking at [ON Order PO Info].[m1 po], etc? Is that what becomes the
"strPO"?

> The function accepts four values. If there's a value for the first
> parameter, the function returns that value. If the first parameter has a
[quoted text clipped - 51 lines]
> >
> > patti
Douglas J. Steele - 24 Mar 2008 17:57 GMT
Your SQL is invoking the function through the inclusion of

fnGetPO([ON Order PO Info].[m1 po],[ON Order PO Info].[m2 PO],[ON Order PO
Info].[m3 po],[ON Order PO Info].[m4 po])

That means that the four fields from the table being passed to the function
are [m1 po], [m2 po], [m3 po] and [m4 po]. Since those fields are being
passed in that order to the function, within the function, po1 refers to the
value passed from [m1 po], p02 refers to the value passed from [m2 po], p03
refers to the value passed from [m3 po] and p04 refers to the value passed
from [m4 po]. strPO is simply an internal variable within the function. Once
it's been assigned a value, the function is assigned that value through the
statement

fnGetPO = strPO

so that's what gets back to the query.

The names used within functions really have nothing to do with what's being
passed to the function. The function could just as easily have been written
as either of the following and it would still accomplish exactly the same
thing.

Public Function fnGetPO(w, x, y, x) As String
Dim a As String

 If Not IsNull(w) Then
   a = w
 Else
   If Not IsNull(x) Then
     a = x
   Else
     If Not IsNull(y) Then
       a = y
     Else
       If Not IsNull(z) Then
         a = z
       Else
         a = "None"
       End If
     End If
   End If
 End If

 fnGetPO = a

End Function

or

Public Function fnGetPO(w, x, y, x) As String
Dim a As String

 If Not IsNull(w) Then
   a = w
 ElseIf Not IsNull(x) Then
   a = x
 ElseIf Not IsNull(y) Then
   a = y
 ElseIf Not IsNull(z) Then
   a = z
 Else
   a = "None"
 End If

 fnGetPO = a

End Function

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks for the speedy reply.
>
[quoted text clipped - 61 lines]
>> >
>> > patti
patti - 24 Mar 2008 23:07 GMT
Thank you very much for explaining this to me and for the alternative coding.
You made it make sense.

> Your SQL is invoking the function through the inclusion of
>
[quoted text clipped - 130 lines]
> >> >
> >> > patti
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.