Please could someone help me? I have a large amount of data in an Access 97 d/b, each entry takes the following format;

[ 123/4567 | Name | Address | Data ]

I would like to sort the information using only the first three numbers in the first entry (that 123/4567 number) and then subsort alphabetically via the Name entry.

I don't want to lose the trailing 4 numbers after the fwdslash, but only need the first three for the current task.

I'm certain this is probably an insultingly simple command/operation, but I just can't seem to get it working. If someone could enlighten me, I'd be very grateful.

Thanks in advance.

[This message has been edited by CodeBat (edited 10-08-2001).]

If the name of the text field containing the '123/4567' is Widget, you could create a calculated field and sort on that, e.g., ORDER BY Val(Left([widget],InStr([widget],"/")-1))

Forgive my ignorance, but this is producing a syntax error;

Syntax error (missing operator) in query expression 'ORDER BY Val(Left([widget],InStr([widget],"/")-1))'.

BY is highlighted by the cursor once the warning has been OK'd. The replacement I am using for [widget] is [Table Name].Field

Am I stupid or is there something wrong?

[This message has been edited by CodeBat (edited 10-08-2001).]

...using only the first three numbers in the first entry ....

If you are going to be using the data in separations such as 123 and 4567, you should probably create separate fields for them. To do this, create two new fields in your table. [First3] and [Last4]. Make an update query. Put those two fields on the grid. Under the criteria for [First3] put Left([Your123/4567FieldName],3). Under the [Last4] put Right([Your123/4567FieldName],4]).

That will populate your new fields when you run the query. You can delete the other since you will not need it anymore.

Now if you want to sort, create a query that sorts [First3] ascending and [Name] ascending. The first one on the grid gets sorted first so if you want [Name] and then [First3], switch them on the grid.

HTH.

"Field" is a reserved word and shouldn't be used to designate a specific field. Rename the field that contains your 123/4567 data to something unique, e.g., Widget. Ensure that field Widget is a text field. Then try rerunning the code provided.

Thanks for the help.

My aologies for being a little obscure in my reponse above; to make things a little clearer I wasn't literally using the string [Table Name].Field for the data, I only used that to make things more identifiable to the reader (in terms of objects and fields).

The actual function I have in my code reads as follows (and this is an exact quote this time http://www.access-programmers.co.uk/ubb/wink.gif);

ORDER BY Val(Left([Contracts Signed].ConID,InStr([Contracts Signed].ConID,"/")-1))

PS: Contracts Signed is the table I want the info from, ConID is the field. This still isn't working, neither is the alternative code supplied by jwindon - am I calling the field incorrectly? The field property is "Text" by the way, should that make any difference. Tried converting it to number but my d/b got chewed in the process...

...oh, and I'm using Access '97 too.

[This message has been edited by CodeBat (edited 10-09-2001).]

Try attacking the problem sequentially:

Create a simple Select query that includes your [ConID] field. Once the query is working, add this calculated field to the query grid just as shown (no table names):

X: left([ConID], Instr([ConID], "/")-1)

If that works, change it to:
X: val(left([ConID], Instr([ConID], "/")-1))

If that works, then change the Sort (which is blank up to this point) to Ascending.

Please post back with the results.

I tried Left([ConID],InStr([ConID],"/")-1) and still got empty results...

...the brick wall beckons.

http://www.access-programmers.co.uk/ubb/frown.gif

[This message has been edited by CodeBat (edited 10-09-2001).]

I'm not giving up yet! Any more suggestions? I've double-checked my syntax and it's all in order, but still not providing any results.