Custom Order

[dcx693]

OK, I think we need to change the approach a bit to accommodate this.

Since all entries will have a space character between the text part of the topic and the numeric portion, we can use the space character to find the numeric portion, add leading zeroes and marry it back to the text portion. If the text portion is "General", we replace it with "_", otherwise, we leave it alone.

Do you want to take a crack at it? You can use the Instr() function to determine where within a string a character can be found. For example:
Instr(1,"General 12"," ")
would return 8.

 

[Mrs.Meeker]

Okay, I keep getting invalid syntax errors so I obviously don't know what I'm doing...but I already knew that.

I'm not sure how to build this.

Help?

Ya know, I'll have to print this out and put it in my book!! What an education!!!

 

[dcx693]

OK, believe it or not, this is the formula I came up with:
IIf(Left([PageCode],7)="General","_",Left([PageCode],InStr(1,[PageCode]," ")-1)) & Format(Mid([PageCode],InStr(1,[PageCode]," ")+1),"0000")

It should handle topics up to "ZZZZZZ.... 9999" and "General 9999".

 

[Mrs.Meeker]

What is an invalid procedure call?

Expr1: IIf(Left([tblQuestions.PageCode],7)="General","_",Left([tblQuestions.PageCode],InStr(1,[tblQuestions.PageCode]," ")-1)) & Format(Mid([tblQuestions.PageCode],InStr(1,[tblQuestions.PageCode]," ")+1),"0000")

I added the tblQuestions. to your formula, it didn't hurt it last time. I have two tables in my query that have PageCode in them. Did I mess it up by putting tblQuestions. in it? I wouldn't think so but don't know what else could be wrong.

 

[dcx693]

I'm not sure why it worked before, but "[tblQuestions.PageCode]" is not a valid way to name items in Access (at least, not that I know of). The proper way is:
[tblQuestions].[PageCode]

 

[Mrs.Meeker]

Wooooo Hooooo!!!!

You are the King (or Queen) whatever the case may be!!!

Thank you so very much! I have learned alot from you. I know that I have gobs and gobs left to learn, but it's people like you that keep me plugging away!! It's really great when someone has the time and patience to help so much!!!!

Thanks again!
Rhonda

 

[dcx693]

I'd rather be called a King, thanks. You're welcome. Glad it worked out.