October 30, 2007 at 7:28 am
quick question hope you can help. In access I hava table that contains a last date.. Some records have a null value..
I'm writing a query to say if the date is null then use todays date. How do I write this in a query
Regards
ritz
October 30, 2007 at 7:41 am
Since you're in Access - it might depend on where you are.
Access' syntax on that would be...
...nz(mydatefield,date())....
SQL Server's syntax on that would be...
...isnull(mydatefield,getdate())......
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 30, 2007 at 8:13 am
This is what I used and does not work
SELECT isnull(LastDate,getdate()) AS Expr1, tblSicknessEpisode.SickEpisodeID, tblSicknessEpisode.PersonnelNumber
FROM tblSicknessEpisode LEFT JOIN tblSMSStaff ON tblSicknessEpisode.PersonnelNumber = tblSMSStaff.[Personal Number]
GROUP BY tblSicknessEpisode.SickEpisodeID, tblSicknessEpisode.PersonnelNumber;
October 30, 2007 at 8:25 am
Are you sure that you have null value?
October 30, 2007 at 8:26 am
what error do you get? if no error - what happens?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 30, 2007 at 8:47 am
expression entered has a function containing the wrong number of arguements
expr1:isnull(LastDate,getdate())
October 30, 2007 at 8:52 am
You're using Access SQL to do this... use the NZ syntax instead
i.e.
SELECT
nz(LastDate,date()) AS Expr1,
tblSicknessEpisode.SickEpisodeID, tblSicknessEpisode.PersonnelNumber
FROM tblSicknessEpisode LEFT JOIN tblSMSStaff ON tblSicknessEpisode.PersonnelNumber = tblSMSStaff.[Personal Number]
GROUP BY tblSicknessEpisode.SickEpisodeID, tblSicknessEpisode.PersonnelNumber;
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 30, 2007 at 9:15 am
thanks very much .
Much appreciated
Regards
Ritesh
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply