Text data query

[Sharp]

Hi

Consider this table:

PRIMARY_KEY
A1
A2
A3
A4
A5

The attribute PRIMARY_KEY has data of type "text".
I'm trying to write a query to extract rows A2-A4.
Because the data is of type 'text' and not 'long integer' I don't know how
to write the constraint.

This is easily solved if the primary key simply contained integers.
SELECT *
FROM table
WHERE PRIMARY_KEY >=2 AND PRIMARY_KEY <=4;

The problem is the letter A preceding the number, and therefore Access
cannot convert it to a long integer.
Is there a way of writing a query that will handle the data as a text
instead of a integer?

Cheers
Michael

 

['69 Camaro]

Hi, Michael.

The problem is the letter A preceding the number, and therefore Access
cannot convert it to a long integer.
Is there a way of writing a query that will handle the data as a text
instead of a integer?

Of course, this depends upon the structure of the rest of the data, but if
the first character of the field is always a character and the rest of the
characters are always digits, then try syntax such as the following:

SELECT PRIMARY_KEY
FROM tblStuff
WHERE ((Right(PRIMARY_KEY, (Len(PRIMARY_KEY) -1))) BETWEEN 2 AND 4);

By the way, on one of your other threads from last week you were having
difficulty with the sort order on the table after assigning an AutoNumber.
Did you find that the table had the "Order By" property set to a field other
than the AutoNumber, or did you fix the problem by using a different method?

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Andi Mayer]

Hi

Consider this table:

PRIMARY_KEY
A1
A2
A3
A4
A5

The attribute PRIMARY_KEY has data of type "text".
I'm trying to write a query to extract rows A2-A4.
Because the data is of type 'text' and not 'long integer' I don't know how
to write the constraint.

This is easily solved if the primary key simply contained integers.
SELECT *
FROM table
WHERE PRIMARY_KEY >=2 AND PRIMARY_KEY <=4;

two posibilities:
WHERE int(left(PRIMARY_KEY,2)) >=2 AND PRIMARY_KEY <=4

or

WHERE PRIMARY_KEY >="A2" AND PRIMARY_KEY <="A4"

a string needs quots

 

['69 Camaro]

Hi, Michael.

Oops. The example I gave you will work, but you should convert the text
string to a Long data type before comparing with numbers. Try the following
syntax instead:

SELECT *
FROM tblStuff
WHERE ((CLng(Right(PRIMARY_KEY, (Len(PRIMARY_KEY) -1)))) BETWEEN 2 AND 4);

And don't use "table" as the table's name. It's a reserved key word.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Sharp]

I have been meaning to respond to that thread,
but unfortunately my Outlook Express had since been corrupted,
and re-downloading the news did to retrieve that thread.

I have followed your instructions,
and did not find the "Order By" associated with any of the fields.
I could re-install Access and the patches,
but have decided to lay this problem to rest.

By the way, your query works like a charm.
It is more robust than the others, because you understand the structure of
my data.
Is it possible to add notes in the "SQL-View"?
I would like to add the other queries for future reference.

Cheers
Michael

 

['69 Camaro]

Hi, Michael.

I have followed your instructions,
and did not find the "Order By" associated with any of the fields.
I could re-install Access and the patches,
but have decided to lay this problem to rest.

It may be that the table was corrupted because it was created before the
latest patches were installed. I wouldn't give up. I would create a new
table and use an append query to insert the records (without the AutoNumber
field) from the old table into the new table.

By the way, your query works like a charm.
It is more robust than the others, because you understand the structure of
my data.

I can't say that I understand the structure, but I figure that it has at
least two digits following the character, based upon our previous thread.
The suggestion I gave you will accommodate a single character followed by a
number from 0 to 2147483648, so that should be plenty.

Is it possible to add notes in the "SQL-View"?

Not conveniently. You could get away with putting your notes in a separate
field, but it would look awkward in the query. For example, you could use:

SELECT thisField, thatField, "These are my notes." AS MyNotes
FROM MyTable;

"These are my notes" would be displayed in the last field in every record in
the query. You could also save a text string in the query's Properties
dialog window, by right-clicking the name of the query in the Database
Window and selecting "Properties." Type in your notes in the Description
text box, then select the "OK" button to save your change.

I would like to add the other queries for future reference.

I would recommend using the second one I suggested, as it converts the text
to a Long data type before the comparison with numbers. I don't recommend
the first one I suggested, even though it works, because it lacks this
conversion function.

Of the two possibilities that Andi suggested, the first one:

WHERE int(left(PRIMARY_KEY,2)) >=2 AND PRIMARY_KEY <=4

.. . . has some typographical errors so it doesn't work. You could try the
following syntax, but this syntax can only ever accommodate a single digit,
so it's not very useful:

SELECT *
FROM tblStuff
WHERE CInt(Right(PRIMARY_KEY,1)) >=2 AND CInt(Right(PRIMARY_KEY,1)) <=4;

Her second suggestion:

WHERE PRIMARY_KEY >="A2" AND PRIMARY_KEY <="A4"

.. . . works, but this syntax will only work perfectly for a single digit,
not multiple digits, after the characters in the text string, because
numbers as characters are evaluated a bit differently than pure numbers.
For example, if your table had the following records:

A1
A2
A3
A4
A5
A30

.. . . and Andi's second suggestion was changed to:

WHERE PRIMARY_KEY >="A2" AND PRIMARY_KEY <="A30"

.. . . then you might expect A2, A3, A4, A5, and A30 to be returned.
However, only A2, A3 and A30 would be returned because in a text string both
4 and 5 compare as _higher_ than 30, since the 4 and 5 are being compared to
the 3, not the 30. This method doesn't work for comparing digits within
strings, unless there's only one digit and it is always stored in the same
position within the string. So this method has severe limitations, too.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)