Conditional statement in Access 97

[hianjali]

Hello All,

I am a beginner in MSAccess and have this prob with my query. I have to pull in all the members who have their DOB in Future and i wrote this query. It works but it takes forever to run the query. I dont understand why its taking so long to run the query, i am using Access 97. Before i put any condition it runs really quick, but after i put the condition it doesnt. Please help

Before i put the condition my query is

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 INNER JOIN (elgemp INNER JOIN elgdep ON elgemp.ENR_SOC_SEC_NUM = elgdep.MEM_SOC_SEC_NUM) ON elgadr.erdpcd = elgdep.MEM_DEP_CODE;

And after i add the condition the query is

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 INNER JOIN (elgemp INNER JOIN elgdep ON elgemp.ENR_SOC_SEC_NUM = elgdep.MEM_SOC_SEC_NUM) ON elgadr.erdpcd = elgdep.MEM_DEP_CODE
WHERE (((elgdep.MEM_BIRTH_DATE)>=Format([Today's Date],"yyyymmdd")));

 

[RoyVidar]

If you're querying for todays date, why not just

[tt]WHERE elgdep.MEM_BIRTH_DATE>=Date();[/tt]

Roy-Vidar

 

[hianjali]

Thanks Roy...When i use it this way as u have mentioned..

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 INNER JOIN (elgemp INNER JOIN elgdep ON elgemp.ENR_SOC_SEC_NUM = elgdep.MEM_SOC_SEC_NUM) ON elgadr.erdpcd = elgdep.MEM_DEP_CODE
WHERE (elgdep.MEM_BIRTH_DATE>=Date());

I get ODBC .. Call Failed Error.. :-(

 

[RoyVidar]

Perhaps, if you tell what kind of database you're connected to, someone can help - seems the date is stored as text? Perhaps drop the formatting and just do

[tt]WHERE elgdep.MEM_BIRTH_DATE>=[Today's Date];[/tt]

where I assume [Today's Date] is inputted through paremeters? Try just inputting that as a string in that format, i e for 11/26/2005, enter 20051126.

Roy-Vidar

 

[hianjali]

nah...date is stored as Number. and giving as 01072006 gives me an error..any query until i dont pass any condition works fine but as soon as i put some conditions it gives me errors....mostly ODBC .. Call Failed...I just cant understand what is wrong.. (

 

[PHV]

So, elgdep.MEM_BIRTH_DATE is NOT a date field ...
Have a look at the DateSerial, Right, Left and Mid functions.

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