Cannot filter on Date Fields in Access - MS Access Crashes

[MaliciousMike]

Good afternoon all,

I'm struggling to find a solution to an issue I'm having when trying to right click and "filter for" on a FEW date columns in a linked table. This also happens in queries when i add a filter for the same fields.

However when I filter on the same columns in a form, there seems to be no issue.

I'm using Access 2003 and SQL Server 2005 (the free one...), and this happens on all machines which use my app.

This isn't stopping me from completing my tasks on this project, however they are causing me to do major work arounds to filter the data.

Are there known issues with a setting or 2 in access or SQLServer? As this doesn't seem to happen with any of our old systems using similar set-ups.

Any help would be phenominal!

 

[The_Doc_Man]

Need more specifics. Are the fields to be filtered all the same type (i.e. Date)? What kind of filter is being applied? Since this is an FE/BE situation with a non-Access BE, we might also need to know which kind of recordset you have created as your link to the BE.

 

[MaliciousMike]

Hi Doc,

Thanks for the reply.

It's an ODBC connection from access to the SQL server.
All the fields I'm having issues with are datetime data types.

I attach an image of what I'm trying to do.
After hitting enter, I get a Microsoft Error Report message.

This also happens when trying to filter from a form.

 

[datAdrenaline]

Hello Mike ...

It appears that your date format is dd/mm/yyyy .. which is fine .. is that a SYSTEM setting .. or has your field or datasheet view been told to format the date that way (ie: a Format property has been set to displaythe data in that format). If you are setting the filter in the manner you are, you must enter the date in the date format that is defined by your SYSTEM, not the format you are seeing your date in (although they may be the same) ...

Also, another "gotcha" is if your date/time field has a time other than 12:00 AM (dates are stored as a number, so ALL dates have a time associated with them, but if they are input without a time, the time associated with the date is 12:00 AM), but the Format property of the control truncates it out, so when you try to filter without a time associated with the date, your data is not filtered, since your data and your desired filter value do not match based on the time.

Does that make sense?

.....

Just to make sure, you are using an MDB/ACCDB correct? ... or is the screen shot from an ADP ..

 

[MaliciousMike]

Thanks for the reply.

