database results showing datatype mismatch in criteria error message

[lshippee]

Initial problem:

Have a database built from form fields in a web form which included a date field that was initially set to a text data type. I can sort with this field but can not perform calculations or use date/time functions. I want to construct a macro or delete query in the Access2000 database that will select all records prior to the current date and delete them. I need a date/time field in order to use the Now() function, so I changed the datatype from text to date/time. Now I am getting the error message above in a database results region that uses the date field for a search parameter.

I have updated the database in FormProperties, Send to Database, Options and gotten the completed successfully message.

I've also rerun the Database Results wizard, and I've checked the properties for the field in the form -- it is "No Constraints" -- date/time is not a datatype option here.

Finally, I have created a completely new page but still get the error message.

Two evovled problems:
I can not get am Access database query to produce results using now() as criteria in the date field -- 0 records returned even though several entries have been made to the database since changing the data type in the database table, form, etc. AND . . .
I can not get my database results to work for just this one results region -- others connected to this same database but not reliant upon the date field as a search parameter work fine including showing all the data in the date field

Here are pages to view related to this problem:

http://www.cesa1.k12.wi.us/calendar/calendarmain.asp

- the gateway to various results regions

http://www.cesa1.k12.wi.us/calendar/postmeetingsform2.asp

- the data entry form upon which the database is based

Any idease about how to resolve the problem(s)? All suggestions and comments appreciated.

 

[DRowland]

ONe problem comes to mind right off the bat. If you are trying to make a query using xxx = (now field), it is really going to be hard to match what the "Now" command as put in the field. Have you tried using the "Date" command for the field, instead of "Now". It will only place a date in the field, rather than all the time info, and make it a lot easier to match.
Also, I had a similar problem, and created a text field that would match the date field, then did the queries to search the text field. It worked just fine for locating a particular date.

Hope that helps.

 

[lshippee]

For drowland --

Thanks for the tip. I was able to get the query to run in the underlying database by changing to date(), but . . .

I am still getting the criteria mismatch error message when rerunning the database results

The results worked beautifully when I had the date field cast as a text field -- the problem seems to have come from changing it to date/time.

I tried changing it back to text after using the date(), but the query returned records that were after the current date i.e. records with a date showing 10/10/01 or 11/01/01 since the text field is read numerically from left to right rather than by the serial assignment used by the date/time field.

I need to be able to reliably identify records with a date earlier than the current date so I can easily purge the database and avoid having my web page show outdated information.

Any further suggestions? Thanks!