Parameter query and GetDate() problems 1

[ebic]

I have a default date and a default time set in a table using GETDATE(). I have the fields formatted to display the date in the date field and the time in the time field.

I have made parameter queries and reports based on them.

The problem is that the queries do not read the date field correctly and therefore the queries return no results and the reports are thereby inaccurate.

 

[PWise]

what should the queries read and what are thay reading

 

[ebic]

Thanks for your response.

The table has 1 field each for Date and Time which are filled out by default using getdate() and then formated for "Short Date" and "Long Time". The date field shows dd-mmm-yyyy in the table but shows dd-mmm-yyyy HH:mm:ss in the stored procedure. The time field shows HH:mm:ss in the table but shows dd-mmm-yyyy HH:mm:ss in the stored procedure. I'm trying to use a parameter query (=@Enter_Date) to search by date (and also by Date Range) and I'm getting intermittant results. I know that the information is in the table but the sp sometimes returns nothing. A workaround that I've been using is search using date range and to input dd-mmm-yyyy 00:00:00 for start date and dd-mmm-yyyy 23:59:59 for end date. This does find the record but it is not the elegant solution I'm looking for. I hope this is enough information for you.

Thanks in advance for your help

 

[PWise]

try
dateadd(d,datediff(d,0,DatefieldName),0)=@Enter_Date

 

[ebic]

It seems to do the trick.

When I added the code to the parameter query it altered it to a new field without an output so I played around to get it in the right place. Now that it's working properly I'll fix up the rest of them and call my job done.

Thank you very much for your assistance

 

[EMEP]

Good day all,

I have recently installed sql 2005. when running access 2003 I get the following error messages. Anybody got a clue how to solve these??

Thank for your troubles.

"This version of MS Access doesn't support design changes with the version os SQL you are connected to."

"New database features not supported"

Both statements are implying some missing files in access 2003 to communicate correctly with sql 2005. Has anybody encountered this problem before and how did you solve it.

hope this info is sufficient to make you understand the problem.

Thanks for your help. greatly appreciated.

Eric