View Designer

[chpicker]

*BANGS HEAD ON WALL*

Can anyone explain what the &%$& VFP is thinking here? The other sad part, of course, is that it was NOT doing this yesterday...

I created a view to a table using the View Designer (not the Wizard). I created a variable called Timerange in the dev environment. I then created a filter condition on a datetime field that was worded like this:

timefield BETWEEN datetime()-m.Timerange,datetime()+m.timerange

This worked yesterday...today, however, I get an Operator/Operand Type Mismatch when I try to save the query. I look at the SQL code it generated and here is the WHERE clause it generated:

WHERE table.timefield BETWEEN "DATETIME()-M.timerange" AND CTOD("DATETIME()+M.timerange"

WTF!? Ok, no problem...I go to the Filter tab, get rid of the quotes and the CTOD function, and re-save it...and it puts them back! The ONLY way I've managed to get around this is to DELETE the View and re-create it. However...I am NOT able to make any changes to it, because it KEEPS DOING THIS! Every time I want to make changes I have to delete and re-create, otherwise it does the stupidity you see above. Any clues as to why?

 

[rgbean]

In the View Designer, under filter I'd choose:
Field Name: table.timefield
Criteria: Between
Example: (datetime()-m.Timerange),(datetime()+m.timerange)

This seems to work. The first time I modify it, it throws away the surrounding parens, but everthing else stays as expected.

Rick

 

[ramani]

WHERE table.timefield BETWEEN "DATETIME()-M.timerange" AND CTOD("DATETIME()+M.timerange"

CHANGE THE CODE TO ...

WHERE BETWEEN(table.timefield , fromValue, ToValue)

Note that the error could stay if the fromValue and toValue types are not the same as timefield.
You have CTOD function on one and not on the other. Think about these as well..
ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK

 

[chpicker]

Heh...I wish it were that simple, Ramani...

I would certainly have changed the code to exactly what you have there if I were able to, but this is the View Designer...it does NOT allow me to manually modify the code since the SQL code view is Read-Only.

rgbean...that doesn't work for me, it still generates the exact same SQL string when I add parentheses.

As a side note...I even tried making it into 2 statements, one >=datetime()-m.timerange, one <=datetime()+m.timerange. In the generated SQL code...it put quotes around the expressions. *sighs*

Honestly, this whole thing would be made worlds easier if I COULD manually edit the SQL code. IS there a way to do this?