Find Max Value of Text Field

[LeslieJ via AccessMonster.com]

My database contains an "inventory" of controlled documents and all their
past and current versions.

I have a query that searches for all the archived documents, and it shows all
of the archived versions related to that document.
For example: Doc # 01 234 V01, Doc #01 234 V02 etc.
The version field is a text field, because each document type has a different
version format (a Head Office idea, not my own).

In this query that searches for all the archived documents, I would like to
only see the last archived version, meaning the highest version in the query
for that document. I have realized that the Group By Max option does not
work because this is a text field, and have not been able to figure out
anything that works with text. Any help would be greatly appreciated!

The following is the SQL for the query: (Note: this query is a parameter
query, that gets the Document Type from a form so as to filter the query,
otherwise it would be just too big.)

SELECT [Documents Info 1].[Documents Id] AS [Document ID], [Documents Info 1].
Identifier AS [Version Identifier], [Documents Info 1].[Document Revision] AS
[Document Revision], [Documents Info 1].Amendment AS [Document Amendment],
[Documents Info 1].[Document Name], [Documents Info 1].Status AS [Document
Status Type], [Documents Info 1].[Date Received], [Documents Info 1].[Date
Issue] AS [Date of Initial Issue], [Documents Info 1].[Effective Date],
[Documents Info 1].Comments, [Documents Info 1].[Head Office DC No] AS [HO
Document Number], [Documents Info 1].[Date Archived], [Documents Info 1].
[Reason Archived]
FROM [Document Type] INNER JOIN ((Documents INNER JOIN [Document Appendiums]
ON Documents.Identifier = [Document Appendiums].Reference) INNER JOIN
[Documents Info 1] ON [Document Appendiums].Identifier = [Documents Info 1].
[Documents Id]) ON [Document Type].Identifier = Documents.[Document Type]
WHERE ((([Documents Info 1].Status)=2) AND (([Documents Info 1].[Date
Archived]) Between #1/1/2006# And #12/31/2008#) AND (([Document Type].
[Document Type])=[Forms]![Transfer Queries]![txtType]))
ORDER BY [Documents Info 1].[Documents Id], [Documents Info 1].[Document
Revision] DESC;

 

[Ron2006]

Essentially, what you must do is

as part of your query you have to parse the single document version
into whatever component parts you need and then group/etc by those
defined fields

example

Doctype:left([docversion],4)

DocVers: right([docversion], len([docversion]-4)


If these components are not fixed length then your formula is more
difficult but still what you need to do.

Ron

 

[Jeff Boyce]

Leslie

What you mean by "Max" and what Access means by "Max" are apparently not it
kilter.

If your records include a date/time field, you can use "Max" on that to find
the most recent one.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[LeslieJ via AccessMonster.com]

No Access and I are definitely not on the same page with this one )

I can't make the Document Revision text field into a Date/Time field, because
not all of the revision formats are dates.

The Document Type, Document Number, and Document Revision fields are all
separate fields. The version is already on it's own, and thinking about it
there are really only three formats for the revision - it may be 01, it may
be 1.0, or it may be a date.

Leslie

What you mean by "Max" and what Access means by "Max" are apparently not it
kilter.

If your records include a date/time field, you can use "Max" on that to find
the most recent one.

Regards

Jeff Boyce
Microsoft Office/Access MVP

My database contains an "inventory" of controlled documents and all their
past and current versions.

[quoted text clipped - 41 lines]

ORDER BY [Documents Info 1].[Documents Id], [Documents Info 1].[Document
Revision] DESC;

 

[John W. Vinson]

The Document Type, Document Number, and Document Revision fields are all
separate fields. The version is already on it's own, and thinking about it
there are really only three formats for the revision - it may be 01, it may
be 1.0, or it may be a date.

You may have painted yourself into a corner! Please place the following
revisions in correct order:

1.0
1.1
03
02
5/15/2008
3.3
05
01

Let us know the logic of your ordering in a way which can be converted into
SQL or code... if that's even possible!

 

[John Spencer]

This MIGHT work.
Generate a calculated field and then get the MAX of the calculated field

Expression to generate calculated field
IIF(IsDate(Revision),Format(CDate(Revision),"yyyymmdd"),IIF(IsNumeric(Revision),Val(Revision),Revision))

SELECT [Document Type]
, [Document Number]
, Max(IIF(IsDate(Revision), Format(CDate(Revision),"yyyymmdd"),
IIF(IsNumeric(Revision),Val(Revision),Revision))) as LastRevision
FROM [Documents Info 1]



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

No Access and I are definitely not on the same page with this one )

I can't make the Document Revision text field into a Date/Time field, because
not all of the revision formats are dates.

The Document Type, Document Number, and Document Revision fields are all
separate fields. The version is already on it's own, and thinking about it
there are really only three formats for the revision - it may be 01, it may
be 1.0, or it may be a date.

Leslie

What you mean by "Max" and what Access means by "Max" are apparently not it
kilter.

If your records include a date/time field, you can use "Max" on that to find
the most recent one.

Regards

Jeff Boyce
Microsoft Office/Access MVP

My database contains an "inventory" of controlled documents and all their
past and current versions.

[quoted text clipped - 41 lines]

ORDER BY [Documents Info 1].[Documents Id], [Documents Info 1].[Document
Revision] DESC;

 

[LeslieJ via AccessMonster.com]

Hi John, I'm sorry, I'm trying to be clear, I think I've been looking at this
too long today.

All those types that you listed below will not all be for the same document.
Say Document Type 1 is Doc #01 123 and it will be V1.0, V2.0, V3.0 etc.
Document Type 2 is Doc #12.345 and it will be V01, V02, V03 etc.
Document Type 3 will be Doc #11002233 and it will be V11/10/2007, V12/01/2008,
V15/02/2008 etc.

Which is why the Document Revision needs to be a text field. I am hoping
that there is a way to search for the maximum value regardless of the format -
in the examples above, I'd only want the Query to show V3.0, V03, or
V15/02/2008 depending of what type of document I searched for.

 

[Lord Kelvan]

ok that text field is bad you should have two numeric fields 1 to
store the doc number and the other to store the version number this
would easly allow you to find the max version number alternativally to
find the max version number what you need to do is regardless of
management you need to take their idea and but in a set of standards
on hwo data needs to be entered

if you have

V1.0
and
V01
and
V11/10/2007

it makes things complicated dooable but from a data standpoint this is
bad it makes querying hard and reporting look bad if youa re using
different values

in your table you shoudl have

Docnum Versionnum otherfileds
01 123 1.0 ...
01 123 2.0 ...
01 123 3.0 ...
01 123 3.1 ...
12.345 1.0 ...
12.345 2.0 ...
12.345 2.1 ...
12.345 2.2 ...

the document number can remain text but the version number since this
is a important field for reporting or queries should be in one format
for all documents

with your method

document type
doc#01 123 V1.0

you have to pull it to peaces before doing the actual query

maxversionnumber: max(cint(Mid([document type],InStr([document
type],"V")+1)))

should do it as long as the V is a constant identifier before the
version type be that a 2.0 a 02 or a date it should work but the V it
what makes that query work

sql version

select max(cint(Mid([document type],InStr([document type],"V")+1))) as
maxversionnumber
from documenttable;

or is the format of the field document type i am reading wrong?

Regards
Kelvan