Custom function Second()

[sanniep]

Hello Experts,

I would like to make a custom function Second() for Access in VB. This function should return the second value from a field like the function First gives the first value.

This function should be like the integrated function First() and Last() in Access.

For an expert like you this shouldn't be to hard I hope.. Can anyone help me out with the code for this function?

Thanks for your time! Sander

[PEB]

Dear,

I think that in Access there is a function like this!

Second(MyTime)

Take care my friend!

;)

[sanniep]

Dear,

I think that in Access there is a function like this!

Second(MyTime)

Take care my friend!

;)

Ha, thanks PEB! But I want to use the function in a query-expression for a non-time field. Based on the code for the First function I would like to create Second, Third, Fourth, etc functions..

Could you help me out? Thanks again, Sander

[PEB]

In fact in the queries First(Expression) or Last(Expression), also Sum and Count() aren't function in Basic and aren't build in also!

They are part of the SQL standard used by MS Access

I don't know how to add reserved words and commands in MS Access SQL language!

Reelly a VB function can help you, but first you should determine your parameters!

Function in VB like you want:

Dcount("Field", "Table","Criteria")

So supply us parameters for your function!

:)

[sanniep]

In fact in the queries First(Expression) or Last(Expression), also Sum and Count() aren't function in Basic and aren't build in also!

They are part of the SQL standard used by MS Access

I don't know how to add reserved words and commands in MS Access SQL language!

Reelly a VB function can help you, but first you should determine your parameters!

Function in VB like you want:

Dcount("Field", "Table","Criteria")

So supply us parameters for your function!

:)

Thanks again PEB. I want to use this for my rows to columns problem, I'm not getting your solution in the other thread to work..

Idea:
Terminalno Item Serialno
123456 Housing 256-5899-6
123456 Print P132589
123456 Display 120-A458

What I want
Terminal 123456 contains Housing, Print, Display

Something like: IF Terminalno=Terminalno then Terminalno, First(item), Second(Item), Third(Item)

[PEB]

Hey Man

Semply you need a function that enumerates the values like this:

Terminalno Item Serialno Number_Entry
123456 Housing 256-5899-6 1
123456 Print P132589 2
123456 Display 120-A458 3

When you have this vision it's easy to do an crosstab using as coulmn header the number_entry isn't it?

Have a nice day!

:)

Thanks again PEB. I want to use this for my rows to columns problem, I'm not getting your solution in the other thread to work..

Idea:
Terminalno Item Serialno
123456 Housing 256-5899-6
123456 Print P132589
123456 Display 120-A458

What I want
Terminal 123456 contains Housing, Print, Display

Something like: IF Terminalno=Terminalno then Terminalno, First(item), Second(Item), Third(Item)

[sanniep]

Hey Man

Semply you need a function that enumerates the values like this:

Terminalno Item Serialno Number_Entry
123456 Housing 256-5899-6 1
123456 Print P132589 2
123456 Display 120-A458 3

When you have this vision it's easy to do an crosstab using as coulmn header the number_entry isn't it?

Have a nice day!

:)

That's what I want Peb! But when the TerminalNo changes the count has to start over.

Like this:
Terminalno Item Serialno Number_Entry
123456 Housing 256-5899-6 1
123456 Print P132589 2
123456 Display 120-A458 3
145898 Screen 589965 1
145898 Print P158899 2

What does this function look like?

Thanks again mate!

[PEB]

Hi,
Create user defined functions in a module like this:

GLOBAL last_criteria, last_used
 
Function Set_last( Values, criterias)
'Stop
last_criteria = nts(criterias)
last_used = nts(Values)
Set_last = last_used
End Function
 
Function Show_last( criterias)
Show_last = last_used
End Function
 
Function Show_last_criteria( criterias)
Show_last_criteria=last_criteria
End Function
 

In your query in the Field row:

