Need help with the update query..

[awinnn]

Hi,
I have table [Leave] and [LeaveDetail]. In table [Leave], i have field StaffNo and TotDays. In table [LeaveDetail], i have field StaffNo, StartDate and EndDate. Both table is linked by StaffNo. I want to update TotDays (equal to EndDate-StartDate+1).

This is what i've done,

UPDATE [Leave] INNER JOIN [LeaveDetail] ON [Leave].[StaffNo]=[LeaveDetail].[StaffNo] SET [Leave].[TotDays] = ([LeaveDetail].[EndDate]-[LeaveDetail].[StartDate]+1)
WHERE ((([Leave].[StaffNo])=[LeaveDetail].[StaffNo]));

This is the sample of data,
StartDate EndDate
02/01/2004 08/01/2004

so..the TotDays should be 8.

my problem is, it displays as 1.
it means, it doesn't calculate the total days..
any idea? thanx..

 

[Steve101]

I'm guessing that your two date fields are defined in the table as Text type as opposed to DateTime type. What is happening is that the two text expressions are being evaluated as:

(two divided by one divided by 2004) minus (eight divided by one divided by 2004) plus one, which ... guess what ... returns a value pretty close to one.

To fix this, eithor:

(a) Change the data type of the two date intended fields to DateTime in the appropriate field definitions - the preferred approach, or

(b) change your SQL to:

UPDATE [Leave] INNER JOIN [LeaveDetail] 
               ON [Leave].[StaffNo]=[LeaveDetail].[StaffNo] 
SET [Leave].[TotDays] = CVDate([LeaveDetail].[EndDate])-
                        CVDate([LeaveDetail].[StartDate])+1
WHERE [Leave].[StaffNo]=[LeaveDetail].[StaffNo];

ie. convert the text based date fields to DateTime fields so that they will evaluate the date differences correctly.

You might also want to investigate the DateDiff() function, which would provide another alternative to finding the difference in days, and you wouldnt have to use the CVDate function; yet another way to do the same thing.


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[BNPMike]

It looks alright.

Best to do this in two stages. Stage 1 is a select where you create a new field TotCalcDays calculated as in you SET. Display the results to make sure it works before Stage 2 - turning the query into an Update.

You can then post sample data for us to see.

 

[awinnn]

Hi all,
sorry for late reply, i was on mc for 2 days..

FYI, StartDate and EndDate is Date/Time type field.

Let say, i have this sample of data,
StarTDate EndDate DaysTaken
02/01/2004 08/01/2004 7
05/01/2004 05/01/2004 1
26/01/2004 27/01/2004 2

so, the TotDays should be 10 days..but in this case..it will be displayed as 7 (first record).
i've included sum() into the Update To, but it does not working..
any idea? thanx in advance..