Data type mismatch in criteria expression

[#1]

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.

[bbi_mba_76]

Hi,

in access is working fine?

[#3]

Yes

[#4]

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.

[israrkhan]

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);

[#6]

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

[#7]

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)

[#8 · Accepted solution]

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#;

[#9]

You are right thanks it's working.