Querying text col in a table

[mosmas]

I have a 'Container ID' column with the following information
formated as text:
448846-1
448846-2
448846-3
448846-4
448846-5
448846-6
448846-7
448846-8
448846-9
448846-10
448846-11
448846-12
448846-13
448846-14
448846-15
448846-16

When I ran this query:

SELECT *
FROM [Product Inventory Database]
WHERE (([Container ID] between "422904-" And "422904-16&quot);

I am getting results with only these numbers.

448846-1
448846-10
448846-11
448846-12
448846-13
448846-14
448846-15
448846-16

Wat can I do to have my query return records *-2 --> *-9?

Mosmas

 

[CajunCenturion]

The reason that you're getting only those records is because it's an alpha field and uses alpha sequenciing rather than numeric sequencing.

There are a couple of options. One is to place leading zeros as appropriate
448846-01
448846-02
...
448846-10
and adjust the between clause accordingly.

A second option would to split this into two fields, a primary number and a sub number, with both fields being numeric.

A third option would be to incorporate a Val function into the query, something like
WHERE (Left([Container ID], 6) = "448846&quot and (Val(Mid[Container ID], 8)) Between 1 and 16)

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein