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(Expressio n) 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","Criter ia")

So supply us parameters for your function!

:)

[sanniep]

In fact in the queries First(Expressio n) 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","Criter ia")

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=Term inalno 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=Term inalno 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(Sho w_last_criteria ([Your_Field])=[Your Field with Criteria],Set_last(nz(Sh ow_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

[CODE=vb]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[/CODE]