function names with dollar

[Guest]

Some of the built-in functions in the query builder seem to come in to
flavours - with and without a dollar sign suffix; e.g. InputBox / Inputbox$,
RTrim / RTrim$ and so on.

Please could someone let me know what the difference is - what the dollar
sign signifies, or how it affects the function?

Many thanks.

 

[Douglas J. Steele]

The $ at the end is a harkening back to the days where you could declare the
variable type by putting a prefix on it.

Dim MyString$

is the same as

Dim MyString As String

In terms of the string functions you're asking about, from the Help file:

Some functions have two versions: one that returns a Variant data type and
one that returns a String data type. The Variant versions are more
convenient because variants handle conversions between different types of
data automatically. They also allow Null to be propagated through an
expression. The String versions are more efficient because they use less
memory.

Consider using the String version when:

- Your program is very large and uses many variables.
- You write data directly to random-access files.

 

[Allen Browne]

In the ancient days of BASIC (1970s), you could add the dollar suffix to
declare a variable as a string, so:
Dim A$
gave a similar effect to typing:
Dim A as String

VBA still supports this ancient style, and the built-in function names do as
well. So, RTrim$() returns a *string* type, whereas RTrim() returns a
Variant type.

You can easily verify that by opening the Immediate Window (Ctrl+G), and
entering:
? RTrim$(Null)
This generates error 94, because Access is unable to output a String when
the value is Null. (Only the Variant type can be Null.) Then try:
? RTrim(Null)
and you receive Null as the answer, since this function returns a Variant,
so there is no error.

In general, you do not want to use this antiquated approach to naming your
variables, but it is actually useful when dealing with literals. For example
the default type for a literal in VBA is Integer. So if you type:
If Len(strWhere) = 0 Then
the 0 will be interpreted as an integer, while Len() returns a Long. That
means every time the line is executed, Access will haver to convert the zero
to a long in order to make the comparison. It might therefore be better to
code:
If Len(strWhere) = 0& Then
where the ampersand is the type-declaration character for a Long. You are
therefore causing Access to store the literal zero as a long, and the
type-conversion is not required.

 

[Guest]

Thanks to both for answers