Trouble with Trim Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All,

When you create a labels report, Access adds the TRIM function by default.
The problem is in Access 2000 v9.0.7616 SP-3 the TRIM function causes
"#Error" to show up in the view window instead of data. If I remove the TRIM
function and just leave the field names preceded by an equal sign it works
just fine in Access 2000.

Now, in Access 2003 removing the TRIM function from the label data causes
the same "#Error" to appear, but if I leave it there the data appears as
normal.

Why does this happen, and can it be fixed?

-Sky
 
Hello All,

When you create a labels report, Access adds the TRIM function by default.
The problem is in Access 2000 v9.0.7616 SP-3 the TRIM function causes
"#Error" to show up in the view window instead of data. If I remove the TRIM
function and just leave the field names preceded by an equal sign it works
just fine in Access 2000.

Now, in Access 2003 removing the TRIM function from the label data causes
the same "#Error" to appear, but if I leave it there the data appears as
normal.

Why does this happen, and can it be fixed?

-Sky
Click to expand...

In the Access 2003 database, if the control source is NOT an
expression, use the control source drop down to select the control
source. That might be all you need do. Other than that, I can't help.

In the Access 2000 database, it's probably just a reference issue.

Open any module in Design view (or click Ctrl + G).
On the Tools menu, click References.
Click to clear the check box for the type library or object library
marked as "Missing:."

An alternative to removing the reference is to restore the referenced
file to the path specified in the References dialog box. If the
referenced file is in a new location, clear the "Missing:" reference
and create a new reference to the file in its new folder.

See Microsoft KnowledgeBase articles:
283115 'ACC2002: References That You Must Set When You Work with
Microsoft Access'
Or for Access 97:
175484 'References to Set When Working With Microsoft Access' for
the correct ones needed,
and
160870 'VBA Functions Break in Database with Missing References' for
how to reset a missing one.

For even more information, see
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html
 
Fredg,


The control source property for the report fields looks like the following:

=TRIM([First Name] & " " & [Last Name])
=TRIM([Address])
=TRIM([City] & ", " & [State] & " " & [Zip])


The Record Source for the report is a query with the following:

SELECT DISTINCT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Birth Date],
DatePart("d",[Birth Date]) AS Expr1
FROM Clients
WHERE (((Month([Clients].[Birth Date]))=[Forms]![Control
Panel]![Birthdates]));


I'm inclined to think that Access 2000 does not support the TRIM the
function, although I can't confirm this. This could also be a bug within that
function, I'm guessing here. Seems to me TRIM should work in both Access 2000
and 2003.

-Sky
 
Fredg,

The control source property for the report fields looks like the following:

=TRIM([First Name] & " " & [Last Name])
=TRIM([Address])
=TRIM([City] & ", " & [State] & " " & [Zip])

The Record Source for the report is a query with the following:

SELECT DISTINCT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Birth Date],
DatePart("d",[Birth Date]) AS Expr1
FROM Clients
WHERE (((Month([Clients].[Birth Date]))=[Forms]![Control
Panel]![Birthdates]));

I'm inclined to think that Access 2000 does not support the TRIM the
function, although I can't confirm this. This could also be a bug within that
function, I'm guessing here. Seems to me TRIM should work in both Access 2000
and 2003.

-Sky

fredg said:
In the Access 2003 database, if the control source is NOT an
expression, use the control source drop down to select the control
source. That might be all you need do. Other than that, I can't help.

In the Access 2000 database, it's probably just a reference issue.

Open any module in Design view (or click Ctrl + G).
On the Tools menu, click References.
Click to clear the check box for the type library or object library
marked as "Missing:."

An alternative to removing the reference is to restore the referenced
file to the path specified in the References dialog box. If the
referenced file is in a new location, clear the "Missing:" reference
and create a new reference to the file in its new folder.

See Microsoft KnowledgeBase articles:
283115 'ACC2002: References That You Must Set When You Work with
Microsoft Access'
Or for Access 97:
175484 'References to Set When Working With Microsoft Access' for
the correct ones needed,
and
160870 'VBA Functions Break in Database with Missing References' for
how to reset a missing one.

