• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 931
  • Last Modified:

How do I refer to multiple return values from a function using the query design view??

By Creating a Public Type I have created a function that returns two values, "Changed" as Boolean, and "Changes" as String.

I am calling my function from the "Field" line (Top Line) in query design view as follows:

Changed:CheckForChanges([DateToCheckAgainst],[DateA],[DateB],[DateC])

Attempting to refer to just one of the return values either by

Changed:CheckForChanges.Changed([DateToCheckAgainst],[DateA],[DateB],[DateC])

or

CheckForChanges([DateToCheckAgainst],[DateA],[DateB],[DateC]).Changed

results in "Sytax Error" warnings form the query designer.

I would also like to utilize the second return value from the function in another field.

Any help?? TIA

0
Spitfire6
Asked:
Spitfire6
  • 19
  • 9
  • 6
  • +1
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You can't really do that.  You Function can only return one value ... what ever you set the function to in the code.  What you are doing above is actually passing those date values to the function .
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Post the code for the function.

Typical here:

Public Function CheckForChanges(date1, date2, date3, date4) As Variant
  ' ** some code here
CheckForChanges = <Some Value based on some calculation, etc>

End Function

mx
0
 
Spitfire6Author Commented:
Here you go... Thanks...

Select Case will not work for me in the end, that is, if I can get this to work at all, because select case will jump out on the first match. I really need to build a string that lists all the matches found. No matter.

I think I can accomplish what I need to do by using a second function, but trying to do it this way a another great learning experience!


Public Type DateChanges
  Changed As Boolean
  Changes As String
End Type
 
Function CheckForChange(CheckDate As Date, Optional Vacate As Date, Optional Court As Date, _
Optional Writ As Date, Optional Trial2 As Date, Optional Agreed As Boolean) As DateChanges
   
   Select Case CheckDate
   
      Case Is = Vacate
          CheckForChange.Changed = True
          CheckForChange.Changes = "Vacate"
      Case Is = Court
          CheckForChange.Changed = True
      Case Is = Writ
          CheckForChange.Changed = True
      Case Is = Trial2
          CheckForChange.Changed = True
      Case Is = Agreed
          CheckForChange.Changed = True
      Case Else
          CheckForChange.Changed = False
    End Select   
   
   
End Function

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Interesting.  Sort of like a mini class module with two properties.

Still, the function can only return one value to the query at a time in the query expression.  Even if it could return two, how would you use both values in the query expression ?

mx
0
 
Spitfire6Author Commented:
Hi mx,

Just fooling around I added

MsgBox (CheckForChange.Changed & "' " & CheckForChange.Changes)

to the end of my function. The message box contained the expected message.

How would I use both values?? First I am using the first return value as criteria. Assuming this value(Changed)  is TRUE, I had then hoped to use the data that was already sent to the function to give us a list of "CHANGES"., hoping to use that string created within the function to display in another field of the query... Something like CheckForChange.Changes .

This is all going toward a report that shows all dates changed on a certain date... a "What Court Dates did we change today? report. In tha app, whenever a date is changed the date and time of the change is also recorded.

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
The Public Type and Function As Type is kind of cool - Not something I've done recently that I can remember.

Sure ... the MsgBox will display both values because you are in code.

Why not have two different query cells where each one grabs one of the two function values?  Or did you try concatenating the Function in the query cell?

mx

0
 
Spitfire6Author Commented:
"Why not have two different query cells where each one grabs one of the two function values?  Or did you try concatenating the Function in the query cell?"

That's exactly what I had hoped to do.

Between our postings I just tried concatenating the two return values into a third variable, and also as the function name.. that is..

CheckForChange = CheckforChange.Changed & ", " & CheckForChange.Changes

Figured I could then contain this function within another that would split out the values.

Concatenating resulted in this error: Compile Error: Only User Defined Types defined in public object modules can be coerced to or from a variant or passed to late-bound functions.

Whew!!!!!!
0
 
Spitfire6Author Commented:
I think I have approached this from the wrong angle. What I need to do is run through all my dates that are submitted to the function and build a string with the matching data. This string will be the return value for the function. Then as criteria (matches yes or no) just look for a "Non-Empty" string.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Whew is RIGHT .... never have seen that message before!

Re
CheckForChange = CheckforChange.Changed & ", " & CheckForChange.Changes

Seems the catch is that ... you need to pass the dates.  But, passing the dates AND referring to the .Changes and/or .Changed at the same time is not going to fly.

