code to add data to a blank field in a table

Posted on 2011-04-26
Last Modified: 2012-05-11

Next query for my Db.......

Db is 'comprised' of two main tables

1   contains most recent staff data
2   contains information entered by user based on the staff data (information that is not captured in staff data)

I have several queries that produce information by way of queries, reports, exports etc.

I have been bothered since the beginning of this process though.

When new staff data is imported, there is no information for 'new' staff in one of the fields within the second table....and as reports are run form queries using both tables, I need to know how to add the following to an exisiting field.

eg:  Joe bloggs commences work...He has no performance agreement in place....All staff must have one within 2 months of commencing work...

How do I add (Last start date plus 2 months) to the field in the second table for joe so that his supervisor gets a report with the due date on it - instead of a blank

Obviously when there is an agreement in place, a review date will be in the field.  I can do a seperate query to add this date but then how do I 'meld' the two fields to one....

as always, any help appreciated.....
Question by:shaz0503
    LVL 7

    Expert Comment

    perhaps  what  you could  do  is add  a  formula  in the  query  something like  

    datefield: iif([review date] is null, [laststart]  + 60, [review date])
    LVL 7

    Expert Comment

    you may also  use  datadd  for adding  2 months
    LVL 2

    Expert Comment

    MSAccess supports DateAdd ( interval, number, date )
    now for current time....

    Apply them in your select query/Update them at once.

    Author Comment

    Thanks all

    manthanein - by adding this to the SQL - seems to remove all records with blank SoE review date

    manthanein and Gk - where and how do I add DateAdd

    I have attached the code and an export of the query so you can see what I am trying to do........

    In column J of the export there are blanks...this is ok...however, I need to add a date to column K where there is a blank that is 2 months from Last Start Date (column E).  This will then allow me to report on those new staff and when a performance agreement is due


    SELECT [_qrytblStatementDetails].[Department Description], [_qrytblStatementDetails].[Employee ID], [_qrytblStatementDetails].Surname, [_qrytblStatementDetails].[Given Name], [_qrytblStatementDetails].[Last Start Date], [_qrytblStatementDetails].[Employment Term Description], [_qrytblStatementDetails].[Eligibility Group], [_qrytblStatementDetails].[Supervisor Name], [_qrytblStatementDetails].[Current SoE], [_qrytblStatementDetails].[SoE on File], [_qrytblStatementDetails].[Date SoE signed], [_qrytblStatementDetails].[SoE review date], [_qrytblStatementDetails].education, [_qrytblStatementDetails].research, [_qrytblStatementDetails].service, [_qrytblStatementDetails].Comments
    FROM _qrytblStatementDetails;

    Open in new window


    Author Comment


    I have been having another look at this and wonder if the following scenario is possible

    Can I import new data and somehow get a table to report new employees (would not be in the existing 'EmployeeData' table which is 'overwritten' on each new import).  

    Could I then add these new employees to the 'tblStatementDetails' table and add the SoE review date as - Import date plus 60 days -

    The more I look at this and play with ideas the more confused and frustrated I get

    LVL 7

    Accepted Solution

    you can try  to add this  field in your query

    NewDateSOESigned: IIf(IsNull([_qrytblStatementDetails]![SoE review date]),DateAdd("m",2,[_qrytblStatementDetails]![Last Start Date]),[_qrytblStatementDetails]![SoE review date])

    this should work

    Author Closing Comment

    Thanks so much

    Works wonderfully....



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    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

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    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, when working with VBA, learn some techniques for writing readable and easily maintained code.

    758 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

    11 Experts available now in Live!

    Get 1:1 Help Now