[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

code to add data to a blank field in a table

Posted on 2011-04-26
7
Medium Priority
?
264 Views
Last Modified: 2012-05-11
All

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.....
0
Comment
Question by:shaz0503
  • 3
  • 3
7 Comments
 
LVL 7

Expert Comment

by:manthanein
ID: 35473093
perhaps  what  you could  do  is add  a  formula  in the  query  something like  



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

Expert Comment

by:manthanein
ID: 35473100
you may also  use  datadd  for adding  2 months
0
 
LVL 2

Expert Comment

by:Gk
ID: 35473105
MSAccess supports DateAdd ( interval, number, date )
now for current time....

Apply them in your select query/Update them at once.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:shaz0503
ID: 35479506
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

rgds

s
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

Copy-Of-Z-ActiveEmployee-tblStat.xlsx
0
 

Author Comment

by:shaz0503
ID: 35479988
All

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

S
0
 
LVL 7

Accepted Solution

by:
manthanein earned 2000 total points
ID: 35480592
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
0
 

Author Closing Comment

by:shaz0503
ID: 35480666
Thanks so much

Works wonderfully....

rgds

S
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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month17 days, 23 hours left to enroll

829 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