I'm going to point Leigh Purvis to this Q ... his expertise will shed more light ... stand by for a bit.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Leigh s/b checking in a bit ...

mx
0
 
Spitfire6Author Commented:
Thanks, I'll be here.. 4 PM in Texas and I work from home...

Mike

0
 
Leigh PurvisDatabase DeveloperCommented:
Hi all.

Thanks for the intro MX (and feel free to point me to ones like this lol - a truly decent question is so rare to find! :-)
Though there isn't actually a "fix" as such.  You've hit a fundamental limitation.
I'll elaborate. :-)

Essentially... I'm glad you've found the Type function return method Spitfire.  I like to use the technique myself and have suggested it here and there a few times.
Essentially I sum it up as this - there are two real ways to return more than one value using a function - return a Type or using return parameters.

Of course you can concatenate return values into a single String which is returned by the function - but from a purely programming point of view - that's just nasty. ;-)
The problem you're faced with is that this *isn't* a purely programming perspective.

Using a Type to have a function return multiple values can replace the need for a dedicated class.
It can make efficient programming sense to objects calling that function - referring to different elements of the function (and Type).
However being called from Jet SQL - these elements methods just aren't exposed.

Consider the analogy of calling form control properties - the classic example when people attempt to reference the column of a Combo control as criteria...
SELECT * FROM TableName WHERE FieldA = Forms!FormName!ComboName.Column(1)
It fails simply because it can't be evaluated in a query.

The Expression service is, technically speaking, supplied by Jet - but it is Access which binds it together.  It allows the interpretation of Form objects, VBA Functions that aren't shared in the Jet library and indeed custom VBA functions.  (For example try referring to a nonshared or custom function from an application other than Access using Jet data... Nada).

But the Expression Service even within Access has its limits.
Want a reasonable guide?  The Eval function.  (Take care here!!!)
?Eval("YourFunction(Param1).Return1")
If you're *lucky* it'll just fail. lol (i.e. Access might actually CRASH - so don't so it in anything important!!!).
It simply can't be resolved as an expression.
Consequently Jet SQL (queries) can't use it either.
It's simply a divide between the two - SQL Vs VBA - that can't be crossed directly.

So - alternatives?
You've already been thoughful about this I can see.
Yes - in your example there's a Boolean and String result.
You can take the presence of the String to imply the boolean value.  Great.
But what about next time - future implementations.
Consider a project I'm working on right now actually.  A function returns a Type like this

Public Type sdPost
    PostID As Long         'ID of new / previous Post record
    IsNew As Boolean       'True if returned ID is new, false if existing to complete
    PostDate As Date       'Date Time of the post record
    PostPartID As Long     'The *current* active post part ID
    SubPosts() As Integer  'Array of ID's of subpost records
End Type

Now I don't have any need to retrieve its values directly into a query - it's only references in code... but suppose anyway.
Obviously with several types - presence of one can't infer another.
Concatenation - the other option you've cited - might be an option.  (Though notice that the last element is an array - so that could get messy!)

I understand what you seek.  Efficency.
You're calling a function in a query for a particular calculated field value.  That function performs one calculation and yet can return more than one value.  You don't want to have to waste resources making another call to a similar function, running the same calculation, to get the other value.

One option that is open to you - is to have the function accept another parameter.
Obviously in an expression you can't return the value from that parameter - but you can use it to direct the function to return one value or the other.
So what?  Well - then you need to make sure that if you're passing the same arguments again that have just been passed - you don't re-run the calculation, but just return the *other* value this time - as directed by the new argument.

A reeeally simple example.

Public Type MySplit
    Value1 As Integer
    Value2 As Integer
End Type

Function fSplitType(varID) As MySplit
   
    'Run the calculation to determine values
    If varID = 1 Then
        fSplitType.Value1 = 1
        fSplitType.Value2 = 2
    Else
        fSplitType.Value1 = 3
        fSplitType.Value2 = 4
    End If

End Function

I'm sure you can see how that would work.  If the parameter passed is 1 it returns 1, 2 - otherwise 3, 4.
We want to avoid the "calculation" (I know it's trivial here).
So we have the function itself return a variant - but store the Type calculation within.

Function fSplitType2(varID, intValue As Integer)

    Static varLastID
    Static varLastSplit As MySplit

    'Check if there's any need to run the calculation
    If varLastID <> varID Then
        varLastID = varID
        'Run the calculation to determine values
        If varID = 1 Then
            varLastSplit.Value1 = 1
            varLastSplit.Value2 = 2
        Else
            varLastSplit.Value1 = 3
            varLastSplit.Value2 = 4
        End If
    End If

    'Determine which value to return in the Function
    If intValue = 1 Then
        fSplitType2 = varLastSplit.Value1
    Else
        fSplitType2 = varLastSplit.Value2
    End If

End Function

That way you call the function
fSplitType2(1, 1) to get Value1
or
fSplitType2(1, 2) to get Value2
But it will retrieve the second value without re-performing the calculation!

I've used Static variables here.  You could use module level variables instead of course if you wanted to be neater.
Once you were using module level variables - you could always use separate functions to retrieve values if you wanted.  But I prefer to perform logic within procedures rather than have to remember different function names to call. :-)

It's not as neat as using a Type return of course - and I'd recommend the Type every time just in code (unless you wanted to move it all to a class due to complexity) - but we have to find workarounds for queries.
0
 
Spitfire6Author Commented:
Thanks LPurvis. Now that is an answer!

I need to digest this, and a couple of home-brews this evening. I'll press on in the AM.

Thanks so much both you guys... talk to you tomorrow.

Mike

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"and feel free to point me to ones like this lol"

An email address would be HANDY :-)

