Date Function in MS Access

[hianjali]

Hello All,

I am a beginner in MS ACCESS and i have a very tight deadline assignment to work on. Someone please help.

I have to pull out the members who have their Effective Date more than six months from the given date.

This is the query i wrote, but i get an error

"This expression is typed incorrectly,or its too complex to be evaluated. for example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expressions to variables"

Someone please help me soon

 

[PHV]

This is the query i wrote
Which query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hianjali]

Sorry about that...this is the query

SELECT DISTINCT elgdep.MEM_SOC_SEC_NUM, elgdep.MEM_DEP_CODE, elgemp.ENR_GRP_NUMBER, elgdep.MEM_EFFEC_DATE, elgdep.MEM_BIRTH_DATE, elgadr.erstat, elgadr.erzpcd
FROM elgadr, elgdep, elgemp
WHERE DateDiff('m', elgdep.MEM_EFFEC_DATE, Format([Today's Date],"yyyymmdd") > 6 )
GROUP BY elgdep.MEM_SOC_SEC_NUM, elgdep.MEM_DEP_CODE, elgemp.ENR_GRP_NUMBER, elgdep.MEM_EFFEC_DATE, elgdep.MEM_BIRTH_DATE, elgadr.erstat, elgadr.erzpcd
ORDER BY elgemp.ENR_GRP_NUMBER;

 

[Golom]

Try

WHERE Abs(DateDiff('m', elgdep.MEM_EFFEC_DATE, Date())) > 6

 

[PHV]

Get rid of the GROUP BY clause as you don't use any aggregate function.
I wonder you have no JOIN clause: are you sure you want a cartesian product of the 3 tables ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hianjali]

I did. Now it doesnt ask me to input today's date as well as i get an error "Data type mismatch in criteria expression"

SELECT DISTINCT elgdep.MEM_SOC_SEC_NUM, elgdep.MEM_DEP_CODE, elgemp.ENR_GRP_NUMBER, elgdep.MEM_EFFEC_DATE, elgdep.MEM_BIRTH_DATE, elgadr.erstat, elgadr.erzpcd
FROM elgadr, elgdep, elgemp
WHERE Abs(DateDiff('m', elgdep.MEM_EFFEC_DATE, Date())) > 6
GROUP BY elgdep.MEM_SOC_SEC_NUM, elgdep.MEM_DEP_CODE, elgemp.ENR_GRP_NUMBER, elgdep.MEM_EFFEC_DATE, elgdep.MEM_BIRTH_DATE, elgadr.erstat, elgadr.erzpcd
ORDER BY elgemp.ENR_GRP_NUMBER;

 

[hianjali]

I now tried removing Group by and also used join...but still i get the same error

SELECT elgdep.MEM_SOC_SEC_NUM, elgdep.MEM_DEP_CODE, elgemp.ENR_GRP_NUMBER, elgdep.MEM_EFFEC_DATE, elgdep.MEM_BIRTH_DATE, elgadr.erstat, elgadr.erzpcd
FROM elgadr, elgdep INNER JOIN elgemp ON elgdep.MEM_SOC_SEC_NUM = elgemp.ENR_SOC_SEC_NUM
WHERE (((DateDiff('m',[elgdep].[MEM_EFFEC_DATE],Format([Today's Date],"yyyymmdd")>6))<>False))
ORDER BY elgemp.ENR_GRP_NUMBER;

"This expression is typed incorrectly,or its too complex to be evaluated. for example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expressions to variables"

 

[hianjali]

Some one please help :-(

 

[PHV]

PARAMETERS [Today's Date] DateTime;
SELECT elgdep.MEM_SOC_SEC_NUM, elgdep.MEM_DEP_CODE, elgemp.ENR_GRP_NUMBER, elgdep.MEM_EFFEC_DATE, elgdep.MEM_BIRTH_DATE, elgadr.erstat, elgadr.erzpcd
FROM (elgdep
INNER JOIN elgadr ON elgdep.??? = elgadr.???)
INNER JOIN elgemp ON elgdep.MEM_SOC_SEC_NUM = elgemp.ENR_SOC_SEC_NUM
WHERE elgdep.MEM_EFFEC_DATE < DateAdd('m',-6,[Today's Date])
ORDER BY elgemp.ENR_GRP_NUMBER;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hianjali]

Thanks PHV...I tried and i get "ODBC --call failed"

PARAMETERS [Today's Date] DateTime;
SELECT elgdep.MEM_SOC_SEC_NUM, elgdep.MEM_DEP_CODE, elgemp.ENR_GRP_NUMBER, elgdep.MEM_EFFEC_DATE, elgdep.MEM_BIRTH_DATE, elgadr.erstat, elgadr.erzpcd
FROM (elgdep
INNER JOIN elgadr ON elgdep.MEM_DEP_CODE = elgadr.erdpcd)
INNER JOIN elgemp ON elgdep.MEM_SOC_SEC_NUM = elgemp.ENR_SOC_SEC_NUM
WHERE elgdep.MEM_EFFEC_DATE < DateAdd('m',-6,[Today's Date])
ORDER BY elgemp.ENR_GRP_NUMBER;

 

[PHV]

What is the ODBC error ?
BTW, how is ODBC concerned ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hianjali]

I have clicked on File - Get External Data - Link Tables - and selected the datasource from ODBC datasources...and using those linked tables to build the query....Hope i made some sense