Please help!

[Guest]

Good morning,

I am trying to write a formula in my Select query to extract a piece of data
from a field called "A". The format of the data (for most records) for field
A is "AAA BBBB 200x". What I like to extract is the date portion of the
field, which would be "200x" from the above example. Additionally, some
records the date can be 6-digit (e.g. 06200x) and my not always be at the end
of the field. Also a few records may have more than 3 components (e.g. "AAA
BBBB CCC 06200x").

Ideally, I would like to extract the date piece from the field no matter
where they are located. Since the date piece is located at the end for most
records, I am happy with just to get from most records.

The formula that I have tried to use is:

Mid(Trim([A]![A]),1,InStrRev(Trim([A]![A])," "))

For some reason, I keep getting a Data Mismatch error.

Please help!

Thanks.

 

[Baz]

You've probably got some null values in your field. Also, your logic seems
a bit awry. This is gonna get really messy, so I would be inclined to write
a function to do it. This function (which I haven't tested) handles null
values, zero-length values, all-spaces values and values with no spaces
(handling all of which would make for a very complex expression if you tried
to do it all in the query):

Public Function GetAfterSpace(ByRef SourceString As Variant) As String

Dim intLastSpace As Integer

If Trim(Nz(SourceString)) = "" Then
GetAfterSpace = ""
Else
intLastSpace = InStrRev(Trim(SourceString)," ")
If intLastSpace = 0 Then
GetAfterSpace = ""
Else
GetAfterSpace = Mid(Trim(SourceString), intLastSpace + 1)
End If
End If

End Function

Thereafter, the expression in your query simply becomes:

GetAfterSpace(A!A)

 

[John Spencer]

I might try the following.

Mid(Trim(A),InStrRev(" " & Trim(A)," "))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Hi John,

Thanks for the formula. It works. Do you think we can take it to the next
level by telling the formula to check whether it is a date? If not a date,
returns null.

When I try the formula below, it's not recognizing.

isdate(Mid(Trim(A),InStrRev(" " & Trim(A)," ")))

It returns 0, instead of -1.

Thanks again.

I might try the following.

Mid(Trim(A),InStrRev(" " & Trim(A)," "))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Good morning,

I am trying to write a formula in my Select query to extract a piece of
data
from a field called "A". The format of the data (for most records) for
field
A is "AAA BBBB 200x". What I like to extract is the date portion of the
field, which would be "200x" from the above example. Additionally, some
records the date can be 6-digit (e.g. 06200x) and my not always be at the
end
of the field. Also a few records may have more than 3 components (e.g.
"AAA
BBBB CCC 06200x").

Ideally, I would like to extract the date piece from the field no matter
where they are located. Since the date piece is located at the end for
most
records, I am happy with just to get from most records.

The formula that I have tried to use is:

Mid(Trim([A]![A]),1,InStrRev(Trim([A]![A])," "))

For some reason, I keep getting a Data Mismatch error.

Please help!

Thanks.

 

[Guest]

Baz,

Thanks for your help. Unfortunately, I need a formula for my Select query.
Thanks again.

You've probably got some null values in your field. Also, your logic seems
a bit awry. This is gonna get really messy, so I would be inclined to write
a function to do it. This function (which I haven't tested) handles null
values, zero-length values, all-spaces values and values with no spaces
(handling all of which would make for a very complex expression if you tried
to do it all in the query):

Public Function GetAfterSpace(ByRef SourceString As Variant) As String

Dim intLastSpace As Integer

If Trim(Nz(SourceString)) = "" Then
GetAfterSpace = ""
Else
intLastSpace = InStrRev(Trim(SourceString)," ")
If intLastSpace = 0 Then
GetAfterSpace = ""
Else
GetAfterSpace = Mid(Trim(SourceString), intLastSpace + 1)
End If
End If

End Function

Thereafter, the expression in your query simply becomes:

GetAfterSpace(A!A)

Good morning,

I am trying to write a formula in my Select query to extract a piece of data
from a field called "A". The format of the data (for most records) for field
A is "AAA BBBB 200x". What I like to extract is the date portion of the
field, which would be "200x" from the above example. Additionally, some
records the date can be 6-digit (e.g. 06200x) and my not always be at the end
of the field. Also a few records may have more than 3 components (e.g. "AAA
BBBB CCC 06200x").

Ideally, I would like to extract the date piece from the field no matter
where they are located. Since the date piece is located at the end for most
records, I am happy with just to get from most records.

The formula that I have tried to use is:

Mid(Trim([A]![A]),1,InStrRev(Trim([A]![A])," "))

For some reason, I keep getting a Data Mismatch error.

Please help!

Thanks.

 

[Baz]

That's what I gave you. You create a public function in a module, and then
you use it in your query just as you would any built-in Access/VBA function.

SELECT GetAfterSpace(A) AS TheDate FROM A

Baz,

Thanks for your help. Unfortunately, I need a formula for my Select query.
Thanks again.

You've probably got some null values in your field. Also, your logic seems
a bit awry. This is gonna get really messy, so I would be inclined to write
a function to do it. This function (which I haven't tested) handles null
values, zero-length values, all-spaces values and values with no spaces
(handling all of which would make for a very complex expression if you tried
to do it all in the query):

Public Function GetAfterSpace(ByRef SourceString As Variant) As String

Dim intLastSpace As Integer

If Trim(Nz(SourceString)) = "" Then
GetAfterSpace = ""
Else
intLastSpace = InStrRev(Trim(SourceString)," ")
If intLastSpace = 0 Then
GetAfterSpace = ""
Else
GetAfterSpace = Mid(Trim(SourceString), intLastSpace + 1)
End If
End If

End Function

Thereafter, the expression in your query simply becomes:

GetAfterSpace(A!A)

Good morning,

I am trying to write a formula in my Select query to extract a piece

of
data

from a field called "A". The format of the data (for most records)

for
field

A is "AAA BBBB 200x". What I like to extract is the date portion of the
field, which would be "200x" from the above example. Additionally, some
records the date can be 6-digit (e.g. 06200x) and my not always be at

the
end

of the field. Also a few records may have more than 3 components

(e.g.
"AAA

BBBB CCC 06200x").

Ideally, I would like to extract the date piece from the field no matter
where they are located. Since the date piece is located at the end

for
most

records, I am happy with just to get from most records.

The formula that I have tried to use is:

Mid(Trim([A]![A]),1,InStrRev(Trim([A]![A])," "))

For some reason, I keep getting a Data Mismatch error.

Please help!

Thanks.

 

[John Spencer]

Well the data you posted would never return something that would be a date

062005 is NOT a date and cannot be interpreted as a date.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John,

Thanks for the formula. It works. Do you think we can take it to the
next
level by telling the formula to check whether it is a date? If not a
date,
returns null.

When I try the formula below, it's not recognizing.

isdate(Mid(Trim(A),InStrRev(" " & Trim(A)," ")))

It returns 0, instead of -1.

Thanks again.

I might try the following.

Mid(Trim(A),InStrRev(" " & Trim(A)," "))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Good morning,

I am trying to write a formula in my Select query to extract a piece of
data
from a field called "A". The format of the data (for most records) for
field
A is "AAA BBBB 200x". What I like to extract is the date portion of
the
field, which would be "200x" from the above example. Additionally,
some
records the date can be 6-digit (e.g. 06200x) and my not always be at
the
end
of the field. Also a few records may have more than 3 components (e.g.
"AAA
BBBB CCC 06200x").

Ideally, I would like to extract the date piece from the field no
matter
where they are located. Since the date piece is located at the end for
most
records, I am happy with just to get from most records.

The formula that I have tried to use is:

Mid(Trim([A]![A]),1,InStrRev(Trim([A]![A])," "))

For some reason, I keep getting a Data Mismatch error.

Please help!

Thanks.

 

[Guest]

John,

I agree with you. Plus, I am dealing with the data that are not consistent
across the board.

What I did was I took an alternate route. Below is my modified formula:

iif(isnumeric(Mid(Trim(A),InStrRev(" " & Trim(A)," ")))=-1 and
len(Mid(Trim(A),InStrRev(" " & Trim(A)," ")))>3,Mid(Trim(A),InStrRev(" " &
Trim(A)," "))),null)

Thanks again.

Well the data you posted would never return something that would be a date

062005 is NOT a date and cannot be interpreted as a date.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John,

Thanks for the formula. It works. Do you think we can take it to the
next
level by telling the formula to check whether it is a date? If not a
date,
returns null.

When I try the formula below, it's not recognizing.

isdate(Mid(Trim(A),InStrRev(" " & Trim(A)," ")))

It returns 0, instead of -1.

Thanks again.

I might try the following.

Mid(Trim(A),InStrRev(" " & Trim(A)," "))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Good morning,

I am trying to write a formula in my Select query to extract a piece of
data
from a field called "A". The format of the data (for most records) for
field
A is "AAA BBBB 200x". What I like to extract is the date portion of
the
field, which would be "200x" from the above example. Additionally,
some
records the date can be 6-digit (e.g. 06200x) and my not always be at
the
end
of the field. Also a few records may have more than 3 components (e.g.
"AAA
BBBB CCC 06200x").

Ideally, I would like to extract the date piece from the field no
matter
where they are located. Since the date piece is located at the end for
most
records, I am happy with just to get from most records.

The formula that I have tried to use is:

Mid(Trim([A]![A]),1,InStrRev(Trim([A]![A])," "))

For some reason, I keep getting a Data Mismatch error.

Please help!

Thanks.

 

[Guest]

Hi Baz,

I just tried your function, and it worked. Just to verify, it is to extract
the last component in the field, right? It is not to extract a date piece
from anywhere in the field?

Thanks again.

That's what I gave you. You create a public function in a module, and then
you use it in your query just as you would any built-in Access/VBA function.

SELECT GetAfterSpace(A) AS TheDate FROM A

Baz,

Thanks for your help. Unfortunately, I need a formula for my Select query.
Thanks again.

You've probably got some null values in your field. Also, your logic seems
a bit awry. This is gonna get really messy, so I would be inclined to write
a function to do it. This function (which I haven't tested) handles null
values, zero-length values, all-spaces values and values with no spaces
(handling all of which would make for a very complex expression if you tried
to do it all in the query):

Public Function GetAfterSpace(ByRef SourceString As Variant) As String

Dim intLastSpace As Integer

If Trim(Nz(SourceString)) = "" Then
GetAfterSpace = ""
Else
intLastSpace = InStrRev(Trim(SourceString)," ")
If intLastSpace = 0 Then
GetAfterSpace = ""
Else
GetAfterSpace = Mid(Trim(SourceString), intLastSpace + 1)
End If
End If

End Function

Thereafter, the expression in your query simply becomes:

GetAfterSpace(A!A)

Good morning,

I am trying to write a formula in my Select query to extract a piece of
data
from a field called "A". The format of the data (for most records) for
field
A is "AAA BBBB 200x". What I like to extract is the date portion of the
field, which would be "200x" from the above example. Additionally, some
records the date can be 6-digit (e.g. 06200x) and my not always be at the
end
of the field. Also a few records may have more than 3 components (e.g.
"AAA
BBBB CCC 06200x").

Ideally, I would like to extract the date piece from the field no matter
where they are located. Since the date piece is located at the end for
most
records, I am happy with just to get from most records.

The formula that I have tried to use is:

Mid(Trim([A]![A]),1,InStrRev(Trim([A]![A])," "))

For some reason, I keep getting a Data Mismatch error.

Please help!

Thanks.

 

[Guest]

John,

I have a little problem.

I have an Excel file connected to the Select query that I have been working
on for a user. When I try to connect between Access and Excel, I got an
error message "Undefined function 'InStrRev' in expression."

Do you know why? It looks like the Query Wizard in Excel is not registering
with the function 'InStrRev'.

Thanks.

Well the data you posted would never return something that would be a date

062005 is NOT a date and cannot be interpreted as a date.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John,

Thanks for the formula. It works. Do you think we can take it to the
next
level by telling the formula to check whether it is a date? If not a
date,
returns null.

When I try the formula below, it's not recognizing.

isdate(Mid(Trim(A),InStrRev(" " & Trim(A)," ")))

It returns 0, instead of -1.

Thanks again.

I might try the following.

Mid(Trim(A),InStrRev(" " & Trim(A)," "))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Good morning,

I am trying to write a formula in my Select query to extract a piece of
data
from a field called "A". The format of the data (for most records) for
field
A is "AAA BBBB 200x". What I like to extract is the date portion of
the
field, which would be "200x" from the above example. Additionally,
some
records the date can be 6-digit (e.g. 06200x) and my not always be at
the
end
of the field. Also a few records may have more than 3 components (e.g.
"AAA
BBBB CCC 06200x").

Ideally, I would like to extract the date piece from the field no
matter
where they are located. Since the date piece is located at the end for
most
records, I am happy with just to get from most records.

The formula that I have tried to use is:

Mid(Trim([A]![A]),1,InStrRev(Trim([A]![A])," "))

For some reason, I keep getting a Data Mismatch error.

Please help!

Thanks.

 

[Guest]

Baz,

I have a little problem.

I have an Excel file connected to the Select query that I have been working
on for a user. When I try to connect between Access and Excel, I got an
error message "Undefined function 'GetAfterSpace' in expression."

Do you know why? It looks like the Query Wizard in Excel is not registering
with the function 'GetAfterSpace'. Is it a problem with Reference? If it
is, do you know what reference I need?

Thanks.

That's what I gave you. You create a public function in a module, and then
you use it in your query just as you would any built-in Access/VBA function.

SELECT GetAfterSpace(A) AS TheDate FROM A

Baz,

Thanks for your help. Unfortunately, I need a formula for my Select query.
Thanks again.

You've probably got some null values in your field. Also, your logic seems
a bit awry. This is gonna get really messy, so I would be inclined to write
a function to do it. This function (which I haven't tested) handles null
values, zero-length values, all-spaces values and values with no spaces
(handling all of which would make for a very complex expression if you tried
to do it all in the query):

Public Function GetAfterSpace(ByRef SourceString As Variant) As String

Dim intLastSpace As Integer

If Trim(Nz(SourceString)) = "" Then
GetAfterSpace = ""
Else
intLastSpace = InStrRev(Trim(SourceString)," ")
If intLastSpace = 0 Then
GetAfterSpace = ""
Else
GetAfterSpace = Mid(Trim(SourceString), intLastSpace + 1)
End If
End If

End Function

Thereafter, the expression in your query simply becomes:

GetAfterSpace(A!A)

Good morning,

I am trying to write a formula in my Select query to extract a piece of
data
from a field called "A". The format of the data (for most records) for
field
A is "AAA BBBB 200x". What I like to extract is the date portion of the
field, which would be "200x" from the above example. Additionally, some
records the date can be 6-digit (e.g. 06200x) and my not always be at the
end
of the field. Also a few records may have more than 3 components (e.g.
"AAA
BBBB CCC 06200x").

Ideally, I would like to extract the date piece from the field no matter
where they are located. Since the date piece is located at the end for
most
records, I am happy with just to get from most records.

The formula that I have tried to use is:

Mid(Trim([A]![A]),1,InStrRev(Trim([A]![A])," "))

For some reason, I keep getting a Data Mismatch error.

Please help!

Thanks.

 

[Baz]

Correct, yes. As has been pointed out elsewhere, your "dates" don't
actually look like dates, but if there is a reliable pattern to the data by
which the "date" component can be identified I daresay we could come up with
a function which could extract it. BTW, you should find whoever designed
this field and beat them to death with a large book on database design.

Hi Baz,

I just tried your function, and it worked. Just to verify, it is to extract
the last component in the field, right? It is not to extract a date piece
from anywhere in the field?

Thanks again.

That's what I gave you. You create a public function in a module, and then
you use it in your query just as you would any built-in Access/VBA function.

SELECT GetAfterSpace(A) AS TheDate FROM A

Baz,

Thanks for your help. Unfortunately, I need a formula for my Select query.
Thanks again.

:

You've probably got some null values in your field. Also, your

logic
seems

a bit awry. This is gonna get really messy, so I would be inclined

to
write

a function to do it. This function (which I haven't tested) handles null
values, zero-length values, all-spaces values and values with no spaces
(handling all of which would make for a very complex expression if

you
tried

to do it all in the query):

Public Function GetAfterSpace(ByRef SourceString As Variant) As String

Dim intLastSpace As Integer

If Trim(Nz(SourceString)) = "" Then
GetAfterSpace = ""
Else
intLastSpace = InStrRev(Trim(SourceString)," ")
If intLastSpace = 0 Then
GetAfterSpace = ""
Else
GetAfterSpace = Mid(Trim(SourceString), intLastSpace + 1)
End If
End If

End Function

Thereafter, the expression in your query simply becomes:

GetAfterSpace(A!A)

Good morning,

I am trying to write a formula in my Select query to extract a

piece
of

data
from a field called "A". The format of the data (for most

records)
for

field
A is "AAA BBBB 200x". What I like to extract is the date portion

of
the

field, which would be "200x" from the above example.

Additionally,
some

records the date can be 6-digit (e.g. 06200x) and my not always be

at
the

end
of the field. Also a few records may have more than 3 components (e.g.
"AAA
BBBB CCC 06200x").

Ideally, I would like to extract the date piece from the field no matter
where they are located. Since the date piece is located at the

end
for

most
records, I am happy with just to get from most records.

The formula that I have tried to use is:

Mid(Trim([A]![A]),1,InStrRev(Trim([A]![A])," "))

For some reason, I keep getting a Data Mismatch error.

Please help!

Thanks.

 

[Baz]

Hi again,

I'm afraid that Jet's ability to execute user-defined functions in queries
only works in Access. Most (but not all) built-in functions will work in
queries run from Excel because they are VBA functions, but functions which
only exist in Access (such as Nz) or user-defined functions can only be used
in Access.

Baz,

I have a little problem.

I have an Excel file connected to the Select query that I have been working
on for a user. When I try to connect between Access and Excel, I got an
error message "Undefined function 'GetAfterSpace' in expression."

Do you know why? It looks like the Query Wizard in Excel is not registering
with the function 'GetAfterSpace'. Is it a problem with Reference? If it
is, do you know what reference I need?

Thanks.

That's what I gave you. You create a public function in a module, and then
you use it in your query just as you would any built-in Access/VBA function.

SELECT GetAfterSpace(A) AS TheDate FROM A

Baz,

Thanks for your help. Unfortunately, I need a formula for my Select query.
Thanks again.

:

You've probably got some null values in your field. Also, your

logic
seems

a bit awry. This is gonna get really messy, so I would be inclined

to
write

a function to do it. This function (which I haven't tested) handles null
values, zero-length values, all-spaces values and values with no spaces
(handling all of which would make for a very complex expression if

you
tried

to do it all in the query):

Public Function GetAfterSpace(ByRef SourceString As Variant) As String

Dim intLastSpace As Integer

If Trim(Nz(SourceString)) = "" Then
GetAfterSpace = ""
Else
intLastSpace = InStrRev(Trim(SourceString)," ")
If intLastSpace = 0 Then
GetAfterSpace = ""
Else
GetAfterSpace = Mid(Trim(SourceString), intLastSpace + 1)
End If
End If

End Function

Thereafter, the expression in your query simply becomes:

GetAfterSpace(A!A)

Good morning,

I am trying to write a formula in my Select query to extract a

piece
of

data
from a field called "A". The format of the data (for most

records)
for

field
A is "AAA BBBB 200x". What I like to extract is the date portion

of
the

field, which would be "200x" from the above example.

Additionally,
some

records the date can be 6-digit (e.g. 06200x) and my not always be

at
the

end
of the field. Also a few records may have more than 3 components (e.g.
"AAA
BBBB CCC 06200x").

Ideally, I would like to extract the date piece from the field no matter
where they are located. Since the date piece is located at the

end
for

most
records, I am happy with just to get from most records.

The formula that I have tried to use is:

Mid(Trim([A]![A]),1,InStrRev(Trim([A]![A])," "))

For some reason, I keep getting a Data Mismatch error.

Please help!

Thanks.

 

[Baz]

InStrRev is a fairly recent invention, and is only a VBA function in
relatively recent versions. Maybe you are using an oldish version of
Office?

John,

I have a little problem.

I have an Excel file connected to the Select query that I have been working
on for a user. When I try to connect between Access and Excel, I got an
error message "Undefined function 'InStrRev' in expression."

Do you know why? It looks like the Query Wizard in Excel is not registering
with the function 'InStrRev'.

Thanks.

Well the data you posted would never return something that would be a date

062005 is NOT a date and cannot be interpreted as a date.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John,

Thanks for the formula. It works. Do you think we can take it to the
next
level by telling the formula to check whether it is a date? If not a
date,
returns null.

When I try the formula below, it's not recognizing.

isdate(Mid(Trim(A),InStrRev(" " & Trim(A)," ")))

It returns 0, instead of -1.

Thanks again.

:

I might try the following.

Mid(Trim(A),InStrRev(" " & Trim(A)," "))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Good morning,

I am trying to write a formula in my Select query to extract a piece of
data
from a field called "A". The format of the data (for most records) for
field
A is "AAA BBBB 200x". What I like to extract is the date portion of
the
field, which would be "200x" from the above example. Additionally,
some
records the date can be 6-digit (e.g. 06200x) and my not always be at
the
end
of the field. Also a few records may have more than 3 components (e.g.
"AAA
BBBB CCC 06200x").

Ideally, I would like to extract the date piece from the field no
matter
where they are located. Since the date piece is located at the end for
most
records, I am happy with just to get from most records.

The formula that I have tried to use is:

Mid(Trim([A]![A]),1,InStrRev(Trim([A]![A])," "))

For some reason, I keep getting a Data Mismatch error.

Please help!

Thanks.

 

[Guest]

Good morning Baz,

Thanks for your responses. In order for my query to work in Excel, I took a
different approach on this field, instead of using "GetAfterSpace" or
"InStrRev".

Thanks again.

Hi again,

I'm afraid that Jet's ability to execute user-defined functions in queries
only works in Access. Most (but not all) built-in functions will work in
queries run from Excel because they are VBA functions, but functions which
only exist in Access (such as Nz) or user-defined functions can only be used
in Access.

Baz,

I have a little problem.

I have an Excel file connected to the Select query that I have been working
on for a user. When I try to connect between Access and Excel, I got an
error message "Undefined function 'GetAfterSpace' in expression."

Do you know why? It looks like the Query Wizard in Excel is not registering
with the function 'GetAfterSpace'. Is it a problem with Reference? If it
is, do you know what reference I need?

Thanks.

That's what I gave you. You create a public function in a module, and then
you use it in your query just as you would any built-in Access/VBA function.

SELECT GetAfterSpace(A) AS TheDate FROM A

Baz,

Thanks for your help. Unfortunately, I need a formula for my Select
query.
Thanks again.

:

You've probably got some null values in your field. Also, your logic
seems
a bit awry. This is gonna get really messy, so I would be inclined to
write
a function to do it. This function (which I haven't tested) handles
null
values, zero-length values, all-spaces values and values with no spaces
(handling all of which would make for a very complex expression if you
tried
to do it all in the query):

Public Function GetAfterSpace(ByRef SourceString As Variant) As String

Dim intLastSpace As Integer

If Trim(Nz(SourceString)) = "" Then
GetAfterSpace = ""
Else
intLastSpace = InStrRev(Trim(SourceString)," ")
If intLastSpace = 0 Then
GetAfterSpace = ""
Else
GetAfterSpace = Mid(Trim(SourceString), intLastSpace + 1)
End If
End If

End Function

Thereafter, the expression in your query simply becomes:

GetAfterSpace(A!A)

Good morning,

I am trying to write a formula in my Select query to extract a piece
of
data
from a field called "A". The format of the data (for most records)
for
field
A is "AAA BBBB 200x". What I like to extract is the date portion of
the
field, which would be "200x" from the above example. Additionally,
some
records the date can be 6-digit (e.g. 06200x) and my not always be at
the
end
of the field. Also a few records may have more than 3 components
(e.g.
"AAA
BBBB CCC 06200x").

Ideally, I would like to extract the date piece from the field no
matter
where they are located. Since the date piece is located at the end
for
most
records, I am happy with just to get from most records.

The formula that I have tried to use is:

Mid(Trim([A]![A]),1,InStrRev(Trim([A]![A])," "))

For some reason, I keep getting a Data Mismatch error.

Please help!

Thanks.