Yes ... THAT ... is an answer!!  I need digestion also - prior to the home brews!!

Thank you Leigh ....

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok ... so:

"there are two real ways to return more than one value using a function - return a Type or using return parameters."

1) However, only one value can be returned at a time ... per function call ... code or otherwise ... or am I missing something ?

2) In SQL ... you simply cannot do this:

Expr1 : fSplitType.Value1  

or Expr : Forms!FormName!ComboName.Column(1)  

but Forms!FormName!ComboName  does work of course.

You just cannot refer to the Properties (Column(1), Value1 )

Right?

mx
0
 
Leigh PurvisDatabase DeveloperCommented:
The crux of the situation is what can be considered a code object.
For example - arrays and collections - classic code objects.
Split fails in queries - so that seems nicely intuitive.
Similarly the Column collection of a combo fails as we know.

Custom function multiple type elements are not part of an expression which can be evaluated - class references don't work either - as would be expected.
Objects and Functions are what it's all about.  (Single return value functions that is ;-)

You can, however, retrieve certain properties as permitted by the expression service.
e.g.
SELECT Forms!FormName.Name
will return the text
FormName
because the form's properties are exposed.
Similarly with controls (as we take the Value property all the time when you think about it).
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
This is what I've been doing for several years - using a Collection ... to Set and Get multiple values,  where Set's are done in Code and Get's can be done from code or SQL.

So, to Set a value:

Call SSF_SetGetParm("Set", "EntityID", me.txtEntityID)

To Get (retrieve) the same value:

 <SomeVariable> = SSF_SetGetParm("Get","EntityID")

or in SQL:

Expr1 : SSF_SetGetParm("Get","EntityID")

or

WHERE  SomeField = SSF_SetGetParm("Get","EntityID")

I've used this scheme in 1000's of places ... seems to work very well.

mx
Option Compare Database   
Option Explicit           
 
    Public cSetGetParm As New Collection    'Used in SSF_SetGetParm()
 
 
Public Function SSF_SetGetParm(sOp As String, Optional sParmName As String, Optional vParmVal As Variant) As Variant
 
'ExtName:Set Get Parameter
'Actions:Sets a parameter; Retrieves a parameter
 
'Ex Call: /
 
    'Call SSF_SetGetParm("Set", "EntityID", sEntityID)
    'SSF_SetGetParm("Get","EntityID")
'-----------------------------------------------------------
 
    On Error GoTo SSF_SetGetParm_Err
    Select Case sOp
        Case "Set"
            cSetGetParm.Remove sParmName
            cSetGetParm.Add vParmVal, sParmName
            SSF_SetGetParm = True
        Case "Get"
            SSF_SetGetParm = cSetGetParm(sParmName)
        Case "adiDisplayItems"
            Dim cItem As Variant
            For Each cItem In cSetGetParm
                Debug.Print cItem
            Next cItem
        Case Else
            MsgBox "Invalid operation passed to function: " & sOp, 16, "Error In Set/Get Parmeter Function SSF_SetGetParm"
            SSF_SetGetParm = False
    End Select
 
SSF_SetGetParm_Exit:
    Err.Clear
    Exit Function
    
