PLEASE--Need help with my query coding..

[namliam]

Instr returns a number where the searched string is found... Try adding or substracting a number of characters from there and see what happens

As for spaces... Trim, Rtrim and Ltrim will save your day there.

 

[KimHuff]

Do you know a good place to get help on to use Instr and the Trim options you mentioned. Adding or subtracting a number of characters from there doesnt really help me. I know you will be shocked, but I dont know VBA very well.. I actually know enough to be dangerous. I can read through some things and say.. ok.. i know what it is doing here.. but knowing how to use the various different options.. like Instr and Trim.

I do want to learn this stuff, but some of the sites I go to and some of the books I have bought havent really helped much.

I will google the Instr and the trim functions for VBA and/or Access and see what I find, but if you are aware of a good place to read/learn about this, I would appreciate your assistance.

Kim

 

[namliam]

Instr and Trim functions are perfectly explained in the ACCESS HELP (just press F1, perhaps after typing the command you are looking for to send help to that very page directly), I know its magic.

... I know you will be shocked, but I dont know VBA very well.. I actually know enough to be dangerous. ....
I do want to learn this stuff, but some of the sites I go to and some of the books I have bought havent really helped much.

..., but if you are aware of a good place to read/learn about this, I would appreciate your assistance.

Sorry not shocked at all, if you were a 100% proof full fledged expert you would not be comming to the forum looking for answers

I cannot help you on teaching though, I am 100% self tought (with the help of this forum among other resources like google)...

The subtracting of characters will help though....
What does this do??
left(InWord, InStr(InWord, " "))

More or less the same as...
left(InWord, 9)
would if InWord would contain "namliaM, ehT"

Now this returns: "namliaM, "
So how to get rid of the last 2 (the comma AND the space) characters??
Left(InWord, 7)
will return: "namliaM"
No space(s) no comma, thus...
left(InWord, 9-2)
is actually the same thing, now making this 100% flexible...
left(InWord, InStr(InWord, " ") - 2)

TADA

 

[KimHuff]

What does this do??
left(InWord, InStr(InWord, " "))

Thank you for spelling this out. I have always wondered what this meant. Spelling things out like that really helps.

Sorry not shocked at all,

Yea.. kind of knew you wouldnt be shocked.. that was the point. LOL

I cannot help you on teaching though, I am 100% self tought (with the help of this forum among other resources like google)...

I too am self taught with regard to many aspects of what I do. My problem is I rarely use Access anymore. I used to use it for directories but stopped doing that in 2003. The only reason I have gone back to it was to volunteer for my sons school. But doing this has kind of peaked my interest again.

left(InWord, InStr(InWord, " ") - 2)

I did try this on the code and although it seems to be working... I get the following error message over and over again.

Error 5: Invalid procedure call or argument

It also isnt "breaking" on the comma which I think can be fixed by just changing the " " to ", "

I will try my best to read up on things... hopefully I can figure it out. It sure will make things easier for next year.

Kim

 

[namliam]

Or just changing " " to "," to break it on the comma instead of a space...

This code will fail if your InWord is blank (NULL), you need to catch that before it happens.
It can be as simple as using a NZ function (access help for more details)

It may also fail on empty strings ("") or if there is no seperator (no space or comma)...

You need to catch the errors, see where they happen and deal with them