Using Jet-SQL (I know, flame away, as long as someone tries to help), have a table of people. One field contains their birthdate, and given user input of a pair of dates (one "low", one "high")I need to use this field to figure out who will be turning exactly X yrs old at a time between the user's given high and low dates. Any help appreciated. And while I do intend to eventually switch database solutions, until I have the spare hardware and time to implement, that is not something I can do immediately.
Name:
Anonymous2006-05-31 8:59
I am OP. I have tried this:
SELECT tblEMP.LAST_NAME, tblEMP.FIRST_NAME, tblEMP.E_BIRTHDAY
FROM tblEMP INNER JOIN tblinslocmast ON tblEMP.LOC_NUM = tblinslocmast.LOC_NUM
WHERE ((([enter end date])>Trim(DateAdd("yyyy",65,[tblEMP].[E_BIRTHDAY]))) AND (([enter start date])<Trim(DateAdd("yyyy",65,[tblEMP].[E_BIRTHDAY]))));
but it returns inaccurate results.
Name:
Anonymous2006-05-31 9:14
What's the INNER JOIN for? Could that be anything to do with it, as it will restrict the rows returned from tblEMP to those with a LOC_NUM in tblinslocmast.
Name:
Anonymous2006-05-31 9:16
Alternatively, check that your input dates are being recognised in the right format. e.g. if you are entering them as dd/mm/yyyy make sure it's not interpreting them as mm/dd/yyyy
Name:
Anonymous2006-05-31 9:21
In your WHERE clause you're comparing strings, not dates, because putting the date in a Trim() converts it to a string.
Name:
Anonymous2006-05-31 9:48
>>3
All rows have a value for LOC_NUM. >>4
I made sure they are all in mm/dd/yyyy format. >>5
Removing Trim() causes no results to be returned.
SELECT tblEMP.LAST_NAME, tblEMP.FIRST_NAME, tblEMP.E_BIRTHDAY, FROM tblEMP INNER JOIN tblinslocmast ON tblEMP.LOC_NUM=tblinslocmast.LOC_NUM
WHERE ((([enter end date])>DateAdd("yyyy",65,tblEMP.E_BIRTHDAY)) And (([enter start date])<DateAdd("yyyy",65,tblEMP.E_BIRTHDAY)));
returns no results. User enters date in formate mm/dd/yyyy, data is stored mm/dd/yyyy
Name:
Anonymous2006-05-31 11:43
Try converting [enter end date] and [enter start date] to dates using the CDate() function
Using Jet-SQL (I know, flame away, as long as someone tries to help), have a table of people. One field contains their birthdate, and given user input of a pair of dates (one "low", one "high")I need to use this field to figure out who will be turning exactly X yrs old at a time between the user's given high and low dates. Any help appreciated. And while I do intend to eventually switch database solutions, until I have the spare hardware and time to implement, that is not something I can do immediately.
2 Name: Anonymous : 2006-05-31 08:59
I am OP. I have tried this:
SELECT tblEMP.LAST_NAME, tblEMP.FIRST_NAME, tblEMP.E_BIRTHDAY
FROM tblEMP INNER JOIN tblinslocmast ON tblEMP.LOC_NUM = tblinslocmast.LOC_NUM
WHERE ((([enter end date])>Trim(DateAdd("yyyy",65,[tblEMP].[E_BIRTHDAY]))) AND (([enter start date])<Trim(DateAdd("yyyy",65,[tblEMP].[E_BIRTHDAY]))));
but it returns inaccurate results.
3 Name: Anonymous : 2006-05-31 09:14
What's the INNER JOIN for? Could that be anything to do with it, as it will restrict the rows returned from tblEMP to those with a LOC_NUM in tblinslocmast.
4 Name: Anonymous : 2006-05-31 09:16
Alternatively, check that your input dates are being recognised in the right format. e.g. if you are entering them as dd/mm/yyyy make sure it's not interpreting them as mm/dd/yyyy
5 Name: Anonymous : 2006-05-31 09:21
In your WHERE clause you're comparing strings, not dates, because putting the date in a Trim() converts it to a string.
6 Name: Anonymous : 2006-05-31 09:48
>>3
All rows have a value for LOC_NUM. >>4
I made sure they are all in mm/dd/yyyy format. >>5
Removing Trim() causes no results to be returned.
SELECT tblEMP.LAST_NAME, tblEMP.FIRST_NAME, tblEMP.E_BIRTHDAY, FROM tblEMP INNER JOIN tblinslocmast ON tblEMP.LOC_NUM=tblinslocmast.LOC_NUM
WHERE ((([enter end date])>DateAdd("yyyy",65,tblEMP.E_BIRTHDAY)) And (([enter start date])<DateAdd("yyyy",65,tblEMP.E_BIRTHDAY)));
returns no results. User enters date in formate mm/dd/yyyy, data is stored mm/dd/yyyy
7 Name: Anonymous : 2006-05-31 11:43
Try converting [enter end date] and [enter start date] to dates using the CDate() function