topic Re: Script Syntax issue in QlikView

Script Syntax issue

samvile18 — Mon, 25 Feb 2013 11:45:36 GMT

Morning fellow Qlikers,

I'm trying to load some data directly from my MSAccess DB - I'd like to only load the data that falls in between two dates, I have the following in the where clause but it keeps crashing and I'm not sure why:

WHERE CRDATE >= '01/02/2013' AND <='28/02/2013' AND RIGHT(INXFLD01,1)='F';

Can anyone help me understand where I'm going wrong please?

Re: Script Syntax issue

sivarajs — Mon, 25 Feb 2013 11:51:28 GMT

WHERE CRDATE >= '01/02/2013' AND CRDATE<='28/02/2013' AND RIGHT(INXFLD01,1)='F';

Re: Script Syntax issue

manideep78 — Mon, 25 Feb 2013 12:35:19 GMT

Here for comparing date two times, you should write field name two times. Otherwise it will give syntax error.

WHERE CRDATE >= '01/02/2013' AND CRDATE<='28/02/2013' AND RIGHT(INXFLD01,1)='F';

Re: Script Syntax issue

samvile18 — Mon, 25 Feb 2013 12:41:48 GMT

Sorry about that chaps, looks like it didn't paste in properly...in my script I HAVE named the field twice and it's still not working.

Re: Script Syntax issue

sivarajs — Mon, 25 Feb 2013 12:46:39 GMT

Could you find where exactly you are getting error?

Re: Script Syntax issue

samvile18 — Mon, 25 Feb 2013 12:50:53 GMT

This is the error it's returning:

Re: Script Syntax issue

sivarajs — Mon, 25 Feb 2013 13:14:35 GMT

This might due to if date is not in a valid range.Please check you have value within the range that you have specified and also the timestamp format

Re: Script Syntax issue

Mon, 25 Feb 2013 13:51:05 GMT

WHERE CRDATE >= DATE('01/02/2013','DD/MM/YYYY')

AND CRDATE<= DATE('28/02/2013','DD/MM/YYYY')

AND RIGHT(INXFLD01,1)='F';

Re: Script Syntax issue

jonathandienst — Mon, 25 Feb 2013 14:14:16 GMT

It looks like the Access database has dates in a format different to what you are using (probably M/D/Y format). Comparing a date field to a string requires the string to be converted into a date and the conversion will attempt to use the date format used in the database (or the default date format for the PC set in the control panel)

Hope that helps

Jonathan

Re: Script Syntax issue

samvile18 — Tue, 26 Feb 2013 10:04:15 GMT

Thanks for all the help everyone, I'm still having odd issues though.

Even though te query is now pulling data back, it seems to be ignoring the criteria and bringing back everything for the year?!!

Any ideas?