removing extra letters in data

[M Thran]

I have a query of a linked table, want to remove the "mm" from the query
results after each instance of reported rainfall in my column headed 24hr#
Precip# Total, the # symbols were periods in the linked table.

 

[Keven Denen]

I have a query of a linked table, want to remove the "mm" from the query
results  after each instance of reported rainfall in my column headed  24hr#
Precip# Total, the # symbols were periods in the linked table.

What fields contain the mm? Why are there mms in the data?

The # signs are there because Access doesn't allow periods in it's
field names. Go into the table design for the linked table and and set
the Caption property for that field to whatever you want to show up as
the column heading (this CAN include periods).

Keven Denen

 

[M Thran]

Hi Kevin,

Changing the caption worked great, changed it to "Estimated Precipitation"

There are mm's in the data to signify millimeters. so the data would look
much like "9mm" or "24mm" It is a text field in the table.

 

[Keven Denen]

Hi Kevin,

Changing the caption worked great, changed it to "Estimated Precipitation"

There are mm's in the data to signify millimeters. so the data would look
much like "9mm" or "24mm" It is a text field in the table.

As long as the mm are the last characters in the field, you should be
able to, in the query, use this formula for the field:

ColumnHeading: Left([Field],Len([Field]) -2)

Replcaing ColumnHeading and Field with whatever is appropriate for
your data.

Keven Denen

 

[John W. Vinson]

I have a query of a linked table, want to remove the "mm" from the query
results after each instance of reported rainfall in my column headed 24hr#
Precip# Total, the # symbols were periods in the linked table.

If you want the data as a number (which will sort numerically - 127mm will
sort BEFORE 84mm) I'd suggest adding a Number datatype field (Long Integer if
you're dealing with integer amounts, Double if you will have fractions), and
updating it to

Val([fieldname])

 

[M Thran]

Hi Kevin,
This again worked great, thanks for the help with this one.

One last issue I am having, there are blanks in the table where no rain is
estimated to fall. With your formula I receive errors in the query? Do you
have any idaes how I might instruct the expression to either ignor blanks or
return a zero value?
--
Mike Thran

Hi Kevin,

Changing the caption worked great, changed it to "Estimated Precipitation"

There are mm's in the data to signify millimeters. so the data would look
much like "9mm" or "24mm" It is a text field in the table.

As long as the mm are the last characters in the field, you should be
able to, in the query, use this formula for the field:

ColumnHeading: Left([Field],Len([Field]) -2)

Replcaing ColumnHeading and Field with whatever is appropriate for
your data.

Keven Denen

 

[Keven Denen]

Hi Kevin,
This again worked great, thanks for the help with this one.

One last issue I am having, there are blanks in the table where no rain is
estimated to fall. With your formula I receive errors in the query? Do you
have any idaes how I might instruct the expression to either ignor blanksor
return a zero value?
--
Mike Thran

As long as the mm are the last characters in the field, you should be
able to, in the query, use this formula for the field:

ColumnHeading: Left([Field],Len([Field]) -2)

Replcaing ColumnHeading and Field with whatever is appropriate for
your data.

Keven Denen

ColumnHeading: Nz(Left([Field],Len([Field]) - 2), 0)

And John is right on, you should create a new field in your table that
is a number data type and use this formula to update that new field.

Keven Denen

 

[M Thran]

Hi,

I have repaced my expression with tthe example, but still get a "#Error" in
the results. Do you have any other suggestions?
--
Mike Thran

Hi Kevin,
This again worked great, thanks for the help with this one.

One last issue I am having, there are blanks in the table where no rain is
estimated to fall. With your formula I receive errors in the query? Do you
have any idaes how I might instruct the expression to either ignor blanks or
return a zero value?
--
Mike Thran

Hi Kevin,

Changing the caption worked great, changed it to "Estimated Precipitation"

There are mm's in the data to signify millimeters. so the data would look
much like "9mm" or "24mm" It is a text field in the table.

:
On Aug 10, 12:48 pm, M Thran <[email protected].>
wrote:
I have a query of a linked table, want to remove the "mm" from the query
results after each instance of reported rainfall in my column headed 24hr#
Precip# Total, the # symbols were periods in the linked table.

What fields contain the mm? Why are there mms in the data?

The # signs are there because Access doesn't allow periods in it's
field names. Go into the table design for the linked table and and set
the Caption property for that field to whatever you want to show up as
the column heading (this CAN include periods).

Keven Denen

As long as the mm are the last characters in the field, you should be
able to, in the query, use this formula for the field:

ColumnHeading: Left([Field],Len([Field]) -2)

Replcaing ColumnHeading and Field with whatever is appropriate for
your data.

Keven Denen

ColumnHeading: Nz(Left([Field],Len([Field]) - 2), 0)

And John is right on, you should create a new field in your table that
is a number data type and use this formula to update that new field.

Keven Denen

 

[John W. Vinson]

I have a query of a linked table, want to remove the "mm" from the query
results after each instance of reported rainfall in my column headed 24hr#
Precip# Total, the # symbols were periods in the linked table.

