Solved

coding for employer's name

Posted on 2014-04-01
16
256 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 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 37

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Runtime Error '3070' 5 51
access vba 5 57
CISCO WIFI 6 73
Opening a PDF or RTF file created from an Access application 3 12
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 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