adding criteria to query

[allison]

Hi,

I have set up a query and am trying to add criteria to it. I have read some past posts as well as some help files, and I am almost there but am missing something.

OK...my query is pulling on field from a table "Dept". Within this field there is a string that contains numbers and text. What I need to do is to set up the criteria so that it extracts only the numeric values from each row in that field. Then I would like to trim it so the zeros before the numbers are not displayed.

Here is a sample of the data in the Dept field.
0100RALPH LAUREN TEXTILE
0101RL SOLIDS
0102WOMENS FRAGRANCE
0103C. DIOR FRAGRANCES
0104DERMABLEND

From the design view of the query I have tried adding Left([Field1],4) to the criteria field. I have also tried to use the Expression Builder but have not been able to get this to work.

Any help or suggestions would be much appreciated.

Thanks,

Allison

 

[bat17]

What you are after is not a criteria but making a new column of data. You are colse though
In a Field: row of a new column in the query view add
MyNumber:=Val(Left([field1],4))

This should then give you just the number portion of the left for places. you could do it with just the val() function as that returns the first value found but if there is a possibility that the fifth charecter is a number then it is best to use the Left function.

HTH

Peter

 

[allison]

Thanks Peter,

That worked and not need to trim, it does it automatically.

Allison