Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

coding for employer's name

Posted on 2014-04-01
16
Medium Priority
?
259 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 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 40

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

963 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