query a text field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to query a text field in a table?

I'd like to determine if Column A is < Column B.

Thanks.
 
The answer may depend on what you mean by <. For example, if comparing the
strings "Abc" and "Abd", the first one is less than the second. That's
because Access is comparing the numeric value of all the characters in the
string. If you want to know if the string in Column A has fewer characters
than the string in Column B, then you need to use the Len() function. One of
the following IIF statements should give you what you want.

IIF([Column A] < [Column B],"Yes","No")

IIF(Len([Column A]) < Len([Column B]),"Yes","No")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
Is it possible to query a text field in a table?

I'd like to determine if Column A is < Column B.

Thanks.
Click to expand...

Sure. Put a criterion on Column A of

< [Column B]

Note that for a text field the comparison is *alphabetical* - that is,
"Aardvark" will sort before "Zebra", but "10224" will sort before "2".
If the text field contains numeric values and you want the comparison
to be done numerically rather than alphabetically, use instead

Val([Column A]) < Val([Column B])


John W. Vinson[MVP]
 
This is second attempt to reply, sorry if both should appear.

The text field contains only numeric values (including decimal) after
removing all % signs.

I am having trouble getting the Val correct. I've tried putting it in the
sql:

SELECT [QAR's_2005].ID, [QAR's_2005].[QAR Associated #],
[QAR's_2005].[Analysts name], [QAR's_2005].[Date turned in],
[QAR's_2005].[QAR number], [QAR's_2005].[QAR start date], [QAR's_2005].[QAR
end date], [QAR's_2005].[Timeliness/Accuracy], [QAR's_2005].[Error Rate],
[QAR's_2005].[AQL Requirement], [QAR's_2005].[Date of COTR Sig],
[QAR's_2005].[Date Issued], [QAR's_2005].[Return Date],
[QAR's_2005].[Rebuttal Yes/No]
FROM [QAR's_2005]
WHERE Val([Error Rate]) < Val([AQL Requirement]);

and get "Data type mismatch in criteria experssion"

as well as various ways within the query grid, with same error message.

I want comparison done numerically, looking for those percentage values in
the Error Rate column that are less than those percentage values in the AQL
Requirement column. So for example

Error Rate column AQL Requirement
99 99.5
100 100
88.65 89.20

Would want rows 1 and 3 returned.



John Vinson said:
Is it possible to query a text field in a table?

I'd like to determine if Column A is < Column B.

Thanks.
Click to expand...

Sure. Put a criterion on Column A of

< [Column B]

Note that for a text field the comparison is *alphabetical* - that is,
"Aardvark" will sort before "Zebra", but "10224" will sort before "2".
If the text field contains numeric values and you want the comparison
to be done numerically rather than alphabetically, use instead

Val([Column A]) < Val([Column B])


John W. Vinson[MVP]
Click to expand...
 
I am having trouble getting the Val correct. I've tried putting it in the
sql:

SELECT [QAR's_2005].ID, [QAR's_2005].[QAR Associated #],
[QAR's_2005].[Analysts name], [QAR's_2005].[Date turned in],
[QAR's_2005].[QAR number], [QAR's_2005].[QAR start date], [QAR's_2005].[QAR
end date], [QAR's_2005].[Timeliness/Accuracy], [QAR's_2005].[Error Rate],
[QAR's_2005].[AQL Requirement], [QAR's_2005].[Date of COTR Sig],
[QAR's_2005].[Date Issued], [QAR's_2005].[Return Date],
[QAR's_2005].[Rebuttal Yes/No]
FROM [QAR's_2005]
WHERE Val([Error Rate]) < Val([AQL Requirement]);

and get "Data type mismatch in criteria experssion"
Click to expand...

Hrm. The criterion looks unexceptional. My biggest concern would be
the use of ' and / in table and field names - since ' is a string
delimiter this can get pretty wierd. Could you try renaming the table
to get rid of the apostrophe?

What are the *actual contents* of the Error Rate and AQL Requirement
fields? Are there leading % signs or other nonnumeric characters, or
leading blanks?

John W. Vinson[MVP]
 
I renamed the table and removed the '

The *actual contents* include

99.5%
95%
100%
99.75%

etc. This is all I see in the fields. Doesn't appear to be any leading %
signs or blanks.



John Vinson said:
I am having trouble getting the Val correct. I've tried putting it in the
sql:

SELECT [QAR's_2005].ID, [QAR's_2005].[QAR Associated #],
[QAR's_2005].[Analysts name], [QAR's_2005].[Date turned in],
[QAR's_2005].[QAR number], [QAR's_2005].[QAR start date], [QAR's_2005].[QAR
end date], [QAR's_2005].[Timeliness/Accuracy], [QAR's_2005].[Error Rate],
[QAR's_2005].[AQL Requirement], [QAR's_2005].[Date of COTR Sig],
[QAR's_2005].[Date Issued], [QAR's_2005].[Return Date],
[QAR's_2005].[Rebuttal Yes/No]
FROM [QAR's_2005]
WHERE Val([Error Rate]) < Val([AQL Requirement]);

and get "Data type mismatch in criteria experssion"
Click to expand...

Hrm. The criterion looks unexceptional. My biggest concern would be
the use of ' and / in table and field names - since ' is a string
delimiter this can get pretty wierd. Could you try renaming the table
to get rid of the apostrophe?

What are the *actual contents* of the Error Rate and AQL Requirement
fields? Are there leading % signs or other nonnumeric characters, or
leading blanks?

John W. Vinson[MVP]
Click to expand...
 
I renamed the table and removed the '

The *actual contents* include

99.5%
95%
100%
99.75%

etc. This is all I see in the fields. Doesn't appear to be any leading %
signs or blanks.
Click to expand...

VERY ODD! This may be a bug, or at least a misfeature. In A2003 in the
Immediate Window:

?Val("99.5")
99.5
?Val("99.5%")
Run Time Error 13: Type Mismatch

Same error occurs with CCur() and CDbl().

Try using the Replace() function to get rid of the percent sign:

?val(replace("99.5%", "%", ""))
99.5

I'll bring up this anomaly to the Powers That Be.

John W. Vinson[MVP]
 
Removed all % in the table. The 'replace' function not working for Access
2000.

Run the sql, Val([Error Rate]) < Val ([AQL Requirement]);
still get error

On Query Grid: When I put in the criteria line of the Error Rate Column

<AQL Requirement

It returns all in the table, even those less than comparison field in AQL
Requirement column.

When I change it to > AQL Requirement (still in criteria line of Error Rate
column)

It returns nothing, although the majority of the records actually have Error
Rate greater than AQL Requirement.

Ideas?
 
KMT said:
Removed all % in the table. The 'replace' function not working for Access
2000.
Click to expand...

Actually, the replace function does work in Access 2000, but if you haven't
applied all of the service packs, you have to do a work-around which
involves creating a "wrapper" function that you call in your query:

Function MyReplace(Expression As String, _
Find As String, _
Replace As String) As String

MyReplace = Replace(Expression, Find, Replace)

End Function

Rather than using Replace(x, y, z) in your query, use MyReplace(x, y, z)
 
I appreciate the reply, but the real problem I'm having is determining which
of two text fields (with numbers such as 99.5, 100, etc) is greater than the
other.


Run the sql, Val([Error Rate]) < Val ([AQL Requirement]);
still get error

On Query Grid: When I put in the criteria line of the Error Rate Column

<AQL Requirement

It returns all in the table, even those less than comparison field in AQL
Requirement column.

When I change it to > AQL Requirement (still in criteria line of Error Rate
column)

It returns nothing, although the majority of the records actually have Error
Rate greater than AQL Requirement.

Ideas?

KT
 
Could I ask why, if these two columns only contain values that are known to
be 1) numeric and 2) percentages, they are being stored as text and with the
'%' sign? Would it not make life easier to simply store them as floats and
leave off/remove the '%'?

I realize this doesn't answer the original question, but it might be a more
reasonable way to store your data. And to avoid the weirdness altogether.
--
Chaim


KMT said:
I appreciate the reply, but the real problem I'm having is determining which
of two text fields (with numbers such as 99.5, 100, etc) is greater than the
other.


Run the sql, Val([Error Rate]) < Val ([AQL Requirement]);
still get error

On Query Grid: When I put in the criteria line of the Error Rate Column

<AQL Requirement

It returns all in the table, even those less than comparison field in AQL
Requirement column.

When I change it to > AQL Requirement (still in criteria line of Error Rate
column)

It returns nothing, although the majority of the records actually have Error
Rate greater than AQL Requirement.

Ideas?

KT

Douglas J. Steele said:
Actually, the replace function does work in Access 2000, but if you haven't
applied all of the service packs, you have to do a work-around which
involves creating a "wrapper" function that you call in your query:

Function MyReplace(Expression As String, _
Find As String, _
Replace As String) As String

MyReplace = Replace(Expression, Find, Replace)

End Function

Rather than using Replace(x, y, z) in your query, use MyReplace(x, y, z)
Click to expand...
Click to expand...
 
Back
Top