Please tell me about Queries and VBA modules.

[Linty Fresh]

Hi all

I'm new to using VBA modules in Access, and I've got a question about using them with queries.

Basically, is it possible to use a VBA module to deal with data already entered. What I'm trying to do is take a street address with both numbers and letters (Example: 111A) and separate out the numeral, which I want in its own field. I've written a module (Public Function StreetNum(s As String) As Integer) which I think will perform what I need, and I'm trying to get the query to recognize it. I'm following a website which seems to indicate that it's possible, but I'm having problems.

I've set aside a field in the query where I want the number to go and named it Adjusted. The heading in the Field name is Adjusted: StreetNum([Street_Number]). I'm getting an error message Undefined function 'StreetNum' in expression.

Many thanks.

 

[pbaldy]

That looks fine. The function would have to be in a standard module, not a form or report module. Also, make sure the function and module don't have the same name.

 

[ajetrumpet]

StreetNum([Street_Number]). I'm getting an error message Undefined function 'StreetNum' in expression.

As an addition to Paul's comment, it really sounds like a spelling error or something.

The function is "undefined". The program can't find it! Why not?

 

[boblarson]

To build on what Adam and Paul said, make sure that you have it in a standard module and that the function is declared as PUBLIC, not PRIVATE.

 

[Linty Fresh]

Just popping in to say thanks. I'll be trying your suggestions tonight, and yes, I've already found one mistake based on what you've all said: I gave the module the same name as the function.

Thanks again.

 

[Linty Fresh]

OK, so I changed the name of the module, and that took care of the undefined error. Thanks, pbaldy!

And now I'm getting a new error when I'm trying to run the query. It's a Compile Error: Invalid Qualifier for one of my variables.

Here's the VB code. Yes, I know it's kind of hosed. My VB coding skills--never that great to begin with--have gotten rusty, and I know that I need to rework my syntax a bit. I'm just interested in the error message. Once I get that out of the way, I think I can remember enough code to solve any logic problems present. The variable giving me the error is in red.
_________________________________________________
Public Function StreetNum(strAddr As String) As Integer

Dim strNum As String
Dim chrArray() As String
Dim strReturn As String
chrArray = strAddr.ToCharArray

strNum = ""
For intcounter = 0 To chrArray.Length - 1
If IsNumeric(chrArray(intcounter)) = True Then
strNum = strNum + chrArray(intcounter)
Else
break
End If
Next

StreetNum = Val(strReturn)

End Function

____________________________________

Could the problem be the data I'm trying to pass to the function? (Right now, the data going to the function is a text field.). Or did I overlook something in the code itself?

Once again, many thanks.

 

[pbaldy]

I guess those guys want me to go first again. strAddr is simply a string being passed to the function so it has no properties that can be accessed like:

strAddr.ToCharArray

I don't use array's much; to use the methodology you've started, try the Mid() function inside a For/Next loop, using the length of the string for the "To" argument (Len() function)

 

[mearle]

It looks like you are mixing .NET style VB with classic VB/VBA, used by Access.
You can't use the ToCharArray method - the nearest equivalent in VBA is to use StrConv to convert to a byte array.
Similarly chrArray.Length should be len(strAddr).
Break is not needed, and neither, in this case, is Else.
In VBA, you would normally use the Mid string function instead of converting to arrays.
However, your whole task could probably be acomplished by using the Val() function on the address string in the query itself, rather than creating your own function.

 

[WayneRyan]

Just my $0.02 worth:

"Also, make sure the function and module don't have the same name"

I'd caddy for that guy anyday!

9-holes, Par three ... Paul carries the bag.

Wayne

 

[pbaldy]

If you're the caddy but I'm carrying the bag, you better have an ice chest full of beer on your back. You'd be the most popular caddy ever!

ps. send some nice SoCal weather up here. The crappy Seattle weather followed me home.

 

[Linty Fresh]

However, your whole task could probably be acomplished by using the Val() function on the address string in the query itself, rather than creating your own function.

That did the trick, mearle. Thanks, you're awesome.

And pbaldy and everyone else, thank you all once again, too. You saved my butt!

I'll get the hang of this, someday.