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


Creating and comparing text fields

Posted on 2011-03-04
Medium Priority
Last Modified: 2012-05-11
I have a form called “frmppm” that displays Monthly Accounts, Amounts & Total of the Amounts when you enter the Person ID and Select the Period. This data is all from a table called “ppm”.
My next task is to create another text box next to my Totals that has the Annual pay of this user divided by 12. This data comes from the “Annual Pay” field from another table called “Vendors”. The Fiscal year starts on the 1st of September of a year and ends on August 31st of the following year. Example we are in Fiscal Year 2011 which is indicated by “FY11” in the "FY" field of the vendors table. If the period selected for example is October 2010, then the “Annual Pay” that should be selected from the “Vendors” table is the one that has “FY11” and divide that amount by 12 to display in this new text box. If the period selected for example is October 2009, then the “Annual Pay” that should be selected from the “Vendors” table is the one that has “FY10” and divide that amount by 12 to display in this new text box. These 2 amounts from the text boxes are suppose to be the same but if they are different I want an error message to display Saying  they are not equal. Can you please help me to accomplish this task. Attached is a copy of the database with the ppm table, query and form. Also Attached is a copy of the Vendors table in excel format. I have also hi lighted the fields that matter in the vendors table.

Question by:Chrisjack001
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
  • 3
  • 3
LVL 85
ID: 35039703
You can use the DLookup function to "lookup" your value:

Me.YourTextbox = DLookup("Annual Pay", "Vendors", "FY='FY" & Cstr(Right(Me.txFY,2) - 1)) & "'")

Expert Comment

ID: 35040105
I think that is about it

Expert Comment

ID: 35040142
Sorry hit the wrong button!

I have made the requested changes (and a couple more)

See what you think

Bye the way, before you add too much more to the database, you need to take a serious look at the table structure

It will make your life a lot harder in the future if you dont sort it out now Database11.mdb
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


Author Comment

ID: 35040332
This is it but they need to type the Person_nbr in the Person ID field as it was originally. Is it not possible to accomplish both

Accepted Solution

TinTombStone earned 2000 total points
ID: 35043039
Changed it back for you Database11.mdb

Author Comment

ID: 35052237
That Part is working again fine but it seems the error message is popping up every time you change the Month year date even when the totals are equal the error message keeps popping up. Thanks for your help again

Author Closing Comment

ID: 35062470
Please disregard my previous email. The form is working perfectly as required. You are awesome. Thanks for all your help.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

721 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