isnumeric

[ted]

Have a report that requires one of the fields to be numeric. When I enter
'isnumeric' in the criteria it puts """'s around it so what is the syntax
for that in the qsl form? TIA

 

[John W. Vinson]

Have a report that requires one of the fields to be numeric. When I enter
'isnumeric' in the criteria it puts """'s around it so what is the syntax
for that in the qsl form? TIA

Use brackets to inform Access that you mean a fieldname rather than a string:
in a vacant Field cell put

IsItANumber: IsNumeric([fieldname])

You can use a criterion of True (or False) on this field to select the desired
records.

 

[Douglas J. Steele]

Add a computed column by wrapping the function around the field in question

IsNumeric([NameOfField])

then put True as the criteria under that field.

 

[Rob Parker]

IsNumeric is a built-in function, which returns True or False, depending on
whether the argument passed to it is a number. You can't use it as a
criterion in a query.

What you can do, though, is use it in a calculated field in the query, to
return only numeric values from a field, and return Null if the value is not
numeric. You'd use the query as the recordsource for your report, rather
than the original table, and use the calculated field rather than the
original field in a bound textbox control (or an expression). The
calculated field in the query will be an expression such as:

MyNumericField: IIf(IsNumeric([MyField]),[MyField],Null)

MyNumericField is the alias for the calculated field - you can give it any
name you want (except the name of any existing field in the query's
table(s)). Change MyField to the actual name of the field.

HTH,

Rob

 

[ted]

Sorry I did not make myself clear:

Have a report
click design
select report under edit
record source on the report click ...

this produces a sql query builder
In the criteria of one of the fields need to find out if the text field is
numeric

Tried isnumeric([fieldname]) & get errors printing the report

Tried a number of variation but unable to get rid of the errors on the
report

 

[John W. Vinson]

Sorry I did not make myself clear:

Have a report
click design
select report under edit
record source on the report click ...

this produces a sql query builder
In the criteria of one of the fields need to find out if the text field is
numeric

Tried isnumeric([fieldname]) & get errors printing the report

Tried a number of variation but unable to get rid of the errors on the
report

Since we have no way to know what you tried or where, or what the errors might
have been, it's more than a bit hard to give you any useful help.

Please open the query in SQL view and copy and paste the SQL text to a message
here. Indicate what field you're trying to check to see if it's numeric; tell
us what you want to happen if it is or isn't numeric; and indicate the nature
of the errors.

We are volunteers, donating time to help you. But we can't help if we can't
see the actual nature of the problem!

 

[ted]

Sorry I did not make myself clear:

Have a report
click design
select report under edit
record source on the report click ...

this produces a sql query builder
In the criteria of one of the fields need to find out if the text field is
numeric

Tried isnumeric([fieldname]) & get errors printing the report

Tried a number of variation but unable to get rid of the errors on the
report

Since we have no way to know what you tried or where, or what the errors might
have been, it's more than a bit hard to give you any useful help.

Please open the query in SQL view and copy and paste the SQL text to a message
here. Indicate what field you're trying to check to see if it's numeric; tell
us what you want to happen if it is or isn't numeric; and indicate the nature
of the errors.

We are volunteers, donating time to help you. But we can't help if we can't
see the actual nature of the problem!

Ok this is what i put in the criteria: isnumeric([field name])
& printing failed with #error's
will work on the sql view & print here

 

[Douglas J. Steele]

Sorry I did not make myself clear:

Have a report
click design
select report under edit
record source on the report click ...

this produces a sql query builder
In the criteria of one of the fields need to find out if the text field
is
numeric

Tried isnumeric([fieldname]) & get errors printing the report

Tried a number of variation but unable to get rid of the errors on the
report

Since we have no way to know what you tried or where, or what the errors
might
have been, it's more than a bit hard to give you any useful help.

Please open the query in SQL view and copy and paste the SQL text to a
message
here. Indicate what field you're trying to check to see if it's numeric;
tell
us what you want to happen if it is or isn't numeric; and indicate the
nature
of the errors.

We are volunteers, donating time to help you. But we can't help if we
can't
see the actual nature of the problem!

Ok this is what i put in the criteria: isnumeric([field name])
& printing failed with #error's
will work on the sql view & print here

You cannot use isnumeric([field name]) as a criteria. You need to put that
as a calculated field (i.e.: put it in an empty cell on the Field row of
your query), and then put True as the criteria for that calculated field.

 

[Rob Parker]

Hi Doug,

The OP got this information from (at least) three responses to his original
post (yours, John Vinson's, and mine). This follow-up is the same as his
original question, and he still hasn't responded to John Vinson's request to
post the SQL of his recordsource/query. But since it's almost a week since
the last action in this thread, it doesn't seem to be something that's
critical for him ;-)

To the OP ("ted"):
You can view the SQL of your report's recordsource query by opening the
query, then selecting View - SQL View. Cut/paste that into your reply, and
maybe someone will be able to assist.

Rob

Sorry I did not make myself clear:

Have a report
click design
select report under edit
record source on the report click ...

this produces a sql query builder
In the criteria of one of the fields need to find out if the text field
is
numeric

Tried isnumeric([fieldname]) & get errors printing the report

Tried a number of variation but unable to get rid of the errors on the
report

Since we have no way to know what you tried or where, or what the errors
might
have been, it's more than a bit hard to give you any useful help.

Please open the query in SQL view and copy and paste the SQL text to a
message
here. Indicate what field you're trying to check to see if it's numeric;
tell
us what you want to happen if it is or isn't numeric; and indicate the
nature
of the errors.

We are volunteers, donating time to help you. But we can't help if we
can't
see the actual nature of the problem!

Ok this is what i put in the criteria: isnumeric([field name])
& printing failed with #error's
will work on the sql view & print here

You cannot use isnumeric([field name]) as a criteria. You need to put that
as a calculated field (i.e.: put it in an empty cell on the Field row of
your query), and then put True as the criteria for that calculated field.