Topic: Desperate M$ Access help needed

[Clark Kent]

I am trying to make labels in Access, the third line is supposed to be City, State and Zip code, but all the report shows in the print view is # error where these should be.  Can someone clue me into what the problem is?

[Bonk]

Dunno, I usually use mailmerge in excel. Its pretty straightforward. Try exporting your access table to an excel woprksheet?

[Clark Kent]

more info, the line with city state and zip code , the line is:
=Trim([City]&","&[State]&","&[ZipCode]),
When I go to the print view it tells me to enter a parameter v\alue for Trim.
I'm lost, I did this exactly how the label wizard wanted it to be created.

[Bonk]

if its anything like the excel worksheet function then just give it one parameter at a time and remove the trailing comma.

Something like:
=concatenate(trim([City]),",",trim([State]),",",trim([ZipCode]))

Or perhaps just correct the formula you have to:
=Trim([City]&","&[State]&","&[ZipCode])

[Clark Kent]

if its anything like the excel worksheet function then just give it one parameter at a time and remove the trailing comma.

Something like:
=concatenate(trim([City]),",",trim([State]),",",trim([ZipCode]))

Or perhaps just correct the formula you have to:
=Trim([City]&","&[State]&","&[ZipCode])

I don;t know anything about giving parameters

[NJAntman]

At work we have something similar to pull address labels from Access, I think it is looking for a number value right after the word Trim that tells the program how many characters are allowed in the address. I'll check ours tomorrow and post an example of it for you.

[Lepton]

=Trim([city] & ", " & [state] & " " & [zip])

This is what the label wizard produced in Access.  This formula should appear in the control source property of the text box that this info is going into.

I think if you are getting an error it may be because you don't have the correct table or query specified for the recordset.  The formula above, I believe, also wants those spaces in there.  When I tried to removed them, I put them back in.

[Clark Kent]

=Trim([city] & ", " & [state] & " " & [zip])

This is what the label wizard produced in Access.

I think if you are getting an error it may be because you don't have the correct table or query specified for the recordset.  The formula above, I believe, also wants those spaces in there.  When I tried to removed them, I put them back in.

Well the table the info comes fromone table, and all the info is input in the table.  If I seperate the info on different lines it shows up, only when I try to put it all into the same line with City, State and Zip do I get an error message and asked for parameter inputs.

[Lepton]

What do you mean by line?  I think the only way that you can have something show up in a report is if it is a label or a textbox.  If you used the wizard, it should have created textboxes.  I think if you have an unbound control like a textbox in a form, you will get #error in the textbox, although I am not sure on that.  For sure, it is not liking something about your expression.

[Clark Kent]

What do you mean by line?  I think the only way that you can have something show up in a report is if it is a label or a textbox.  If you used the wizard, it should have created textboxes.  I think if you have an unbound control like a textbox in a form, you will get #error in the textbox, although I am not sure on that.  For sure, it is not liking something about your expression.

I need three lines, it should look like standard mailing labels do:
Name
Address
City, State Zip

Line 1- name, line 2 address, you get the idea.  Line 1 &2 work fine, line three does not.  i made this label with the label wizard, and line three jsut won't jive.  If I seperate line three's components on to other other lines I each one will show up individually, but never together.

[Lepton]

Don't know what to tell you at this point.  All I can suggest is that you check some of the things I suggested or just use the wizard again.

[TheJudge]

Okay, let's go back to some basics here (best thing to do usually)


1.  Check to make sure you are basing the report on the correct table or query.
2.  Check to make sure your query/table (I always recommend using queries) has three separate fields for city, state, & zip
3.  Make sure the names of those fields in the query/table MATCH exactly the names in the report, right down to spaces.
4.  Verify that you are not using data from an OLD database such as data stored under access 95/97 and now being run with a copy of access 2003.  (or even 2002/2003).  If memory serves, the "Trim" function went the way of the dodo bird recently.
5.  If you're using data from an older db with a newer version right now, go to this site:  http://support.microsoft.com/default.aspx?scid=kb;en-us;283166&Product=acc
6.  I personally recommend option 2 as it's easier and takes care of other problems that might crop up.

If this doesn't help...I have the following questions:

1.  Where did this data come from and how was it brought into access?
2.  What version of access are you using?
3.  what are the field properties of the field in question?

Let's see what good this does!

[Clark Kent]

Okay, let's go back to some basics here (best thing to do usually)


1.  Check to make sure you are basing the report on the correct table or query.
2.  Check to make sure your query/table (I always recommend using queries) has three separate fields for city, state, & zip
3.  Make sure the names of those fields in the query/table MATCH exactly the names in the report, right down to spaces.
4.  Verify that you are not using data from an OLD database such as data stored under access 95/97 and now being run with a copy of access 2003.  (or even 2002/2003).  If memory serves, the "Trim" function went the way of the dodo bird recently.
5.  If you're using data from an older db with a newer version right now, go to this site:  http://support.microsoft.com/default.aspx?scid=kb;en-us;283166&Product=acc
6.  I personally recommend option 2 as it's easier and takes care of other problems that might crop up.

If this doesn't help...I have the following questions:

1.  Where did this data come from and how was it brought into access?
2.  What version of access are you using?
3.  what are the field properties of the field in question?

Let's see what good this does!

OK, here's what I got:
1.  check
2.  I'm trying to produce a report, that's the assignment, using the label wizard, can't bend on this one, the table these come from are all on the same table
3.  They do match exactly to those from the table
4.  The data from the CD is supposed to be Access 2003, my version of access in 2003


Now, the data came from a text supplied CD of databases that are supposed to be used in assignments for learning Access, as I said I'm using access 2003, and what field properties in particular are you looking for?

[FMMonty]

If you'd like, E-mail me a copy of what you're doing and I'll take a look at it.

kbutterworth@bks-consulting.com

[Clark Kent]

If you'd like, E-mail me a copy of what you're doing and I'll take a look at it.

kbutterworth@bks-consulting.com

Thanks monty, sorry for the delay but I've been pretty busy, you should have it in your email right now.  had some trouble, as apparently gmail doesn't allow me to send executable files.

[FMMonty]

Hi Clark,

the problem is that they haven't upgraded the database properly, so there is a reference missing.

OK, open your mailing list and OK through the trim request.
Click the view menu, and select code.  That'll open the Visual Basic Editor
In VB click tools menu, select references.  You'll get a warning dialogue, you know that you need to fix the reference so click OK.
You will see a list of references, some ticked, some not.  One will say missing and will be checked.  Uncheck it.
Your database is now fixed.

Then go and slap whoever upgraded it