SSF_SetGetParm_Err:
    Select Case sOp
        Case "Set"
            Select Case Err.Number
                Case 0, 5
                    Resume Next
                Case Else
                    Beep
                    MsgBox "Operation: Set" & Chr(13) & "Parameter: " & sParmName & Chr(13) & "Value: " & vParmVal & Chr(13) & "Error Number: " & Err.Number & Chr(13) & "Description: " & Err.Description, 16, "Error In Set/Get Parmeter Function SSF_SetGetParm"
                    SSF_SetGetParm = False
                    Resume SSF_SetGetParm_Exit
            End Select
        Case "Get"
            Select Case Err.Number
                Case 0, 5, 3420, 2467, 3167
                    Resume Next
                'Case 5
                    'MsgBox "Operation: Get" & Chr(13) & "Problem: Parameter has not been set." & Chr(13) & "Parameter: " & sParmName, 16, "Error In Set/Get Parmeter Function SSF_SetGetParm"
                Case Else
                    Beep
                    MsgBox "Operation: Get" & Chr(13) & "Parameter: " & sParmName & Chr(13) & "Error Number: " & Err.Number & Chr(13) & "Description: " & Err.Description, 16, "Error In Set/Get Parmeter Function SSF_SetGetParm"
                    SSF_SetGetParm = False
                    Resume SSF_SetGetParm_Exit
            End Select
        Case Else
            Beep
            MsgBox "Operation: " & sOp & Chr(13) & "Parameter: " & sParmName & Chr(13) & "Error Number: " & Err.Number & Chr(13) & "Description: " & Err.Description, 16, "Error In Set/Get Parmeter Function SSF_SetGetParm"
            SSF_SetGetParm = False
            Resume SSF_SetGetParm_Exit
    End Select
    
End Function

Open in new window

0
 
Spitfire6Author Commented:
Hi Guys, I have been following along.

LPurvis, I understand your long answer above. One nagging thing in the back of my mind is that since we are calling the function more than one time how do we determine the order in which those calls occur? I am sure that it has to do with the structure of the SQL, and if working with SQL server I think I would look into the Query analyzer, but in Access... not sure.

This has been a grreat discussion thus far, and I have learned a lot from both of you guys. And... sort of patting myself on the back for starting it! :-)

Wouldn't it have been great (and logical) if MS had allowed us to refer to properties in querys, such as my original: Expr1:CheckForChange(param1, param2,  param3).Changed and then perhaps additional references to the function would be made thusly: Expr2:CheckforChange().Changes.

Would this be possible given the limitations you describe?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"And... sort of patting myself on the back for starting it! :-)"

Most definitely Yes ...  this "a truly decent question is so rare to find! :-)"  you will NOT find in may Q's :-)

"CheckForChange(param1, param2,  param3).Changed"

Yes, that would be VERY cool.  And knowing that I have discovered AMAZING things that Jet can do ... you almost gotta believe there is a way to pull it off. But, if Leigh doesn't know how, then the probability is *very* low.


mx
0
 
Leigh PurvisDatabase DeveloperCommented:
Spitfire, with the workaround I mentioned - the order of execution doesn't matter per se.
Verifying the previously passed parameters to not rerun your calculation means that it always gets run once - whichever parameter is requested.

As for those multiple values, I honestly don't think it would be anything MS would be likely to implement.
(Of course - I never thought we'd see Multi-value fields... but there you go.. :-s  And since they're queryable - perhaps an extention to functions might appear in the future... but I wouldn't expect so at all).

Consider however that there would need to be considerable interal optimisation performed - as even if you could have
Exp1: CheckForChange(param1, param2,  param3).Changed, Exp2: CheckForChange(param1, param2,  param3).Changes
Jet would need to recognise, optimise and split the return output - as opposed to calling the function multiple times for each request.
The workaround I offered already does this - I can't see MS implementing it.
As things stand - Jet already optimises function calls that don't have any parameters passed so that it is called once only.  Pass a non-constant parameter - then it's immediately called for each row.
(i.e. if you pass a field that's non-constant even if the field value is always the same, pass a fixed value - that's constant).
However that optimisation by Jet - of only calling a function once does *not* apply when the function call is repeated within the same row!

For example
1)   SELECT Field1, fFunctionCall(Field1) As Exp1 FROM TableName
will see fFunctionCall called for every row.
2)   SELECT Field1, fFunctionCall() As Exp1 FROM TableName
will see the function called once - made available to every row
3)   SELECT Field1, fFunctionCall() As Exp1, fFunctionCall() As Exp2 FROM TableName
will see the function called twice!  Jet does *not* optimise the second call.
4)   SELECT Field1, fFunctionCall(Field1) As Exp1, fFunctionCall(Field1) As Exp2 FROM TableName
will see the function called *twice* for *every* row.