For even more information, see
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html
Click to expand...
Click to expand...

Access 2000 certainly does support the Trim function.

Did you not check the reference in the Access 2000 database, as I
suggested?
Even if none are marked MISSING, follow the Quick Solution
instructions on the link I gave you in the previous post.

On the Access 2003 database, controls sources (without Trim) of:

=[First Name] & " " & [Last Name]
[Address] (from the drop-down, without the = sign is OK)
= [City] & ", " & [State] & " " & [Zip]

Should all work, as long as the name of the control is not the same as
the name of a field used within that control's control source
expression. So the first control, for example, can not be named
either "First Name' nor "Last Name". It is OK if the [Address] control
(without the = sign) is named "Address" because it is not an
expression.
 
Fredg,

After further investigation, I discovered the problem with TRIM is being
caused by code in one of my forms that calls the reports. If I just run the
report as is everything is fine, but when I open it via my form the trouble
begins. Now I just have to figure out what part of the code is causing the
problem.

Thanks Fredg for your help on this, and I apologize for sending you in the
wrong direction re: this.

-Sky


fredg said:
Fredg,

The control source property for the report fields looks like the following:

=TRIM([First Name] & " " & [Last Name])
=TRIM([Address])
=TRIM([City] & ", " & [State] & " " & [Zip])

The Record Source for the report is a query with the following:

SELECT DISTINCT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Birth Date],
DatePart("d",[Birth Date]) AS Expr1
FROM Clients
WHERE (((Month([Clients].[Birth Date]))=[Forms]![Control
Panel]![Birthdates]));

I'm inclined to think that Access 2000 does not support the TRIM the
function, although I can't confirm this. This could also be a bug within that
function, I'm guessing here. Seems to me TRIM should work in both Access 2000
and 2003.

-Sky

fredg said:
On Thu, 3 Mar 2005 08:53:03 -0800, Sky Warren wrote:

Hello All,

When you create a labels report, Access adds the TRIM function by default.
The problem is in Access 2000 v9.0.7616 SP-3 the TRIM function causes
"#Error" to show up in the view window instead of data. If I remove the TRIM
function and just leave the field names preceded by an equal sign it works
just fine in Access 2000.

Now, in Access 2003 removing the TRIM function from the label data causes
the same "#Error" to appear, but if I leave it there the data appears as
normal.

Why does this happen, and can it be fixed?

-Sky

In the Access 2003 database, if the control source is NOT an
expression, use the control source drop down to select the control
source. That might be all you need do. Other than that, I can't help.

In the Access 2000 database, it's probably just a reference issue.

Open any module in Design view (or click Ctrl + G).
On the Tools menu, click References.
Click to clear the check box for the type library or object library
marked as "Missing:."

An alternative to removing the reference is to restore the referenced
file to the path specified in the References dialog box. If the
referenced file is in a new location, clear the "Missing:" reference
and create a new reference to the file in its new folder.

See Microsoft KnowledgeBase articles:
283115 'ACC2002: References That You Must Set When You Work with
Microsoft Access'
Or for Access 97:
175484 'References to Set When Working With Microsoft Access' for
the correct ones needed,
and
160870 'VBA Functions Break in Database with Missing References' for
how to reset a missing one.

For even more information, see
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html
Click to expand...
Click to expand...

Access 2000 certainly does support the Trim function.

Did you not check the reference in the Access 2000 database, as I
suggested?
Even if none are marked MISSING, follow the Quick Solution
instructions on the link I gave you in the previous post.

On the Access 2003 database, controls sources (without Trim) of:

=[First Name] & " " & [Last Name]
[Address] (from the drop-down, without the = sign is OK)
= [City] & ", " & [State] & " " & [Zip]

Should all work, as long as the name of the control is not the same as
the name of a field used within that control's control source
expression. So the first control, for example, can not be named
either "First Name' nor "Last Name". It is OK if the [Address] control
(without the = sign) is named "Address" because it is not an
expression.
Click to expand...
 
Back
Top