numbers

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

Guest

I made up a table with a field which originally contained text and numbers.
So left it as a text field. After entering data and then subsequently trying
to query the field I feel it would be better to just use the numbers however
when I try to query pull the data on 1 and 2 it pulls up data for 10 and 11.
I have tried to change it to a number field but as it will delete all data
entered I don't want to do it.

Can anyone tell me how to rectify this?
 
I made up a table with a field which originally contained text and numbers.
So left it as a text field. After entering data and then subsequently trying
to query the field I feel it would be better to just use the numbers however
when I try to query pull the data on 1 and 2 it pulls up data for 10 and 11.
I have tried to change it to a number field but as it will delete all data
entered I don't want to do it.

Can anyone tell me how to rectify this?
Click to expand...

It would help if you post the actual query SQL and an example of what
you get and what you wish to get.
 
table fields
Unit No (Text)
Initial Assessments (Text)
Acheived (Number)

Initial Assessments are 1 through to 11

What I want to do is: Count the Unit No. Criteria for Initial Assessments
all data for initial assessments 1 and 2. Group by Acheived.

When I do: Count Unit No. Use criteria " between 1 and 3" for criteria for
Initial Assessments. Group by achieved. I return the results I want but also
get the data for Initial Assessments 10 and 11. I realize between is usually
used for a number field.

I hope you can understand what I mean.

When I do
 
if the data in the field is all numeric characters (0-9), then you can
coerce the data type to a numeric value within the query, as

CLng([put fieldname here])

you should be able to set criteria on the calculated field, AND you don't
have to use it in the query output unless you want to - you can still "show"
the original field in the output.

hth
 
if the data in the field is all numeric characters (0-9), then you can
coerce the data type to a numeric value within the query, as

CLng([put fieldname here])

you should be able to set criteria on the calculated field, AND you don't
have to use it in the query output unless you want to - you can still "show"
the original field in the output.

hth

JEM said:
table fields
Unit No (Text)
Initial Assessments (Text)
Acheived (Number)

Initial Assessments are 1 through to 11

What I want to do is: Count the Unit No. Criteria for Initial Assessments
all data for initial assessments 1 and 2. Group by Acheived.

When I do: Count Unit No. Use criteria " between 1 and 3" for criteria for
Initial Assessments. Group by achieved. I return the results I want but also
get the data for Initial Assessments 10 and 11. I realize between is usually
used for a number field.

I hope you can understand what I mean.

When I do
Click to expand...
Click to expand...

You can also use
Val([FieldName])
 
Sorry were have I to put the 1 and 2?

In
Val([Initial Assessment])

or in
CLng([Initial Assessment])

As you can no doubt tell I am relatively new to Access. Thanks for your
patience
fredg said:
if the data in the field is all numeric characters (0-9), then you can
coerce the data type to a numeric value within the query, as

CLng([put fieldname here])

you should be able to set criteria on the calculated field, AND you don't
have to use it in the query output unless you want to - you can still "show"
the original field in the output.

hth

JEM said:
table fields
Unit No (Text)
Initial Assessments (Text)
Acheived (Number)

Initial Assessments are 1 through to 11

What I want to do is: Count the Unit No. Criteria for Initial Assessments
all data for initial assessments 1 and 2. Group by Acheived.

When I do: Count Unit No. Use criteria " between 1 and 3" for criteria for
Initial Assessments. Group by achieved. I return the results I want but also
get the data for Initial Assessments 10 and 11. I realize between is usually
used for a number field.

I hope you can understand what I mean.

When I do

:

On Sun, 19 Aug 2007 08:52:01 -0700, JEM wrote:

I made up a table with a field which originally contained text and numbers.
So left it as a text field. After entering data and then subsequently trying
to query the field I feel it would be better to just use the numbers however
when I try to query pull the data on 1 and 2 it pulls up data for 10 and 11.
I have tried to change it to a number field but as it will delete all data
entered I don't want to do it.

Can anyone tell me how to rectify this?

It would help if you post the actual query SQL and an example of what
you get and what you wish to get.
Click to expand...
Click to expand...

You can also use
Val([FieldName])
Click to expand...
 
Sorry were have I to put the 1 and 2?

In
Val([Initial Assessment])

or in
CLng([Initial Assessment])

As you can no doubt tell I am relatively new to Access. Thanks for your
patience
fredg said:
if the data in the field is all numeric characters (0-9), then you can
coerce the data type to a numeric value within the query, as

CLng([put fieldname here])

you should be able to set criteria on the calculated field, AND you don't
have to use it in the query output unless you want to - you can still "show"
the original field in the output.

hth

table fields
Unit No (Text)
Initial Assessments (Text)
Acheived (Number)

Initial Assessments are 1 through to 11

What I want to do is: Count the Unit No. Criteria for Initial Assessments
all data for initial assessments 1 and 2. Group by Acheived.

When I do: Count Unit No. Use criteria " between 1 and 3" for criteria for
Initial Assessments. Group by achieved. I return the results I want but
also
get the data for Initial Assessments 10 and 11. I realize between is
usually
used for a number field.

I hope you can understand what I mean.

When I do

:

On Sun, 19 Aug 2007 08:52:01 -0700, JEM wrote:

I made up a table with a field which originally contained text and
numbers.
So left it as a text field. After entering data and then subsequently
trying
to query the field I feel it would be better to just use the numbers
however
when I try to query pull the data on 1 and 2 it pulls up data for 10
and 11.
I have tried to change it to a number field but as it will delete all
data
entered I don't want to do it.

Can anyone tell me how to rectify this?

It would help if you post the actual query SQL and an example of what
you get and what you wish to get.
Click to expand...

You can also use
Val([FieldName])
Click to expand...
Click to expand...

In a query, add a new column.
ChangeToNumber: CLng([Initial Assessment])

or use

ChangeToNumber:Val([Initial Assessmaent])

As criteria on this new column, you would write:
Between 1 and 10
or whatever values you wish to return if you only want some of the
records..
 
Back
Top