So we'd be talking about a fundamental change in the way function calls are optimised - let alone the ability to return type values.

To be honest I don't have a problem with it as it is.
As a Developer I am a 50/50 man.  I look to neither queries nor code as a preferred solution.  I like both equally and go either way in any given situation.
Despite that, it doesn't massively bother me that there exists this divide.
For one of Access great strength's is that is *isn't* tied to Jet.
And while MS may now be pushing hard for linked tables as the data access methodology of choice in 2007 - ultimately, when dealing with non-Jet sources, the integration with VBA isn't something we can take for granted so much.  What we can already do to enhance Jet using VBA is great - and gives Jet the boost it needs to be able to hold on to the tails of SQL Server and it's superior power and process control (and its own functions of course!)
But that integration isn't, and perhaps shouldn't be, totally encompassing.  Not unless Jet (i.e. ACCDB) has a guaranteed future long into the distance.  And I don't think anything can be guaranteed.

Perhaps the Expression service *will* be enhanced.
But bear in mind that it isn't Properties per se that it can't access (I gave a few examples of objects that it can resolve).  It's code objects.
For example - if an expression could evaluate a type of a function return - it could then be argued that it should be able to access public variables.  And I actually wouldn't feel comfortable with that.
(We have procedures - like MX's SSF_SetGetParm for example - to handle such requirements).
0
 
rockiroadsCommented:
Blimey, Leigh and MX, go on!!! I see Leigh your back to writing essays, this time with MX and not Harfang :)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You and those two are part of my EE 'Dream Team'.  There are 3-4 others also.  In the past, for me ... the answer to the question "Who Ya Gonna Call"  was .... No Clue.

As a result of the acquaintances I have made on EE ... that question now has a MUCH better answer.

mx
0
 
rockiroadsCommented:
why thanks!!! :) Mate, you have figured it out, like a lot of others. Its all about providing answers with explanations and as a team, not at each other - as it sometimes is - the greed for points. I know you and Leigh aint in it for the points as they are meaningless but for helping out. - Just remember this rule, if Leigh has answered then there is a high chance your solution wont get accepted, the top dog that he is, aint that right Leigh :-D

Ive managed this fight this EE addiction, mainly due to my job taking all my time up, and having to work on client site !!!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"if Leigh has answered then there is a high chance your solution wont get accepted"

LOL. Well, the detail and in depth knowledge that he has on these subjects makes that a ... Non-Issue.  In fact, I called him in on the Q - knowing well what the ramifications would be, lol.

Wouldn't this be cool ... an EE World Conference ... ideally held in this part of the World (lol) ... wherein we could all meet in person and hang out! I think that would be super cool.

mx
0
 
rockiroadsCommented:
try hard not to make it a geek convention then. Make sure there is plenty of beer
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Make mine a Gin & Tonic.

As soon as I win the California Lotto (very soon now) I'm going to fund the EEWC ... :-)

mx
0
 
rockiroadsCommented:
Good man, I'll do the same when I win national lottery!
0
 
Spitfire6Author Commented:
Hi Guys,

Although I am not new around here I have not taken the time to figure out what the points deal is, except for that it will increase those who give answers rank as guru, genius, etc. All very fun.

Points don't seem to cost anything so it would seem logical to max out points assigned to any question to be sure to get the attention of the best on the board. That would not be in the spirit of the game though.

In any case, I am always amazed at the talent that is available here and at the speed at which I get help! I am completely self taught (with the exception of a couple of Jr College courses in Access which were pretty worthless) and have only worked by and for myself as a consultant. Been sort of working in my own little world since I decided to make the career change seven years ago. My problem is that I am spread too thin between CF, Access / VBA, VB / VB.net, SQL Server and server admin to get really sharp in any one area. So it's great to have you guys out there.

I think I can do this... I have upped the points on this to 250 and will split between Purvis and MX as I learned a lot from both of you guys. Purvis should get 125 DOLLARS each for those essays!!!

And, I will bring a keg of my finest homebrew, perhaps a nice Belgian Ale, to the EE World Conference!

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Purvis should get 125 DOLLARS each for those essays!!! "

And I should get a Finders Fee, LOL :-)

And a little xtra for tuning Rocki in :-)

Lets make the EEWC happen!!

mx
0
 
