nirmal kumar
asked on
Iam getting this error "Operation must use an updateable query"
I have to take current date and a field from my table and need to calculate number of days in between two dates using SQL in MS ACCESS
My Query is
UPDATE AllTickets SET Aging = (select DATEDIFF("d",NOW(),CreateT
WHERE [ReferenceNo] = 176698;
CreateTime is an Field in my table and it will have value like "8/12/2014 11:37:57 AM"
AllTicket is my Table name
Can anyone suggest me good solution?
My Query is
UPDATE AllTickets SET Aging = (select DATEDIFF("d",NOW(),CreateT
WHERE [ReferenceNo] = 176698;
CreateTime is an Field in my table and it will have value like "8/12/2014 11:37:57 AM"
AllTicket is my Table name
Can anyone suggest me good solution?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
can you tell me data type of Aging and ReferenceNo ?
try this --
You should have #Date#..then it will work..
UPDATE AllTickets
SET [Aging] = DATEDIFF ( "d" , [CreateTime] , NOW() )
WHERE [ReferenceNo] = 176698;You should have #Date#..then it will work..
If [CreateTime] is not a date field, use: DateDiff("d", CDate([CreateTime]), Now())
And/or if you have Nulls: DateDiff("d", Nz([CreateTime], Now()), Now())
/gustav
And/or if you have Nulls: DateDiff("d", Nz([CreateTime], Now()), Now())
/gustav
ASKER
Aging - Number
CreateTime - Date/Time
CreateTime - Date/Time
Then you probably have Null values.
/gustav
/gustav
chk this
UPDATE AllTickets
SET [Aging] = DATEDIFF ( "d" , NZ([CreateTime],NOW()) , NOW() )
WHERE [ReferenceNo] = 176698;ASKER
Yes ,I need to update those empty field with the calculated number of days
is above worked?
ASKER
Hi Pawan,
UPDATE AllTickets
SET [Aging] = DATEDIFF ( "d" , NZ([CreateTime],NOW()) , NOW() )
WHERE [ReferenceNo] = 176698;
The above query was also giving the same error
UPDATE AllTickets
SET [Aging] = DATEDIFF ( "d" , NZ([CreateTime],NOW()) , NOW() )
WHERE [ReferenceNo] = 176698;
The above query was also giving the same error
It is CreateTime that is the question - as I wrote:
/gustav
And/or if you have Nulls: DateDiff("d", Nz([CreateTime], Now()), Now())
/gustav
Then it could be No. that is a string. Try:
WHERE [ReferenceNo] = '176698';
/gustav
WHERE [ReferenceNo] = '176698';
/gustav
check these..and tell me which one is working.
select DATEDIFF ( "d" , NZ([CreateTime],NOW()) , NOW() )
WHERE [ReferenceNo] = 176698;
or
DATEDIFF ( "d" , NZ([CreateTime],NOW()) , NOW() )
WHERE [ReferenceNo] = '176698';
select DATEDIFF ( "d" , NZ([CreateTime],NOW()) , NOW() )
WHERE [ReferenceNo] = 176698;
or
DATEDIFF ( "d" , NZ([CreateTime],NOW()) , NOW() )
WHERE [ReferenceNo] = '176698';
ASKER
Hi pawan,gustav
my ReferenceNo is "short text"
my ReferenceNo is "short text"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes the above query is all you need...
Note that you should always put the string in single quotes for comparison.
Note that you should always put the string in single quotes for comparison.
UPDATE AllTickets
SET [Aging] = DateDiff("d", [CreateTime], Now())
WHERE [ReferenceNo] = '176698';
Just FYI - Now() = Current Date + time of day. For this purpose, you should be using Date()
UNLESS CreateTime also includes time of day and you want that to be considered in your calculation.
UNLESS CreateTime also includes time of day and you want that to be considered in your calculation.
@Author - Do you need more help with this. :)
Solution provided.
ASKER
Thanks for your kind reply,
I tried the both the queries its saying "data type mismatch in criteria expression"