Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

coding for employer's name

Posted on 2014-04-01
16
Medium Priority
?
258 Views
Last Modified: 2014-04-09
Hi Experts,
I have a question about to print a fprm base on the employer's name.  currently we have a employer table, and users enter the employer sometime different even it's the same name.  for example, some users enter AT & T, some enter as AT&T, some enter AT & T Corp, some enter AT&T corp etc.  What I need to do is if the employer is AT & T then only the form prints out no envelope, if the employer is not AT & T then prints out the form and envelopes.  Here is my code:

if Empl = left ([Empl],6) = "AT & T" or Left([Empl],4) = "AT&T" then
   forms
else
  forms
  envelope
end if

if Empl = left ([Empl],6) = "AT & T" or Left([Empl],4) = "AT&T" then (I want to pull anthing that has AT& T in the begining, is this correct?   because when I use this, even the employer is not AT & T, the envelope did not print)

Thanks,
0
Comment
Question by:urjudo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1400 total points
ID: 39969425
try this

if left([Empl],6) = "AT & T" or Left([Empl],4) = "AT&T" then
 
' forms

else
 ' forms
  'envelope
end if
0
 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 200 total points
ID: 39969470
Why not change the way you do data entry to use a combo instead?  That way you get rid of the variations.
0
 

Author Comment

by:urjudo
ID: 39969565
Sorry Rey Obrero, I tried your suggest, for some reason, it still not printing out the envelope if I have two employers in the same record, one is At & T and other one is different employer, the form prints out fine, but the envelope is not print out for the one that is not AT & T.  this is really strange.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39969587
<if I have two employers in the same record, one is At & T and other one is different employer,>

how did it happen that you have two employer in the same record?

how are the data recorded in the [Empl] field?
0
 

Author Comment

by:urjudo
ID: 39969650
some time the guy has two different job, one is full time job, one is part time job, we have a special# like the SSN for each client, we also have a employer table that has each client's #, for example, John Doe, his special# store in client table is 012567892, on the employer table, we have his special#012567892, has employer code 225, 312  (225 is the auto# of the employer that we enter in a empolyer code for At& T, and 312 is the auto# for Mcdonald's)
I hope I explain clear
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39969666
you should have a single unique record  for an  employee, employer combination.
0
 

Author Comment

by:urjudo
ID: 39969671
I have to show the employer's name and address on the form, so if the client has two employers, the there will be two forms print out, one for each employer
0
 

Author Comment

by:urjudo
ID: 39969673
This works fine until now we have to skip the envelope if the employer is At & T
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39969695
Now, you are starting to see the effect of not having a unique record for employee, employer combination.
0
 

Author Comment

by:urjudo
ID: 39969703
how do I have a single unique record  for an  employee, employer combination?
0
 

Author Comment

by:urjudo
ID: 39969712
Do you think I should use "Loop" option (But I don't know how).  I check the query, the AT& T is the last update, so it show on the first  and the other employer show second on the query, that why the system sees the first is the At& T and did not see the second employer, so the second employer envelope did not print
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39969717
SSN                 employer code
012567892    225      ' this is one unique record for 012567892 and AT & T
012567892    312      'this is another unique record for  012567892 and Mcdonald
0
 

Author Comment

by:urjudo
ID: 39969737
That's how we store in the emplyer table
0
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 400 total points
ID: 39969770
You should have an ID field for the employer, and instruct users to not add a new record when there is a change -- update the current employer record instead.  You clearly have a lot of near-duplicates, which need to be cleaned up.  Then you can use a combo box to select the employer, with the ID in column 0 (usually not displayed), and the name in column 1.  When there is a reference to the employer in another table, it should use a foreign key field containing the employer ID, not the name.
0
 

Author Comment

by:urjudo
ID: 39969826
we do, here is the designed:
SPTable: SP#(PK), SPLN, SPFN, SPSSN, SPDOB
Client Table: clientID(auto#), case#, ClientSP#
EmployerCodeTable:  EmployerCode(Auto#), Employer name, employer address
Employer Table: EmployerID(Auto#), ClientSP#, EmployerCode
so the relationship is
ClientSP# ---> SP# (Pull client)ame)
to pull all informations:
ClientSP#--->SP#
ClientSP#(client Table)--->ClientSP#(Employer Table) --->EmployerCode(Employer Table)--->EmployerCode(EmployerCode Table).

One of the problem that I notice is when users enter the employer, they did not enter in a same format, for example, some enter as AT&T, some enter as A t & T etc, I think I should fix them and make them the same format
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 39989855
They should only have to enter the employer name once -- in the Employers table (or whatever is it called in your app).  Anywhere else the name is needed, it should be pulled from the Employers table via the ID link, and displayed in a locked control on a form.  That way it will always be the same, and if it was entered incorrectly, it only needs to be changed once, in the Employers table.

If you are seeing different versions of the employer name, that means that the name itself is stored somewhere other than the Employers table.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question