Problem with select Statment in Foxpro project 2

[makdu]

Hi,
I have a problem with the select statement with a Date field in foxpro project

SELECT * from &TRDB  where inpdate >  {ltr} into table temp.dbf 
use temp.dbf 
GO BOTTOM
 @ 2,3 SAY  RECNO()

I tired

SELECT * from &TRDB  where inpdate >  {^ltr} into table temp.dbf

where &TRDB is a table name and ltr is a date value (eg: 02/03/2011)
But the select statement is always returning whole table .
What am i doing wrong with the Select Statement

 

[Mike Lewis]

You can't use the curly brackets syntax with a date variable (ltr, in your example). If you remove the curly brackets, it should work OK.

In other words, instead of this:

SELECT * from &TRDB  where inpdate >  {ltr} into table temp.dbf

Do this:

SELECT * from &TRDB  where inpdate >  ltr into table temp.dbf

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Mike is right, ltr just needs to be a date variable, not a string. To convert string to date use CTOD(), but be aware this is sensitive to the current setting of how to convert dates to strings.

It would be okay to use a string in the format ^ YYYY-MM-DD woth curly but that would need to be done this way:
ltr = "2012-08-18"
select... where inpdate> {&ltr}

And it will be more effective to go the route Mike suggests and make ltr as date variable.

Bye, Olaf.

 

[Olaf Doschke]

Sorry for the typos. Most important it should be {^&ltr} with an ltr string prepared that way.

If ltr is a date value anyway you can use Mike's suggestion without any change, but then it would be really puzzling why you think you need to put a date variable within curly braces.

Bye, Olaf.

 

[Mike Lewis]

Also, Makdu, I'm surprised that you say that the Select statement is returning the whole table. Judging from the code you posted, I would have expected something like a "Date/datetime contains illegal characters" error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

There are a lot of questions about the syntax here. None of it could possibly work which makes me think there are typos in the original post.

However, the query could return all records if the value in the variable evaluates to an empty() date. Everything > Nothing! <g> It's also possible that the dbf is the result of a previous query. There could be some faking himself out here.

Need input! (Number Five Alive!)

 

[Mike Lewis]

the query could return all records if the value in the variable evaluates to an empty() date.

True, but you would still get an error by virture of the variable within the curly brackets. We really need Makdu to come back and give us some feedback on the replies he's received so far.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Makdu could look into temp.dbf from a previous sql without that wrong where clause. And an error handler could swallow the error. Many things are possible, I agree only Makdu can contribute to this with more info.

Bye, Olaf.

 

[TamarGranor]

In addition, you should switch from a macro (&) for the table to a name reference (parentheses). Macros can fail in that situation if the table name or path includes a space:

SELECT * from (TRDB) where inpdate >  ltr into table temp.dbf

Actually, unless you need the table to exist after you close the application, you should also probably use a cursor, rather than a table, as the destination.

SELECT * from &TRDB  where inpdate >  ltr into cursor temp

Tamar

 

[TamarGranor]

Oops, just realized that I left the macro when I showed selecting into cursor. This is better:

SELECT * from (TRDB)  where inpdate >  ltr into cursor temp

Tamar

 

[makdu]

Thank you all for the reply.
The ltr is a dat field only.
I am going to test the suggestions and will post you the result.

 

[makdu]

Thanks MikeLewis , Your solution worked .
The reason i went with Bracket is after searching for Date comparision. What i misssed is that my field is already a date variable.

 ltr=LTRM2
SELECT * from &TRDB  where inpdate >  ltr into table temp.dbf

where LTRM2 is the date field in the table
I need the select statment result after the table is closed. That is the reason , i wrote it to a temperory table

Thank you all for the suggestions.

 

[TamarGranor]

Needing the SELECT result after the source table is closed doesn't require a temporary table. You only need to create a temporary table rather than a cursor if you need the result to stick around after the application is closed.

Tamar