June 19, 2009 at 10:02 am
how do i get the following query to return the project number when it contains a decimal point if I enter 1111 as the project number it returns all of the records if I enter 1111.01 as the project number no records are returned even though record data exists and can be found using the filter form.
SELECT DISTINCTROW tblTimesAll.Initials, Sum(tblTimesAll.HoursWorked) AS SumOfHoursWorked, tblTimesAll.TaskDate, tblTimesAll.ProjectNo, tblTimesAll.TaskLetter, First(tblTimesAll.TaskDescription) AS FirstOfTaskDescription, tblTimesAll.Notes
FROM tblTimesAll
WHERE (((tblTimesAll.ProjectNo)=forms!frmrptProj!ProjectNo) And ((tblTimesAll.TaskDate)>=forms!frmrptProj!BeginDate And (tblTimesAll.TaskDate)<=forms!frmrptProj!EndDate))
GROUP BY tblTimesAll.Initials, tblTimesAll.TaskDate, tblTimesAll.ProjectNo, tblTimesAll.TaskLetter, tblTimesAll.Notes;
Steve
June 19, 2009 at 12:06 pm
I'm assuming you're using access due to the large number of exclamation points and unnecessary parenthesis. Please correct me if I'm incorrect.
More than likely you are having some data type conversion issues. What is the datatype of tblTimesAll.ProjectNo and what is the datatype being passed into your query? Access may be doing a conversion which will remove everything after the decimal in one place or the other during the comparison.
-Luke.
June 22, 2009 at 4:36 am
Your correct Luke its an access front end to the sql db, had changed data type in the sql but not in the access front end did that and its now working correctly
cheers
Steve
June 22, 2009 at 7:04 pm
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply