Where to find list of "Functions" supported by Access

[Dennis]

Access 2003

Where can I obtain a list of functions (math, Data manipulation, text, etc.) that Access supports?

Excel has many supported functions.

I have the feeling that Access supports far fewer functions. I could be wrong.

Specifically, what functions are available to calculate, manipulate data for Access reports?

If I wanted to extract the numeric data from the text data in the fields below,
what Access functions would do the job best?

What is the smartest way to extract number(s) from a Field that has mixed information like below?

Original Field NewField1 NewField2
7980KWH 7980 KWH
.8MCF .8 MCF
FLAT FLAT

I would prefer to use a function(s) rather than create separate fields for reporting purposes.

TIA Dennis

 

[fredg]

Access 2003

Where can I obtain a list of functions (math, Data manipulation, text, etc.) that Access supports?

Excel has many supported functions.

I have the feeling that Access supports far fewer functions. I could be wrong.

Specifically, what functions are available to calculate, manipulate data for Access reports?

If I wanted to extract the numeric data from the text data in the fields below,
what Access functions would do the job best?

What is the smartest way to extract number(s) from a Field that has mixed information like below?

Original Field NewField1 NewField2
7980KWH 7980 KWH
.8MCF .8 MCF
FLAT FLAT

I would prefer to use a function(s) rather than create separate fields for reporting purposes.

TIA Dennis

1) To find Access functions:

You'll find them in VBA help.
Open any Code module and click on Help.
Type
Function
in the Search for control.
You'll get a list.
Click on the ones that interest you.

2) If your data looks like your sample above,
to parse leading numbers from a field of mixed values:

= IIf(Asc([Address])<= 57,Val([Address]),"") ' Numbers
= IIf(Asc([Address])>= 65,[Address],"") ' Letters

 

[Allen Browne]

To get a list of functions supported by VBA in Access:

1. In Access 2003, open a code window, and choose MS Visual Basic Help from
the Help menu.

2. In the Table of Contents, open the first item - MS Access VB Reference.
Under that heading choose Functions.

For parsing a text field, see Left(), Mid(), Right(), Instr(), and Len().

You can also get this information by pressing F2 in the code window to open
the Object Browser. Select the VBA library, Strings class, and the functions
for handling strings are shown as Members.

Although Access is able to parse strings, there is a basic rule of
normalization that says the data should be atomic. That means you should
store only one thing in a field. If you can build a structure like that, it
is *much* more efficient than constantly parsing a non-atomic field, and it
is dead easy to concatenate the results together for display purposes (e.g.
where needed on a report).

 

[Dennis]

Good Afternoon Allen

Thanks for your knowledge and time.

Your comments are well taken. In theory, you are correct.

In the real world, when an organization has older software and cannot afford the $60,000 price tag
for an upgrade, non-theoritically-best workarounds occur.

In this situation, a "Comments" field is being used to capture data.

The "Comments Field" is composed: (the specific focus is Water, Gas, Electric Utilities)
1 a Vendor's Account number;
2nd is Start Date - Stop Service Dates and
3rd is what you see below, Utility quantity usage and Unit ID

So, in one field exported to Access, there are five pieces of data. Account #; two dates; Qty & ID

So the challenge is to extract that data with Access functions;
or import the data into Excel (which has powerful functions) then;
re-import back into Access.

I could be wrong but Left(), Mid(), Right, Len(), InStr() are not robust enough to handle even the
simple extraction that I requested - as the starting point and type of data in the field varies.

Excel's answer:

{=IF(ISERROR(--LEFT(A1,MAX(IF(ISNUMBER(--LEFT(A1,seq)),seq)))),"", _
--LEFT(A1,MAX(IF(ISNUMBER(--LEFT(A1,seq)),seq))))}

where seq = ROW(INDIRECT("1:1024")) -->Workaround the max of 7 nested functions

A1 is, of course, Cell #1 in Column A

As you can see, the first formula is an Array.

In short, can one accomplish this in Access? If so how?

Thanks! Dennis

******************************************************************************************

 

[Dennis]

This information was omitted from my previous post:

This is the information that I believe may have difficulty extracting:


Original Field NewField1 NewField2
7980KWH 7980 KWH
.8MCF .8 MCF
FLAT FLAT

If I am incorrect and I may be, please let me know what I am missing

Thanks Dennis

 

[Brendan Reynolds]

You're right that you can't (as far as I can see) do it purely by using
built-in functions in the query, but you can do it using built-in functions
in a custom VBA function and calling the custom function in the query. The
following example makes some assumptions about the data, for example that
the non-numeric part is always uppercase letters A to Z. It may need some
modification.


SELECT tblTest1.OldField, GetLetters([OldField]) AS Expr1,
Left$([OldField],Len([OldField])-Len([Expr1])) AS Expr2
FROM tblTest1;

Public Function GetLetters(ByVal strInput As String) As String

Dim strChar As String
Dim strWork As String
Dim lngCounter As Long

For lngCounter = Len(strInput) To 1 Step -1
strChar = Mid$(strInput, lngCounter, 1)
If Asc(strChar) >= Asc("A") And Asc(strChar) <= Asc("Z") Then
strWork = strChar & strWork
End If
Next lngCounter
GetLetters = strWork

End Function