Solved

coding for employer's name

Posted on 2014-04-01
16
252 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
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 350 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 34

Assisted Solution

by:PatHartman
PatHartman earned 50 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
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 100 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now