How to display only 3 letters of a last name in field

[Judy-Court Clerk]

I have a muni court on Access 2003. Judge wants me to print out a list of all
"outstanding balances due" so that any of the other personnel can take a
payment from a defendant when I'm out of the office. I created a query that
works fine but with underage defendants, I have to protect their identities
and many times these are old fines and defendants don't know how much they
owe or what their case number was. So, can I reduce the amount of letters (to
3) in my 'last name' field that prints out on my query, form or report -
without messing up their last name in the table? Thus protecting a juvenile
but giving enough info for a town employee to look up what they owe... This
is my first post and I've only been on the job one year. Thank you in
advance!
Judy

 

[Dorian]

Sounds like a weird system to me.

However, In the query, you can use RIGHT(LastName,3)

If someone's name is LEE, I don't see how you are protecting their identity.
Also, a lastname does not really identify someone, that would be SSN. What if
you have two people called LEE but with different fines, wont that screw up
yopur system?

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[John W. Vinson]

On Thu, 11 Dec 2008 07:42:15 -0800, Judy-Court Clerk <Judy-Court

I have a muni court on Access 2003. Judge wants me to print out a list of all
"outstanding balances due" so that any of the other personnel can take a
payment from a defendant when I'm out of the office. I created a query that
works fine but with underage defendants, I have to protect their identities
and many times these are old fines and defendants don't know how much they
owe or what their case number was. So, can I reduce the amount of letters (to
3) in my 'last name' field that prints out on my query, form or report -
without messing up their last name in the table? Thus protecting a juvenile
but giving enough info for a town employee to look up what they owe... This
is my first post and I've only been on the job one year. Thank you in
advance!
Judy

You can use a query with a calculated field Left([LastName], 3).

This will of course show Mr. Jones, Ms. Jones, Mr. Jonathan, the other Mr.
Jonathan as JON... and won't provide much of any security.

I'm sorry but your entire premise is flawed!

 

[Judy-Court Clerk]

On Thu, 11 Dec 2008 07:42:15 -0800, Judy-Court Clerk <Judy-Court

I have a muni court on Access 2003. Judge wants me to print out a list of all
"outstanding balances due" so that any of the other personnel can take a
payment from a defendant when I'm out of the office. I created a query that
works fine but with underage defendants, I have to protect their identities
and many times these are old fines and defendants don't know how much they
owe or what their case number was. So, can I reduce the amount of letters (to
3) in my 'last name' field that prints out on my query, form or report -
without messing up their last name in the table? Thus protecting a juvenile
but giving enough info for a town employee to look up what they owe... This
is my first post and I've only been on the job one year. Thank you in
advance!
Judy

You can use a query with a calculated field Left([LastName], 3).

This will of course show Mr. Jones, Ms. Jones, Mr. Jonathan, the other Mr.
Jonathan as JON... and won't provide much of any security.

I'm sorry but your entire premise is flawed!

Yes, John, flawed indeed but it's what the Judge wants me to do. However, I
tried it just now and I am getting an error message "Undefined function
'Left' in expression." I don't know what that means. I got the same error
when I tried using 'Right' too. Can you help me? Thanks...

 

[John W. Vinson]

Yes, John, flawed indeed but it's what the Judge wants me to do. However, I
tried it just now and I am getting an error message "Undefined function
'Left' in expression." I don't know what that means. I got the same error
when I tried using 'Right' too. Can you help me? Thanks...

This appears to be the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

 

[Judy-Court Clerk]

This appears to be the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

Well, I did what you suggested... the MISSING one was checked so I unchecked
it, checked it, closed and opened and re-entered the info into the query (I
am typing this into the 'criteria' row, under the Last Name column). Same
error. Checked another reference, got a DLL error, closed and opened and
still the same 'Undefined function' error. I feel pretty inept right about
now. I really appreciate your help in all of this.... I wish this would work
for me unless I'm doing something totally wrong...
Judy

 

[John W. Vinson]

Well, I did what you suggested... the MISSING one was checked so I unchecked
it, checked it, closed and opened and re-entered the info into the query (I
am typing this into the 'criteria' row, under the Last Name column). Same
error. Checked another reference, got a DLL error, closed and opened and
still the same 'Undefined function' error. I feel pretty inept right about
now. I really appreciate your help in all of this.... I wish this would work
for me unless I'm doing something totally wrong...
Judy

The missing reference may really BE missing. What lines do you have in the
References window? If you uncheck the MISSING one and leave it unchecked, can
you compile the database (Debug... Compile <my project>) successfully?

 

[Judy-Court Clerk]

The missing reference may really BE missing. What lines do you have in the
References window? If you uncheck the MISSING one and leave it unchecked, can
you compile the database (Debug... Compile <my project>) successfully?

 

[Judy-Court Clerk]

The missing reference may really BE missing. What lines do you have in the
References window? If you uncheck the MISSING one and leave it unchecked,

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

John, I have to close off for the night but will re-post tomorrow morning
and with your help get this to work! Thanks again

***********************************************
Okay, do you want to know which lines are "checked" in the reference window?
I can do the task of unchecking the MISSING one, go to VB/Debug/Compile (I've
never done this before) and see what happens? I am working on my home
computer if that makes any difference, I have the same version as my work
file.
Judy

 