Ranking:IIF(Show_last_criteria([Your_Field])=[Your Field with Criteria],Set_last(nz(Show_last([Your Field with Criteria]))+1, [Your Field with Criteria]),Set_last(1, [Your Field with Criteria]))

Be careful you need to append the results in a table before use the result in a crosstab!

An other solution for this field you can find about a post for the ranking in MS Access Forum! There was a very usefull link!

:)

[sanniep]

Hi Peb,

Priorities shifted so I didn't look into this problem since now.. Thanks for you september reply!!

I will try your function, thanks for the effort!

Sander

[sanniep]

Man, I don't get this to work!! Whaooaoaoaoao!!

Peb, can I sent you a sample database? It will contain 2 tables, a sample table and a table how I want it to be. Could you applicate your solution to this database?

Hope to hear from you mate! Grtz, Sander

[Killer42]

Can't you do a fairly simple VBA function which would open a dataset with the specified criteria, then do MoveFirst, then MoveNext, and return what it finds?

[sanniep]

Thanks Killer and PEB for your input!! I've got PEB's functions to work, result works as a charm!

Thanks again! Sander

[MrWm]

Hi,
Create user defined functions in a module like this:

GLOBAL last_criteria, last_used
 
Function Set_last( Values, criterias)
'Stop
last_criteria = nts(criterias)
last_used = nts(Values)
Set_last = last_used
End Function
 
Function Show_last( criterias)
Show_last = last_used
End Function
 
Function Show_last_criteria( criterias)
Show_last_criteria=last_criteria
End Function
 

:)

I have copied these functions into a module but I get an error that the nts(criterias) function is not defined.

Did I miss something?

Thanks...Wm

[Killer42]

I have copied these functions into a module but I get an error that the nts(criterias) function is not defined.
Did I miss something?

I vaguely recall a similar issue coming up some months back. I think nts() might have just been some function that PEB created to avoid returning a null value, or some such thing.

Until we sort out exactly what the story is, you might try simply not using the function, and see what happens. If the intention is simply to store and retrieve a value, then transferring directly to/from the global variable might be good enough.

I've sent a message to PEB, asking him to look in on this thread - hopefully he can shed some light.

[PEB]

I vaguely recall a similar issue coming up some months back. I think nts() might have just been some function that PEB created to avoid returning a null value, or some such thing.

Until we sort out exactly what the story is, you might try simply not using the function, and see what happens. If the intention is simply to store and retrieve a value, then transferring directly to/from the global variable might be good enough.

I've sent a message to PEB, asking him to look in on this thread - hopefully he can shed some light.

Yeah Killer its just like this

Function nts(Stri) As String
  On Error Resume Next
  Dim result As String
  If IsNull(Stri) Then
    result = ""
  Else
    result = Stri
  End If
  ns = result
End Function

[Killer42]

Thanks for that, PEB. So MrWm, does that solve your problem?

[MrWm]

Well, it does remove the code error I was having, however the query that I am using (based on PEB's Sept 21st post) displays an error in the ranking field of every record:

Query -
Ranking:IIF(Show_last_criteria([Your_Field])=[Your Field with Criteria],Set_last(nz(Show_last([Your Field with Criteria]))+1, [Your Field with Criteria]),Set_last(1, [Your Field with Criteria]))

My query looks at EmpID, Date, Score, and the Ranking. I am trying to rank the scores for each user for each month, to create the crosstab query. I have used EmpID to replace [Your_field] and [Your Field with Criteria].

I can't seem to find my error...any suggestions?

Also, in the query above there is a function nz() that I don't know what it does. Is this the problem?

[Killer42]

Hm... I suppose the nz() function might be the problem. But it's a function built into MS Access, so as far as I know it should be alright.

One thing I would suggest is to use MS Access directly to test your query. It's much quicker and easier to play with the query in Access's GUI query designer. Once you've got it working, then you just go to the SQL view and copy it. (Note, you can also paste you SQL into there in the first place.)


Quoted from online help in Access...

Nz Function
You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.