UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Prob Creating/calling A Function In Vba, Any Version    
 
   
compdinosaur
post Feb 25 2020, 08:53 PM
Post#1



Posts: 299
Joined: 29-August 07
From: Rockville, MD


This is the first time that I have attempted to write a function procedure and am having difficulty - my procedure compiles, but I get a ByRef argument type mismatch when I try to invoke the function. I can't find an example that I understand. I wish to eliminate a quote from a string then take the left most 4 or less char stopping at a comma or before
QUOTE
Private Function CreateCall2(Hold As String) As String
Dim Placequote As Integer
Dim Placecomma As Integer
'remove any quote from Hold String, first find the quote in the string
Placequote = InStr([Hold], "'")
' initialize hold to 6 char (might have both a quote and a comma)
Hold = Left(Hold, 6)
'check to see if a quote was found, eliminate quote from string, put in Hold
If Placequote > 1 Then
Hold = Left([Hold], [Placequote] - 1) & Mid([Hold], [Placequote] + 1, 5)
End If
' save the first 5 char of the string in Hold
Placecomma = InStr(Hold, ",")
'
'call2 should have <= 4 characters; if no comma, take 1st 4 char
Hold = Left(Hold, 4)
'if a comma, take up to comma
If Placecomma > 1 Then
Hold = Left(Hold, Placecomma - 1)
End If
End Function


I set Hold:
QUOTE
Hold = Ucase(me.Author)
Me.Call2=CreateCall2(Hold)

--------------------
Barbara
Go to the top of the page
 
theDBguy
post Feb 25 2020, 09:22 PM
Post#2


UA Moderator
Posts: 77,703
Joined: 19-June 07
From: SunnySandyEggo


Hi Barbara. How did you declare the variable Hold? What is the value in Me.Author?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Feb 25 2020, 09:43 PM
Post#3


UtterAccess VIP
Posts: 11,782
Joined: 10-February 04
From: South Charleston, WV


You want to eliminate a character from a string? Can't you just Replace with ""?

--------------------
Robert Crouser
Go to the top of the page
 
compdinosaur
post Feb 26 2020, 07:10 AM
Post#4



Posts: 299
Joined: 29-August 07
From: Rockville, MD


Thank you for our replies and suggestions. The value in "Me.Author" is a character string of an Author's LastName,FirstName. I am attempting to create in Me.Call2 the second part of a Spine Label for a library book. I'd like to use the function for a book's author, except for biographies, where is will use this for the person (subject) the biography is about.

I've put the Dim for Hold at the top and taken the advice to use the Replace function (of which I was previously unaware) Now I no longer get an error, but Me.Call2 does not get the value of the result of the function.

QUOTE
Option Compare Database
Option Explicit
Dim Hold As String
'
'[other code here]
'
'this is where I don't know how to get the result of the function in Me.Call2 (Call2 is supposed to have the first 4
' or less char of the letters in Hold at this point. After the function is called, Hold contains what I wish, but the result doesn't "fill" Me.Call2
'
Me.Call2 = CreateCall2(Hold)
'
'[other code here]
'
Private Function CreateCall2(Hold As String) As String
'
Dim Placecomma As Integer
'remove any quotes from Hold String
Hold = Replace(Hold, "'", "")
'
' save the first 5 char of the string in Hold
Placecomma = InStr(Hold, ",")
'
'call2 should have <= 4 characters; if no comma, take 1st 4 char
Hold = Left(Hold, 4)
'if a comma, take up to comma
If Placecomma > 1 Then
Hold = Left(Hold, Placecomma - 1)
End If
'
End Function

--------------------
Barbara
Go to the top of the page
 
cheekybuddha
post Feb 26 2020, 07:25 AM
Post#5


UtterAccess Moderator
Posts: 12,600
Joined: 6-December 03
From: Telegraph Hill


Hi Barbara,

For a function to return a value you need to assign it at the end.

The way you have written it you will have scope issues with your variable 'Hold'. I'm not sure why you need the module level version.

CODE
Private Function CreateCall2(ByVal Hold As String) As String

  Dim Placecomma As Integer

' remove any quotes from Hold String
  Hold = Replace(Hold, "'", "")

' save the first 5 char of the string in Hold
  Placecomma = InStr(Hold, ",")

' if a comma, take up to comma
  If Placecomma > 1 Then
    Hold = Left(Hold, Placecomma - 1)
  Else
' call2 should have <= 4 characters; if no comma, take 1st 4 char
    Hold = Left(Hold, 4)
  End If

' The important bit!!
  CreateCall2 = Hold

End Function


Then, to call the code, you can use:
CODE
  Me.Call2 = CreateCall2(Ucase(Me.Author))


To be honest, I don't really understand what your code is doing!

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
compdinosaur
post Feb 27 2020, 07:01 AM
Post#6



Posts: 299
Joined: 29-August 07
From: Rockville, MD


yes, this is what I needed, thank you. I also deleted the Dim statement at the top for Hold.

I am working on a database for a small (around 3,000 vol) library adding a Dewey Decimal Classification to records in an old database where existing records need to be edited and records for new books will be added. The librarians wish me to be able to create a 2-part Call Number for these books that will translate into exportable data for new spine labels. The top of the label (Call1) will be the Dewey Decimal number, BIO, FIC, MYS or REF. They wish for the bottom part of the label to be uppercase the first 4 or less (if last name is less than 4 char) significant letters of either the Author's last name (in the case of all books except biographies) and the Subject's last name in the case of Biographies.
This now seems to be working, but I will do some more testing.

Thanks again for all your help.

--------------------
Barbara
Go to the top of the page
 
cheekybuddha
post Feb 27 2020, 07:16 AM
Post#7


UtterAccess Moderator
Posts: 12,600
Joined: 6-December 03
From: Telegraph Hill


hi Barbara,

Thanks for the explanantion.

It appears this could be simplified a little.

As I understand it you wish Call2 to be the first four letters (or less) of the name before the comma (if there is a comma)
CODE
Function CreateCall2(Author As String) As String

  Dim strNames() As String

  strNames = Split(Author, ",")
  CreateCall2 = Ucase(Left(strNames(0), 4))

End Function


The Split() function will split the author names into an array with a comma as the delimiter.

If there is no comma, then the whole author name goes into the first element of the array.

So, the first element of the array will contain either the surname, or the whole name.

Then just take the first 4 characters of the name. If there are fewer then they're all that will be returned.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
theDBguy
post Feb 27 2020, 05:14 PM
Post#8


UA Moderator
Posts: 77,703
Joined: 19-June 07
From: SunnySandyEggo


Hi Barbara. Glad to hear you got it sorted out. If Call2 is a calculated value, then you might be able to simply add it as a column in your query or an expression on your form. For example:

CODE
=UCase(Left(Left([Author],InStr([Author],",")-1),4))


Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    28th March 2020 - 08:45 AM