iam making a simple computation but iam having a problem with this

If btnAdd.Text = "&Add" Then
            cbleave.Enabled = True
            dtFrom.Enabled = True
            dtTo.Enabled = True
            dtApplied.Enabled = True
            clear()
            btnAdd.Text = "&Save"

            btnSearch.Enabled = True



            sql = "SELECT IFNULL(MAX(cnt),0) FROM tblleaveapp"
            NewDataSet(sql)
        Else
            If cbleave.Text = String.Empty Then
                MsgBox("Choose Leave Type.", MsgBoxStyle.Information, Me.Text)
                Exit Sub
            End If

            sql = "SELECT leaveLeft FROM tblleaveapp"
            NewDataSet(sql)
            If ds.Tables("a").Rows.Count > 0 Then



            Else
                Dim available As Integer
                Dim days As Integer
                Dim use As Integer


                days = Integer.Parse(txtnumber.Text)
                use = Integer.Parse(txtUse.Text)


                available = days - use

                txtavailable.Text = available.ToString


            End If


            Dim available As Integer
            Dim days As Integer
            Dim use As Integer


            days = Integer.Parse(txtnumber.Text)
            use = Integer.Parse(txtUse.Text)


            available = days - use

            txtavailable.Text = available.ToString


            sql = "INSERT INTO tblleaveapp (employeeID, leaveType, leaveNumber, `from`, `to`, leaveUse, applied, leaveLeft) VALUES ('" & replaceQuote(txtID.Text) & "','" & replaceQuote(cbleave.Text) & "','" & replaceQuote(txtnumber.Text) & "','" & replaceQuote(dtFrom.Text) & "','" & replaceQuote(dtTo.Text) & "','" & replaceQuote(txtUse.Text) & "','" & replaceQuote(dtApplied.Text) & "','" & replaceQuote(txtavailable.Text) & "' )"
            SaveRecord(sql)

            clear()
            cbleave.Enabled = False
            dtFrom.Enabled = False
            dtTo.Enabled = False
            dtApplied.Enabled = False
            btnAdd.Text = "&Add"
            btnSearch.Enabled = False
            MsgBox("Are you sure you want to save this data? This data cannot be edited.", MsgBoxStyle.Information + MsgBoxStyle.YesNo, Me.Text)
        End If

the logic is i need to subtract the available days to to desire days and then the total of the two will become my available days to another transaction
example:
10 - 2 = 8
8 - 6 = 2
2 - 2 = 0
something like that please help me

thanks in advance

you want to perform this action in your query or in your application?

in query sir did you know how??? please help me

you can use

DATEDIFF(dd, FirstDate,2ndDate  )

this will return difference of days .firstdate and 2nd date are two dates .hope this will solve your prob.:)
If yes then please mark your thread solved and vote me up :)

Regards

this is my query i used it in my project , this will give you an idea .

select ProductName ,Packing , BatchNbr ,ExpiryDate ,ExpireInDays  
from (
select ph.productname,ph.Packing ,b.BatchNbr ,b.ExpiryDate ,d.distributorid,
case when b.ExpiryDate > GETDATE() then DATEDIFF(dd, GETDATE(),b.ExpiryDate  )
else DATEDIFF(dd, GETDATE(),b.ExpiryDate  ) end as ExpireInDays
from HOProducts ph
inner join DistProducts  dp on ph.HOProductId = dp.HOProductId 
inner join InvoiceTransactionBody itb on itb.ProductId  = dp.ProductId 
inner join Distributors d on dp.DistributorId = d.DistributorId 
inner join Batches b on itb.BatchNo = b.BatchNbr  
) as n Where 1=1

Regards

example iam the employee and i want to have a vacation leave and i want to have a vacation in 4 days and the vacation leave have 10 days maximum so if i get 4 days my vacation leave will be 6 days automatically

then on my another vacation i want to used again my vacation leave and i want to acquired 3 days so my vacation leave will be 3 days left automatically

here`s my problem sir...i cant get the total value of my first transaction to my available days
example 10 is the available and i used 3 so my left days is 7
on my another transaction the available days was 10 again which is wrong
10 - 3 = 7 first transaction
7 - 2 = 5 second transaction

please help me T_T

please provide the fields of your tables , which are used in this query , so i can able to provide you solution.

Regards

employeeID, leaveType(example vacation leave/sick leave), leaveNumber(maximum number of leave depends on leaveType), from, to, leaveUse(days used), leaveLeft(days left or days available after the transaction), applied(date of transaction)

where is the limit of days ? i mean in which field you are defining the limit as you mentioned above 10 is the limit?

where is the limit of days ? i mean in which field you are defining the limit as you mentioned above 10 is the limit?

at leaveNumber

well i suggest you to use head and detail tables for this to maintain your records, for example headtable has fields , RecHeadID,LeaveType,MaxDays and detail table fields RecDetailID,EmpID,RecHeadID,from,to with these two tables you can manage all your leave details of your employees , but here is a query for your current table ,

select employeeid,leavetype,leavenumber,leaveuse , (leavenumber-leaveuse) as leaveleft
from
(
select employeeid,leaveType,LeaveNumber,
(
select sum(leaveuse)
from table1
)  as LeaveUse
from table1
) m

i not tested it. if you caught any error , please do inform me,

Regards

example iam the employee and i want to have a vacation leave and i want to have a vacation in 4 days and the vacation leave have 10 days maximum so if i get 4 days my vacation leave will be 6 days automatically

then on my another vacation i want to used again my vacation leave and i want to acquired 3 days so my vacation leave will be 3 days left automatically

here`s my problem sir...i cant get the total value of my first transaction to my available days
example 10 is the available and i used 3 so my left days is 7
on my another transaction the available days was 10 again which is wrong
10 - 3 = 7 first transaction
7 - 2 = 5 second transaction

please help me T_T

Try this

select  DateDiff("d",(Format([FromDate], 'MM/dd/yyyy')),(Format([ToDate], 'MM/dd/yyyy'))) from Tablename

why some one down vote my post :-X with out any reason :-| , this is not fair :(

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.