topic Re: Data type mismatch in criteria expression in QlikView

Data type mismatch in criteria expression

Wed, 23 Oct 2013 09:22:17 GMT

Hi,

When I am tring to reload access database table

CompanyItems:

LOAD ID,

Name,

Contact,

Book,

ItemType,

Priority,

Date;

SQL SELECT *

FROM CompanyItems

where Date between '01/07/2013' and '25/10/2013';

I am getting below error

Data type mismatch in criteria expression.

SQL SELECT *

FROM CompanyItems

where Date between '01/07/2013' and '25/10/2013'

Could anyone help me how to correct this.

Re: Data type mismatch in criteria expression

bbi_mba_76 — Wed, 23 Oct 2013 09:27:14 GMT

Hi,

in access is working fine?

Re: Data type mismatch in criteria expression

Wed, 23 Oct 2013 09:32:29 GMT

Yes

Re: Data type mismatch in criteria expression

Wed, 23 Oct 2013 09:39:27 GMT

Hi,

Maeby you can use Date function over date values just to ensure the Database knows you are passing dates and not strings. This is ofcourse if the source data type is Date.

Re: Data type mismatch in criteria expression

israrkhan — Wed, 23 Oct 2013 09:41:13 GMT

Hi,

Try This, Make sure Date is in same format.

CompanyItems:

LOAD ID,

Name,

Contact,

Book,

ItemType,

Priority,

Date;

SQL SELECT *

FROM CompanyItems

where Date between TRY_CAST('01/07/2013' AS Date) and TRY_CAST('25/10/2013' AS Date);

Re: Data type mismatch in criteria expression

Wed, 23 Oct 2013 09:41:18 GMT

Thanks and sorry can you explain a bit more as I am not clear what it means.

Re: Data type mismatch in criteria expression

Wed, 23 Oct 2013 09:44:22 GMT

Thanks when I am using this it's showing below error

SQL##f - SqlState: 37000, ErrorCode: 4294964196, ErrorMsg: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Date between TRY_CAST('01/07/2013' AS Date) and TRY_CAST('25/10/2013' AS Date)'.

SQL SELECT *

FROM CompanyItems

where Date between TRY_CAST('01/07/2013' AS Date) and TRY_CAST('25/10/2013' AS Date)

Re: Data type mismatch in criteria expression

Wed, 23 Oct 2013 09:57:23 GMT

Maeby you can try something like this:

CompanyItems:

LOAD ID,

Name,

Contact,

Book,

ItemType,

Priority,

Date;

SQL SELECT *

FROM CompanyItems

where Date between #01/07/2013# and #25/10/2013#;

Re: Data type mismatch in criteria expression

Wed, 23 Oct 2013 09:58:29 GMT

You are right thanks it's working.