If you want the data as a number (which will sort numerically - 127mm will
sort BEFORE 84mm) I'd suggest adding a Number datatype field (Long Integer if
you're dealing with integer amounts, Double if you will have fractions), and
updating it to

Val([fieldname])

If you have blanks, use

Val(NZ([fieldname]))

 

[Hans Up]

I have repaced my expression with tthe example, but still get a "#Error" in
the results. Do you have any other suggestions?

In the query designer, select SQL View. Copy the text from that window
and paste it your reply so we can see what you have now.

 

[M Thran]

SELECT [Compiled Forecast Data].Location, [Compiled Forecast Data].Date,
Left([High Temp#],InStr([High Temp#],"°C")-1) AS [High Temp 1], Left([Low
Temp#],InStr([Low Temp#],"°C")-1) AS [Low Temp 1], Left([Wind
Speed/Dir#],InStr([Wind Speed/Dir#],"km")-1) AS [Wind Speed 1],
Nz(Left([24hr# Precip# Total],Len([24hr# Precip# Total])-2),0) AS [Estimated
Precipitation 1]
FROM [Compiled Forecast Data];

 

[Hans Up]

SELECT [Compiled Forecast Data].Location, [Compiled Forecast Data].Date,
Left([High Temp#],InStr([High Temp#],"°C")-1) AS [High Temp 1], Left([Low
Temp#],InStr([Low Temp#],"°C")-1) AS [Low Temp 1], Left([Wind
Speed/Dir#],InStr([Wind Speed/Dir#],"km")-1) AS [Wind Speed 1],
Nz(Left([24hr# Precip# Total],Len([24hr# Precip# Total])-2),0) AS [Estimated
Precipitation 1]
FROM [Compiled Forecast Data];

Which columns are producing the "#Error" you mentioned?

If [High Temp#], [Low Temp#], or [Wind Speed/Dir#] are either Null or
empty string ("") your SELECT statement will generate errors.

Try the field expressions as individual queries. Start with these three:

SELECT
Left([High Temp#],InStr([High Temp#],"°C")-1) AS [High Temp 1]
FROM [Compiled Forecast Data];

SELECT
Left([Low Temp#],InStr([Low Temp#],"°C")-1) AS [Low Temp 1]
FROM [Compiled Forecast Data];

SELECT
Left([Wind Speed/Dir#],InStr([Wind Speed/Dir#],"km")-1) AS
[Wind Speed 1]
FROM [Compiled Forecast Data];

 

[M Thran]

Good Morning,
The string where I receive a #ERROR is in the one below
the other three work great as there is alsway data in the table for those.

Nz(Left([24hr# Precip# Total],Len([24hr# Precip# Total])-2),0) AS [Estimated
it happens when there is no precipitation in the forcast. I would like the
blanks to read zero.
Please have a look if you have a moment
thanks
--
Mike Thran

SELECT [Compiled Forecast Data].Location, [Compiled Forecast Data].Date,
Left([High Temp#],InStr([High Temp#],"°C")-1) AS [High Temp 1], Left([Low
Temp#],InStr([Low Temp#],"°C")-1) AS [Low Temp 1], Left([Wind
Speed/Dir#],InStr([Wind Speed/Dir#],"km")-1) AS [Wind Speed 1],
Nz(Left([24hr# Precip# Total],Len([24hr# Precip# Total])-2),0) AS [Estimated
Precipitation 1]
FROM [Compiled Forecast Data];

Which columns are producing the "#Error" you mentioned?

If [High Temp#], [Low Temp#], or [Wind Speed/Dir#] are either Null or
empty string ("") your SELECT statement will generate errors.

Try the field expressions as individual queries. Start with these three:

SELECT
Left([High Temp#],InStr([High Temp#],"°C")-1) AS [High Temp 1]
FROM [Compiled Forecast Data];

SELECT
Left([Low Temp#],InStr([Low Temp#],"°C")-1) AS [Low Temp 1]
FROM [Compiled Forecast Data];

SELECT
Left([Wind Speed/Dir#],InStr([Wind Speed/Dir#],"km")-1) AS
[Wind Speed 1]
FROM [Compiled Forecast Data];

 

[John W. Vinson]

Nz(Left([24hr# Precip# Total],Len([24hr# Precip# Total])-2),0) AS [Estimated

Try

Val([24hr# Precip# Total] & "")

The Val() function may be better than Left() since Left() will take a text
string and extract a text string; Val will convert a text string into a
number, and millimeters of precipitation should certainly be treated as a
number. Concatenating an empty string will give a text string (which Val will
handle) rather than a NULL (which will cause an error).

 

[Hans Up]

Val([24hr# Precip# Total] & "")

The Val() function may be better than Left() since Left() will take a text
string and extract a text string; Val will convert a text string into a
number, and millimeters of precipitation should certainly be treated as a
number. Concatenating an empty string will give a text string (which Val will
handle) rather than a NULL (which will cause an error).

Nicely done, Sir. Concise is nice.

Regards,
Hans