why this wont work!!??

[rocco]

Hello,
I have a table (CONTACT) where we collect data from patient's visit.
There is a filed named 'visit' where IT is recorded the visit number. The
filed is settled as TEXT because visit can go from 1 to 20 and then from c1
to c12.
Snce it is text, I have used Access's conversion formula to filter data into
this field. But this wont work!!!
Here is the code:

SELECT CONTACT1.ID, CONTACT1.VISIT
FROM (SELECT ID, VISIT FROM CONTACT WHERE VISIT NOT LIKE 'c*') AS CONTACT1
WHERE CINT(CONTACT1.VISIT)>12

and always I receive the message: 'data type mismatch in criteria expression'.

Can help?
Thanks

 

[Jeff Boyce]

You are trying to convert a text value into an integer in your SQL
statement.

Jeff Boyce
<Access MVP>

 

['69 Camaro]

Hi, rocco.

why this wont work!!??

It won't work because you are trying to convert records holding c1 through
c12 into integers. CInt( ) can only convert text into integers if the value
in the text string is numerical or a decimal point. You must store the "c"
part of the Visit # in a different field if you want to use this function to
convert.

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.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

 

[rocco]

That's way I have used a subquery to avoid converting data like 'c*'

 

[Rick Brandt]

That's way I have used a subquery to avoid converting data like 'c*'

I don't think you can rely on the subquery being processed *first* sending only
records without a "c" to the outer query. Try two separate queries with the
first being used as an input to the first. That might force the desired
processing order.

 

['69 Camaro]

Hi, rocco.

Sorry. I left out something important in my earlier explanation. Rick hit
the nail on the head. Jet "helps" by optimizing the query, but not
necessarily the way we expect. The entire table, which includes the unwanted
"C*" records, is used when evaluating the WHERE clause in the query to
eliminate records that aren't included in the requested data set. The
subquery's WHERE clause is evaluated afterward to eliminate records that
aren't included in that requested data set, and then the rest of the
processing goes on.

Therefore, even though you don't expect the "C*" records to be included in
the data set when the query's WHERE clause is attempting to convert the text
string to an integer data type, they are.

If I were you, I'd skip the subquery and just use the following SQL statement:

SELECT ID, VISIT
FROM CONTACT
WHERE (Val(CONTACT.VISIT) > 12);

But I'll still recommend that you store string data ("c") in a separate text
field from the current field, which should be converted to a numerical field.
Since you have two subtypes (numerical and text with numerical) of the Visit
attribute, it's acceptable to use two fields for data storage and concatenate
the values for queries whenever there's a character in the text field of the
pair.

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.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

 

[rocco]

Thanks!!
Now it is more clear!!!!
I will follow your suggestions.
Rocco