Leigh PurvisDatabase DeveloperCommented:
Hey Rocki - how goes it?

Belgian Ale sounds good to me, and of course Rocki's precious Kronenbourg 1664 too :-p
An EEWC has been mentioned by some of us for a good while.  
So far I've met only one fellow EE'er (Dave).  I imagine I'll add Markus to that too some day - and of course when I'm down the midlands way I migiht even catch Rocki one of these days. ;)
(I have yet to drag my... self... lol over to Seattle in a couple of months - where I'll get to gather with some online buddies I have at my other hang out these days - EE should take note of MS's example lol).

A good thread this one.  (Though one's which give a "you can't" answer might not get a great rating in the future lol).

Spitfire - if I might just ask though...
You're probably still implementing the method of implication of the Boolean result by the presence of the String value - is that right?
Can you give a quick example of the relevant part of your query in which you're doing that?
(Or are you not actually making that decision in your query - but in, say, a recordset which has opened the query?)
Worth casting an eye over though...

Cheers
0
 
Spitfire6Author Commented:
Hi Leigh --

I tried the method you ask about, creating a string of the list of changes in the function, and treating the return of an empty string as Boolean "No". I found that the query executed very slowly. It had to go through If- Then's for 5 fields in each record comparing dates... for 30K + Records. Bu-How (That's "No Bueno" in Chinese).

What I am working on now is running a first query, using the original function that uses Select Case just to see if there are any changes on the date in question. Real world, this will return perhaps 30 records. Then I am going to run another query, based onthe first query of course,  that will use a function that builds the list of actual changes.

I have it working now. It is much faster.

Mike



Mike

0
 
rockiroadsCommented:
Yo Leigh, how ya doing. Im still stuck in Surrey :( Ive been there almost a year now, totally crap being away from home 5 days a week. Oh well, a job is a job. Not much happening in the Midlands.
Dave came up near me and gave me a phone call but I missed it, damn, otherwise I was gonna take him out for a krony (good memory mate! you remembered my favoured sip). I'll treat you to one if your ever in the Midlands/Surrey way.

MX, I just saw the annual awards email, big congrats pal on achieving rookie of the year in 3 categories! (my EE email account is not my main one so Ive hardly been checking it).

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Thanks Rocki ... appreciate the kudo.

mx
0
 
Leigh PurvisDatabase DeveloperCommented:
As a general rule, string manipulation functions called in queries aren't too bad, compared to the relatively huge overhead of data accessing ones.
However your 32K rows here is playing a big part.

Your function isn't too complex as far as actual calculation goes (so an If statement deciding which value to return would like have much the same overhead as the "calculation" itself :-)

Do you want to post both what you tried that was slow (query and function) and then what you're currently implementing (queries and function).
I still feel there's a "worth a look situation" here (even if you're happy with the speed now ;-)

If nothing else future readers might want to see it implemented.

Cheers.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Leigh / Rocki ... do you know anything about this:

http://vil.nai.com/vil/content/v_vul35540.htm

mx
0
 
Leigh PurvisDatabase DeveloperCommented:
It doesn't give much information.  Sounds a bit like it's an example implementation of the means of exploiting Jet's function execution which prompted the creation of Sandbox mode in the first place. :-)
(Which would explain why it hasn't made big news among the Access community).

It's lists XP SP2 and Acc2003 SP3 - but I suspect that that simply means that those releases are *still* vulnerable (if Sandbox is disabled I'd imagine).
If they were to give more details then I'd imagine that would be helpful.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"which prompted the creation of Sandbox mode in the first place"

Yes, I remember it well.  This is how it went down (approximately):
1) A nasty person created an MDB with an AutoExec macro that called a Function which executed the Shell command which did something to the effect of executing code like
Del *.* or whatnot - which caused a few problems. lol.

2) This nasty person sent (or loaded) the mdb to a nice person, who then opened the mdb - w/o first checking who the mdb had been sleeping with ... and %*&*!@&&!)(@   = Oh Shit !!!!

3) M$ called this a 'virus' ... and consequently implemented Sandbox Security in A2003 ... aka Sandbox InSecurity - since it is easily circumvented - as my database colleagues in the San Diego Access Users Group - along with info from our friend Garry Robinson (vb123.com) proved in short order.

Today, we are left with the legacy of this mess.  It appears that article above is referring to something else - and yes (actually no) not enough information.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
** Leigh **
Index Q - right up your roadway - give us the insight:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23167354.html

mx
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 19
  • 9
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now