Software

Ask something
Discussion Locked

Access 2K custom function

0 helpful
9 replies

by curlergirl 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I need to perform a certain task from a query in Access 2K and the "experts" at MS have told me that this can be done with a cusotm function. Problem is - I don't know how to write one. I've tried with the Access Help - but I can't seem to figure it out. I've racked my brains for other ways to do this and I can't come up with anything that works. I need help ASAP on this!!Here's what I need to do:

Each record contains the following fields: client, matter, category, description. I need to compare the client, matter and category fields from one record with those from the next record; if they are the same, I need to add an asterisk (*) and an incrementing number to the category field. Then I need to do the same comparison with the PREVIOUS record with the same results if they match. If all three don't match (i.e., if only two match), then nothing is done. Here's an example:

Client 1, Matter 1, Category 1.1, First
Client 1, Matter 1, Category 1.1, Second
Client 1, Matter 2, Category 1.1, Third
Client 2, Matter 1, Category 3.3, Fourth
Client 2, Matter 1, Category 3.3, Fifth
Client 2, Matter 1, Category 5.4, Sixth

Results would need to be:

Client 1, Matter 1, Category 1.1*1, First
Client 1, Matter 1, Category 1.1*2, Second
Client 1, Matter 2, Category 1.1, Third
Client 2, Matter 1, Category 3.3*1, Fourth
Client 2, Matter 1, Category 3.3*2, Fifth
Client 2, Matter 1, Category 5.4, Sixth

Any ideas?

9 replies

by curlergirl

P.S. Some add'l. info if it helps: The original data is being pulled from an external Access database. I have linked tables within my database that allow me to pull out the info I need using queries. I am using a make table query to pull the original data first and then I need to edit the data as described above to then export it to ASCII text format for import to a third database.

by maxwell edison

Greetings,

Microsoft article Q209857 titled, "How to Find Minimum or Maximum Value Across Fields of Record", might be of some help.

The article states, "Microsoft Access has no built-in functions that find the maximum or minimum values of numbers across the fields of a record."

"This article provides two custom functions that cycle through the values across fields and return the minimum or maximum values of each record."

"Microsoft provides programming examples for illustration only..."

The whole article with the explainations and examples can be found here:

http://support.microsoft.com/support/kb/articles/Q209/8/57.ASP?LN=EN-US&SD=gn&FR=0&qry=custom%20functions&rnk=4&src=DHCS_MSPSS_gn_SRCH&SPR=ACC2000

Cut and paste the link (REMOVE SPACES if they sneak into the pasted URL.)

Good luck,

Maxwell

by maxwell edison

.
.
Cut and paste this link as well. There are 21 Microsoft articles listed here that may contain helpful information as well. (Don't forget to REMOVE those pesky SPACES)

http://search.support.microsoft.com/kb/psssearch.asp?SPR=acc2000&T=B&KT=ALL&T1=7d&LQ=custom+functions&S=F&A=T&DU=C&FR=0&D=kbaccess2000Search&LPR=&LNG=ENG&VR=http%3A%2F%2Fsupport.microsoft.com%2Fsupport%3Bhttp%3A%2F%2Fsupport.microsoft.com%2Fservicedesks%2Fwebcasts%3Bhttp%3A%2F%2Fsupport.microsoft.com%2Fhighlights&CAT=Support&VRL=ENG&SA=GN&Go.x=30&Go.y=19

Regards,

Maxwell

by curlergirl

Thanks for the suggestions, Maxwell. I looked at the article but it didn't do the trick for me.

by sl-campbell

(I'll have to split this because of the length)

PART ONE:

Create a new Module and add the following sub:

Public Sub CategoryIncrement()

Dim db As Database
Dim rsClients As Recordset
Dim rsClient As Recordset
Dim sqlClients As String
Dim sqlClient As String

Dim sCurrClient As String
Dim sCategory As String
Dim sMatter As String
Dim iStartPos As Integer
Dim lCounter As Long

Set db = Application.CurrentDb

sqlClients = "SELECT DISTINCTROW Client, Matter, First(Category) AS CategoryFld " _
& "FROM Clients " _
& "GROUP BY Client, Matter " _
& "HAVING Count(Client)>1 AND Count(Matter)>1"

Set rsClients = db.OpenRecordset(sqlClients, dbOpenSnapshot)

Do Until rsClients.EOF
'Get next client
sCurrClient = rsClients("Client")

'Get matter for current client
sMatter = rsClients("Matter")

'Get category Root for current client
'This has to be done in case records are added later where
'new records will not have the sub category ID and old records will
'We will use then LIKE in the sql criteria
'BETTER: Seperate the category field into 2 fields:
'base category and Category extension
'Then you can combine them together for "show and tell"

sCategory = rsClients("CategoryFld")
iStartPos = InStr(sCategory, "*")
If iStartPos > 0 Then sCategory = Left$(sCategory, iStartPos - 1)

'reset counter
lCounter = 0

by sl-campbell

PART TWO:

'Create recordset for the current client
'BETTER: Have a date field to sort on then the records are sure
'to get sorted by the order of when the data was actually entered

sqlClient = "SELECT * FROM ClientsWHERE Client='" & sCurrClient _
& "' AND Matter='" & sMatter _
& "' AND Category LIKE '" & sCategory & "*'"

Set rsClient = db.OpenRecordset(sqlClient, dbOpenDynaset)

If Not rsClient.EOF And Not rsClient.BOF Then
'Populate the recordset so we can determine the actual record count
rsClient.MoveLast
rsClient.MoveFirst
End If

'Only get records if more than one
If rsClient.RecordCount >1 Then
'Loop through current client records which have duplications
Do Until rsClient.EOF
'increase counter
lCounter = lCounter + 1

'change Category field to reflect the new category ID
'(Could use the following IF to determine if the record already
'has an extension, and only when it doesn't then update the
'record, or, with-out the IF, always update all records with duplications
'iStartPos = InStr(sCategory, "*")
'If iStartPos = 0 Then
rsClient.Edit
rsClient("Category") = sCategory & "*" & lCounter
rsClient.Update
'End If 'Get next data for current client
rsClient.MoveNext
Loop
End If

'Get next Client
rsClients.MoveNext
Loop
End Sub

'Run the sub by calling it from a form or macro or by using F-5

by sl-campbell

The only thing that you need to change here is the table name in the FROM section of the sql string for both recordsets.

by curlergirl

I'm impressed! I really appreciate the time you obviously took to answer my question. I have a followup question for you which I have emailed directly to you - I hope you don't mind, but it involves bucks rather than points.

This discussion is closed to new replies.