problem after load images in database..

[leen]

hi...i got a problem..i need a help..when i hv load 38000 image files
in database ..i need to sort the file refer to the name of the
file..like a103.bmp,a104.bmp,a105.bmp..bc*oz when i sort it..it has
been like this..a103.bmp,a1031.bmp,a104.*bmp,a1041.bmp...can u help
me??is there any coding in vba which can solve this problem??? plz i
really need a help...tq.....

[pietlinden]

you have to split the text prefix and the number and sort by each one.
If you concatenate text and numbers, you'll get text sort orders. Use
LEFT, RIGHT and MID to split off the two parts into two different
fields in a query and sort by the textpart and then the numeric part.

[Phivos]

What is happening here is that the filenames are sorted as characters
and not as numbers..So 1000 comes before 2. Do you have a field where
you store the filename? You need to sort on an expression which takes
the part of the filename which looks like a number and converts it to a
number that you can sort on. Using Val(string) converts the string to
a number.

assuming your name has a character 'a' then the number and then '.bmp':

first :You have to get rid of the file extention .bmp.

Use a string expression that takes all the characters starting fron the
left except the last 4. Assuming the field with the filename is called
'filename',

Left([filename];Len([filename])-4)
will do that for you.

Next get rid of the 'a' in front of the filename. You need the
rightmost characters except for the first one.
Right([Expr1];Len([Expr1])-1)
will do that, where Expr1 is the result of the previous step.

Now all you need is Val(Expr2) (the result from the previous step)
which will give you the number part of the filename as a number you can
sort on.

Of course you can combine the above steps into one.

Val(Right(Left([filename];Len([filename])-4);Len(Left([filename];Len([filename])-4))-1))

You can put this expression in a query and sort on this.

Hope this helps.

Phivos

leen wrote:

hi...i got a problem..i need a help..when i hv load 38000 image files
in database ..i need to sort the file refer to the name of the
file..like a103.bmp,a104.bmp,a105.bmp..bc*oz when i sort it..it has
been like this..a103.bmp,a1031.bmp,a104.*bmp,a1041.bmp...can u help
me??is there any coding in vba which can solve this problem??? plz i
really need a help...tq.....

[leen]

thank you Phivos...can u giv any coding on that???
Phivos wrote:

What is happening here is that the filenames are sorted as characters
and not as numbers..So 1000 comes before 2. Do you have a field where
you store the filename? You need to sort on an expression which takes
the part of the filename which looks like a number and converts it to a
number that you can sort on. Using Val(string) converts the string to
a number.

assuming your name has a character 'a' then the number and then '.bmp':

first :You have to get rid of the file extention .bmp.

Use a string expression that takes all the characters starting fron the
left except the last 4. Assuming the field with the filename is called
'filename',

Left([filename];Len([filename])-4)
will do that for you.

Next get rid of the 'a' in front of the filename. You need the
rightmost characters except for the first one.
Right([Expr1];Len([Expr1])-1)
will do that, where Expr1 is the result of the previous step.

Now all you need is Val(Expr2) (the result from the previous step)
which will give you the number part of the filename as a number you can
sort on.

Of course you can combine the above steps into one.

Val(Right(Left([filename];Len([filename])-4);Len(Left([filename];Len([filename])-4))-1))

You can put this expression in a query and sort on this.

Hope this helps.

Phivos

leen wrote:

hi...i got a problem..i need a help..when i hv load 38000 image files
in database ..i need to sort the file refer to the name of the
file..like a103.bmp,a104.bmp,a105.bmp..bc*oz when i sort it..it has
been like this..a103.bmp,a1031.bmp,a104.*bmp,a1041.bmp...can u help
me??is there any coding in vba which can solve this problem??? plz i
really need a help...tq.....

[leen]

thank you Phivos...can u giv any coding on that???

[leen]

helo Phivos...i've follow ur instruction..but i got a problem...when i
run the code..it will said.."wrong number of arguments used with the
function in query expression"...can u help me???...is there anything
wrong???this is my coding
SELECT [tblLoadOLE].[OLEPath]
FROM tblLoadOLE
ORDER BY
VAL(Right(Left([OLEPath].Len([OLEPath])-4).Len(Left([OLEPath].Len([OLEPath])-4))-11));