I am trying to evaluate the latest occurence of a date which will go
in the field titled eligibility date. Table is set up as follows and
am using update query to update the eligibility date to the latest of
hire,award, or accident)
Emp Hire Date Accident Date Award Date eligibility
Date
Hire Date is never a null, however if there are no accidents or awards
those respective fields will be null. I tried using if and max
statements but to no avail. Any suggestions
2  1752 
"Steve" <sv*******@msn. com> wrote in message
news:2d******** *************** ***@posting.goo gle.com... I am trying to evaluate the latest occurence of a date which will go
in the field titled eligibility date. Table is set up as follows and
am using update query to update the eligibility date to the latest of
hire,award, or accident)
Emp Hire Date Accident Date Award Date eligibility
Date
Hire Date is never a null, however if there are no accidents or awards
those respective fields will be null. I tried using if and max
statements but to no avail. Any suggestions
Try Nz() function.
1. Or better still, change your table structure. I'm not sure what the exact
purpose of this database is, but does each employee only have zero or one
accidents? Probably not.
So I think you need a table of accidents, joined many to one to employee, on
the Emp field.
2. If Eligibility is just the maximum of Hire Date, Accident Date, and Award
Date, then that's 'derived data'. Meaning you can work it out from the data
you've got. So you don't need to do an update query, because you shouldn't
be storing eligiblity date atall.
Mike
Thanks for the feedback Mike. I need elgibility date because there
are calculations that need to know how many hours has someone worked
after their elibility date which changes if they have been given an
award or were in a accident. I am just struggling to come up with it
because of the nulls. I wish it wer in excel then it a simple
solution!
"Mike MacSween" <mi************ ******@btintern et.com> wrote in message news:<3f******* *************** *@pubnews.gradw ell.net>... "Steve" <sv*******@msn. com> wrote in message
news:2d******** *************** ***@posting.goo gle.com... I am trying to evaluate the latest occurence of a date which will go
in the field titled eligibility date. Table is set up as follows and
am using update query to update the eligibility date to the latest of
hire,award, or accident)
Emp Hire Date Accident Date Award Date eligibility
Date
Hire Date is never a null, however if there are no accidents or awards
those respective fields will be null. I tried using if and max
statements but to no avail. Any suggestions
Try Nz() function.
1. Or better still, change your table structure. I'm not sure what the exact
purpose of this database is, but does each employee only have zero or one
accidents? Probably not.
So I think you need a table of accidents, joined many to one to employee, on
the Emp field.
2. If Eligibility is just the maximum of Hire Date, Accident Date, and Award
Date, then that's 'derived data'. Meaning you can work it out from the data
you've got. So you don't need to do an update query, because you shouldn't
be storing eligiblity date atall.
Mike This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: smcgouga |
last post by:
Visual Basic 6. ADO 2.8
I have an as400 DB2 V5R1 datasource. Dates are defined as *ISO format
and have a range from '0001-01-01' to '9999-12-31'. I am trying to
update a date field on the database with a value of '0001-01-01'
(*LOVAL) The problem is that I need to use ADO cursors and can not
use the SQL update command:
"update tbl set dateField = '0001-01-01 where ..." (note this works no
problem)
|
by: Jordan |
last post by:
I'm working with a Repeater using a custom function inside the
<itemtemplate> to return a text string. The parameters SHOULD be (DateTime,
DateTime, Int16) but the difficulties in getting DateTimes to be evaulated
is odd. It's much easier to do (String, String, Int16) and convert the
Strings back and forth to dates to compare. I'm also expecting that the 2nd
DateTime parameter will be "null" at times and want to detect that.
I'm just...
|
by: Rookie Card |
last post by:
I know this is a very common issue but cannot find a thread that
resolves the issue.
I need to re-format my dates to "MMM d, yyyy". The problem I have is I
have an MSSQL database that allows <NULLS>.
I'm using this code to format the dates:
<%# IIF((dsCase.FieldValue("inj_beg", Container) <>
nothing),DateTime.Parse(dsCase.FieldValue("inj_beg",
Container)).ToString("MMM d, yyyy"), "") %>
|
by: **Developer** |
last post by:
I need to sort the columns of a ListView.
Some columns contain dates and others contain integers.
What I did once before is in the Compare method I tried date and if that
failed I did Integer.
Seems kinda not nice - is there a better way?
Thanks
|
by: PC Datasheet |
last post by:
An Access user saw my name in a newsgroup and sent me a request for help on
a project. As part of the project, a list of the dates in a month was
needed. For anyone needing a list of dates in a month, here is what I used:
1. Create a table named TblNumbers with one field named Num and populate
the table with 1 to 31
2. Create a query based on TblNumbers
3. Pull down Num into the first field of the query.
4. Put the following...
| | |
by: AAJ |
last post by:
Hi
Does anyone know of any good publically available set of standards for
managing dates when dealing with a database server (in my case SQL Server
2000 and c# VS2005).
At the moment, if I create a record in the database for say a job, the
invoicedOn date field will be left null within the data base, not being
assigned untill its actually invoiced.
|
by: M Skabialka |
last post by:
I am creating my first Visual Studio project, an inventory database. I have
created a form and used written directions to add data from a table to the
form using table adapters, data sets, etc.
I imported a table from an Access database which has some date fields. Many
of these dates have never been filled in, so are nulls. If I look directly
into the table data from Visual Studio it shows NULL.
However, when I scroll from record to...
|
by: Phil Stanton |
last post by:
I am being thick.
I have a table of employees - EmpID & EmpName
I have a table or wage rates for each employee - RateID, EmpID, WageRate
RateFron (Date)
I have a table of payments - PayID, EmpID PayAmount, DatePaid
how on earth do I do a query to give me EmpName, DatePaid, HoursWorked.
The Hours worked is calculated from the PayAmont and the appropriate
WageRate at that date. Somehow have to find the rate at any date
|
by: paulquinlan100 |
last post by:
Hi
I'm trying to execute the following code:
sSQL = "insert into lkpChanges
(SiteRef,ProjectName,InvestmentType,FieldChanged,PreviousDate,NewDate,ChangeDate,CurrentRelaunch)
" & _
"values (" & sRef & ", """ & pName & """,""" & iType &
""",""" & fChange & """," & IIf(IsNull(pDate), Null, pDate) & "," &
IIf(IsNull(txtFSOS), Null, txtFSOS) & "," & Date & "," &
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
