CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> Archive -> CodeCharge.Discussion

 Searching on Date

Print topic Send  topic

Author Message
Paul
Posted: 02/19/2001, 9:20 AM

I'm trying to search a table by date. No matter how I enter the date I get
the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
expression.

/dssts/Common.asp, line 35

I am using Access 97 as my database on a Windows 98 machine.

Oh and Adam I checked and I'm not using a reserved word as a variable name
this time ;-)

Any help would be grately apprciated.


Paul
Posted: 02/19/2001, 11:25 AM

I have just searched the code in both common.asp and the asp file for the
page I'm working on. I find no reference to the CDate() function which
would be used to convert text to date. I wonder if this something I need to
put into an event myself or is this a bug in CodeCharge? I don't see any
way that a date could be handled without this function being called; but
then there may be a different way of handling dates that I'm not aware of.

Paul

Paul
Posted: 02/20/2001, 6:33 AM

By changing the field type in the database from "Date" to "Text" I got this
working. However I really think this is a work-around rather than a
solution.

I notice that in the code the CSTR() function is being used to compare the
database field content and the form field content. In theary this should
convert the date field of the the database and the value of the form field
to a string value and they should then be the same if the dates are the
same. However this for some reason is not the case (at least in my
experience). When I convert the database field type to text and do the
compares everything works fine and my dates, that are equal, are found to be
so. Very strange.

I'm having a ripping good time sitting here talking to myself. Anyone that
gets the erge can jump in at anytime, all help is grately appreciated :-)

Paul
Paul wrote in message <96rkkn$csl$1@news.codecharge.com>...
>I'm trying to search a table by date. No matter how I enter the date I get
>the following error:
>
>Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
>
>[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
>expression.
>
>/dssts/Common.asp, line 35
>
>I am using Access 97 as my database on a Windows 98 machine.
>
>Oh and Adam I checked and I'm not using a reserved word as a variable name
>this time ;-)
>
>Any help would be grately apprciated.
>
>
>

Alexey Alexapolsky
Posted: 02/22/2001, 3:45 AM

Paul ,

Briefly , it's a feature of MS ACCESS.
It handles dates in different way.

In Jet sql you should use data comparisions with pound sign , e.g. OrdDate =
#1/1/1980#
Find in your code line (where date handling is made) like
sWhere = sWhere & "b.[date_assigned]='" & replace(pdate_assigned, "'",
"''")
and replace ' with # sign (not all , only the ones that surround date)

--
Regards,
Alexey
CodeCharge Support

Paul <paporter@visto.com> wrote in message
news:96rkkn$csl$1@news.codecharge.com...
> I'm trying to search a table by date. No matter how I enter the date I
get
> the following error:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
>
> [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
> expression.
>
> /dssts/Common.asp, line 35
>
> I am using Access 97 as my database on a Windows 98 machine.
>
> Oh and Adam I checked and I'm not using a reserved word as a variable name
> this time ;-)
>
> Any help would be grately apprciated.
>
>
>

Paul
Posted: 02/22/2001, 8:23 AM

That would be fine if we were talking about a constant, Alexey. But since
we're talking about a form field variable and a database field content it's
really not practical. The simple solution would be for CodeCharge to use
the CDATE() function on the form field variable in the compare so that the
compare would look something like:

if database.datefield = cdate(form.datefield) then whatever

as apposed to if cstr(database.datefield) = cstr(form.datefield) then
whatever

As it currently generates.

The use of the CDATE() function mimizes the possibility of problems when
comparing dates; where CSTR() is a dumb function that just does exactly what
it's told (compares string A to string B) and nothing more.

Just my humble opinion;

Paul
Alexey Alexapolsky wrote in message <972u40$6pt$1@news.codecharge.com>...
>Paul ,
>
>Briefly , it's a feature of MS ACCESS.
>It handles dates in different way.
>
>In Jet sql you should use data comparisions with pound sign , e.g. OrdDate
=
>#1/1/1980#
>Find in your code line (where date handling is made) like
> sWhere = sWhere & "b.[date_assigned]='" & replace(pdate_assigned, "'",
>"''")
>and replace ' with # sign (not all , only the ones that surround date)
>


Paul
Posted: 02/22/2001, 8:29 AM

Sorry I miss spoke. What I intended to say was:

The use of the CDATE() function mimizes the possibility of problems when
comparing dates; where CSTR() is a dumb function that just does exactly what
it's told (convert field A to string A) and nothing more.

Again just my opinion;

Paul


   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.