[John W. Vinson]

The missing reference may really BE missing. What lines do you have in the
References window? If you uncheck the MISSING one and leave it unchecked,

John, I have to close off for the night but will re-post tomorrow morning
and with your help get this to work! Thanks again

***********************************************
Okay, do you want to know which lines are "checked" in the reference window?

Yes... particularly the MISSING one.

I can do the task of unchecking the MISSING one, go to VB/Debug/Compile (I've
never done this before) and see what happens? I am working on my home
computer if that makes any difference, I have the same version as my work
file.
Judy

The problem is that the reference may be perfectly fine on your computer and
missing on the work computer.

The References are the "extra" program libraries that Access needs to do its
work. At the very least you should have the standard ones checked: Visual
Basic for Applications, Microsoft Access <version> Object Library, OLE
Automation, and Microsoft DAO <version> Object Library.

What may have happened is that at some point you installed some sort of add-in
or additional library; if that library existed on the computer where you first
installed it, but does not exist on your work computer, it's "missing".
Unfortunately if the VBA engine has a missing reference anywhere, references
in other libraries can get messed up too. The Left() function is in the Visual
Basic for Applications library, but even if that library is checked and not
missing, VBA may not be able to find it if some OTHER library is missing.

 

[Judy-Court Clerk]

***********************************************
Okay, do you want to know which lines are "checked" in the reference window?

Yes... particularly the MISSING one.

I can do the task of unchecking the MISSING one, go to VB/Debug/Compile (I've
never done this before) and see what happens? I am working on my home
computer if that makes any difference, I have the same version as my work
file.
Judy

The problem is that the reference may be perfectly fine on your computer and
missing on the work computer.

The References are the "extra" program libraries that Access needs to do its
work. At the very least you should have the standard ones checked: Visual
Basic for Applications, Microsoft Access <version> Object Library, OLE
Automation, and Microsoft DAO <version> Object Library.

What may have happened is that at some point you installed some sort of
add-in
or additional library; if that library existed on the computer where you
first
installed it, but does not exist on your work computer, it's "missing".
Unfortunately if the VBA engine has a missing reference anywhere, references
in other libraries can get messed up too. The Left() function is in the
Visual
Basic for Applications library, but even if that library is checked and not
missing, VBA may not be able to find it if some OTHER library is missing.
--
John W. Vinson [MVP]
**********************************************
I have 6 lines checked: Visual Basic - MS Access Obj Library - MS DAO
Library - MS VBA Extensibility 5.3 - MS forms Obj Library - MISSING: MS
Word Obj Library. I don't see OLE Automation... So, am I stuck? We're a
small court, I should probably just do it in Excel, huh? :-(

 

[John W. Vinson]

I have 6 lines checked: Visual Basic - MS Access Obj Library - MS DAO
Library - MS VBA Extensibility 5.3 - MS forms Obj Library - MISSING: MS
Word Obj Library. I don't see OLE Automation... So, am I stuck? We're a
small court, I should probably just do it in Excel, huh? :-(

<chuckle>

Don't use a crescent wrench when you want to drive nails.

Are you *USING* Microsoft Forms? are you *USING* the Word Object Library in
your Access application?

Try unchecking those two. I don't think OLE is essential either so don't worry
about it. If you uncheck those two, and don't recheck them, does your database
compile? If it compiles, does it work?

 

[Judy-Court Clerk]

<chuckle>

Don't use a crescent wrench when you want to drive nails.

Are you *USING* Microsoft Forms? are you *USING* the Word Object Library in
your Access application?

Try unchecking those two. I don't think OLE is essential either so don't worry
about it. If you uncheck those two, and don't recheck them, does your database
compile? If it compiles, does it work?

**************************************************
Am I using MS Forms? There are forms in Access that were created before I
got on the job, so I am unsure of the answer to that. Am I using Word Object?
Unknown... I just tried to uncheck MS forms and error box says "Can't remove
or reference, in use". Then I tried to uncheck MISSING: and error says"Error
in loading .dll" I went back into references and it's checked again... I was
able to uncheck it yesterday... good lord I'm gonna need a drink! ;-)

 

[John W. Vinson]

**************************************************
Am I using MS Forms? There are forms in Access that were created before I
got on the job, so I am unsure of the answer to that. Am I using Word Object?
Unknown... I just tried to uncheck MS forms and error box says "Can't remove
or reference, in use". Then I tried to uncheck MISSING: and error says"Error
in loading .dll" I went back into references and it's checked again... I was
able to uncheck it yesterday... good lord I'm gonna need a drink! ;-)

Microsoft Forms 2.0 is a separate product, part of Visual Studio. It sounds
like something in your database is assuming that Forms 2.0 is installed, and
that it *isn't* installed on the machine where you're having this problem.

Try opening the database while holding down the SHIFT key to bypass any
startup code. Then go to the VBA editor (Ctrl-G is a quick shortcut) and
remove the reference to Forms (and Word). Then compile again - you should get
an error and it should take you to whichever routine is using the reference.

Do you have a way to get in touch with whoever developed this app? Maybe they
can tell you what you can take out, or what you'll need to install to make it
work.