Search Error in Grid Search Criteria

Applications Grid / Reports
#1

I have a grid displaying list of payments
The Search box and fields are activated
I need to search between 2 dates or just an exact date
When I set up the criteria I get the following error message

Error
Error while accessing the database:
[Microsoft][ODBC dBase Driver] Data type mismatch in criteria expression.
select count(*), sum(CREDIT) as sum_credit, sum(CREDIT_US) as sum_credit_us from CPTECLI where (CREDIT <> 0) OR (CREDIT_US <> 0) and ( DATE_TRANS = ‘2010-01-08’ )

I understand the error is caused because of the date_trans = ‘2010-01-08’

How can I fix this problem ?

Reginald

#2

Re: Search Error in Grid Search Criteria

Can you go into regional settings for that field and override the default to ‘YYYYMMDD’
date_field->Values Format/Regional Settings (select no).

Enter in the detail mask for the date.

Are you strictly using this driver? then change your regional settings for the entire project.

Regards,
Scott.

#3

Re: Search Error in Grid Search Criteria

I just changed the setting as mentioned
I am still getting the same error message.

the date format is still ( DATE = ‘2010-01-08’ )

I must be missing something… !

Regional Settings No

Display ddmmyyyy Field display format.

Detail Mask / Field detail mask.

#4

Re: Search Error in Grid Search Criteria

Why is it when I use DATE fields to search in the QueryBuilder
the system returns the query with quotes

EXAMPLE

Conditons DATE = 10/10/2009

SELECT
CUST_NO,
DATE,
INV_NO,
TRANSACT,
CREDIT,
CREDIT_US
FROM
CPTECLI
WHERE
(DATE = ‘10/10/2009’)

WHEN THE QUERY IS RUN
It returns the following error

odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC dBase Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect | Script: C:Program Files
etmakev5wwwrootscriptcaseprod hirdadodbdriversadodb-odbc.inc.php linha: 540

When I get rid of the quote (DATE = 10/10/2009)
it return an empty set

when I search as follow (DATE >= 10/10/2009)

the system returns the following data which obviously not what the query is requesting from the database

CUST_NO DATE INV_NO TRANSACT CREDIT CREDIT_US
5104 2002-01-03 17987 RET: CER 20X30 98-52 0.0 49.68
5061 2002-01-03 17988 Ventes: REQ/7954/7953/PLYWOOD 0.0 12.20
5037 2002-01-03 17990 Ventes: lat 10 12 16 1x12x16pp… 0.0 2.42
5037 2002-01-03 17991 Ventes: 2X4X10PP 0.0 7.50
5037 2002-01-03 17992 Ventes: DUCO BEIGE GOLD… 0.0 1.10
5037 2002-01-03 17993 Ventes: TOLE PVC 53.46 0.00

Why I am getting these wrong results ?

#5

Re: Search Error in Grid Search Criteria

I think we need to clear up some things first.

Your error message mentions odbc: dbase (DBF). Is this the correct driver? Are you trying to use DBF files, or?
You changed the settings for ‘YYYYMMDD’ to match DBF formats, but it is still using mm/dd/yy?
If you enter the query manually using the DBF date format I mention, does it work then? (‘20091010’)

Regards,
Scott.

#6

Re: Search Error in Grid Search Criteria

Yes indeed, I am using dbase(DBF) FILES.
I am upgrading an Clipper DOS Application
The date must be expressed in French Format (DD/MM/YYYY)

I SET THE REGIONAL SETTINGS AS FOLLOW
Regional Settings No
Detail Mask dd/mm/yyyy

What is it that I need to do Scott to fix the problem and move foward with the search ?

#7

Re: Search Error in Grid Search Criteria

I will have to duplicate your setup and get back with you.

Is it required that you maintain the use of DBF files as the old clipper app is still in use? In using DBF for years and writing several DBF database managers, I would recommend you change to an SQL based DB if possible (just a thought)

Anyhow, I will see if I can setup something soon.

Regards,
Scott.