I've attached 2 more images.
The first one is the "design view" of the table and the settings in Access (And yes, it's via a MDB file, not ADP.
Viewing the table on the SQL machine, there is a time of "00:00" on each date, however referencing my first post, this is the case for my earlier projects which are working fine so I'm feeling that this cannot be the cause.

The second attachment is the settings for the column on the SQL machine, which hopefull might help.

Unfortunately my knowledge is very general when it comes to these linking tables and all these additional settings. I'm just good with VB and SQL strings!!

Thanks for all the help guys.

 

[namliam]

LC month and LC Date seems like duplicated data to me (i.e. Not exectaly perfectly normalized)

Have you tried running a query instead?

Also I can imagine... but have not tried it... maybe you need to enclose the date by ## ??

 

[MaliciousMike]

Hi Mailman, or Namliam.

Thanks for the reply.
LCMonth and LCDate are seperate data, however for filtering and reporting purposes, I needed one column to have exact days in a month and one column to have the month that the date is in. If that makes sense...

I've tried filtering in the table itself, from a query and from a form. All give the same error. This is why I think it's to do with the link between my MDB and the tables on the SQL machine.

Could it be to do with american date format? Could this cause conflict?
I've just opened the table on the SQL machine and run an SQL query for "10/01/2008" and this seems to have filtered fine, however when I type the same in the MDB I get the same error again.

Thanks for all the assistance with this one. Unfortunately my knowledge is too limited!

 

[namliam]

The exact date can be abstracted to a month quite easily in both SQL server and Access... So why have two dates when you already have 1 that can do both.
Format (YourDate, "MMM YYYY") will roll up to a month

there are a few more you could use....

I know there may be issues SQL server vs Access, where the SQL data type for Date/time is more precise than the Access one (sql server goes to 1000th of a second, access doesnt go beyond seconds).
Perhaps that is causing a problem??
I have never tried linking a SQL Server table in access.... so I cannot really help you in that sence... Other than beeing a sounding board

 

[MaliciousMike]

Hi Mailman,

Thanks for all your help.

Having 2 date fields may seem quite useless if they are in part referencing the same data. However they are 2 seperate columns in Access and in the SQL. The columns are not directly linked to each other in any way at all except for the data they actually contain (if you see what I mean...). So in theory, there shouldn't be any issue, regardless of their irellevence.
Also, this doesn't just occur for these 2 columns, it also occurs for some other logging columns towards the end of the table.

I have 4 date columns, 3 have different formats:
LCMonth - mmm yy
LCDate - dd/mm/yyyy
LastUpdated - dd/mm/yyyy hh:mm:ss
LogDate - dd/mm/yyyy hh:mm:ss

Every other column in the table has no issue with filtering.

 

[namliam]

(if you see what I mean...). So in theory, there shouldn't be any issue, regardless of their irellevence.

I do see what you mean, but the problem is still there.... You are storing the same data twice.... Not a real big issue, but I suggest you try storing ALL your data twice, having to change it twice, requiring double the storage space, etc....

From a database/storage/efficiency/normalization point of view duplication of data is a big No-No!

The format is unrespective of the problem I think... But like I said my knowledge of ODBC to SQL Server is limited at best.

Also the formatting doesnt change the 'nature' of a date/time field... its a date/time field that always contains a value that is reflecting of a date AND time... That is DAY MONTH YEAR HOUR MINUTE SECOND and in SQL Server, Milliseconds. This is true for all your date fields!

 

[MaliciousMike]

An update on this:
I've recently discovered that doing a filter for dd/mm/yyyy* works.
If I omit the wildcard, it fails... epically!

This would point it into the direction of some sort of error with storing time data.
However this doesn't occur with my older projects which still makes it weird.

Does anyone know of a fix for this linking between SQL and Access?

Thanks for any help.

 

[MaliciousMike]

Sorry for bump, but this is causing serious issues for me.

 

[namliam]

I am sorry, but I truelly have no clue any more... Did you try sending time along?

 

[MaliciousMike]

Well, adding the wildcard at the end basically means "Any Time" right?

Time is being stored on the SQL server because it's a Datetime field.
e.g. 01/10/2008 00:00:00

All the times are 00:00:00 and it's the same for older databases of mine.
There's no formatting of the time in my access tables, it's simply the date showing.

this is intensely annoing me

 

[namliam]

I can sympathise with your frustration.... but like I said I am fresh out of ideas on this problem.

Try sending DD/MM/YYYY 00:00:00 but other than that... it seems 'kinda' strange.

 

[MaliciousMike]

So I managed to fix it!

I'm not sure where the issue primarily lies, however I deleted the "MS_Format" extended column property from SQL server which seemed to fix it for some instaces of the problem, however what trully fixed it was Exporting all my tables, forms, queries, reports etc to another mdb file.

Splendid...

I hope this helps someone.

 

[namliam]

LOL

Yes strangely enough that seems to fix the oddest of problems... Should add that to my "to do" list before going crazy....

On the same list as reboot the comp... see if that fixes it...

 

[The_Doc_Man]

Adding the * at the end of the filter prevents a field-size mismatch, which might be occurring on the front end vs. back end comparison of data types.

Namliam, I've not used a back-end server for Access. (Our shop uses a different front end for the case where we have a back-end DB.) Is there a chance that the raw record being exchanged between FE and BE is undergoing an implied (and invisible) mode change for the purpose of date interconversion?

I.e. Even though the BE field type is date/time, that isn't always guaranteed to be the same internal format as Access's timeline method. So could there be a hidden format change that is causing this confusion?

Mike, can you do proper filtration on other fields that are NOT date/time format? I.e. does any other data type cause a problem or is it only date/time format?

Richard