arrrggghhh!! Select statements..

Hello,

I am not sure why I can't get my select to work.

this is what I am doing

select *,pmt_date as pmtdate from payment ;
where payment.pmt_date between sdate and edate
order by invo_num into dbf (dir_temp+'temp1.dbf')

select * from temp1,invlines ;
where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')

payment looks like this

paynum invonum pmt_date amount rptrnum
1 100 1/1/07 200.00 15
2 100 1/1/07 700.00 29


invlines looks like this

invonum linenum rptrnum product amount
100 1 15 a $50
100 2 15 b $150
100 3 29 c $500
100 4 29 d $200

I want to get this:

invonum pmt_date rptrnum product amount linenum
100 1/1/07 15 a $50 1
100 1/7/07 15 b $150 2
100 1/7/07 29 c $500 3
100 1/7/07 29 d $200 4

but I keep getting!!!

invonum pmt_date rptrnum product amount linenum
100 1/1/07 15 a $50 1
100 1/1/07 29 a $50 1
100 1/7/07 15 b $150 2
100 1/7/07 29 b $150 2
100 1/7/07 29 c $500 3
100 1/7/07 15 c $500 3
100 1/7/07 29 d $200 4
100 1/7/07 15 d $200 4

sorry but i don't even know what topic to search this under if this already
came up. I would search for going to throw the computer out but who knows
what will come up then. Thank you in advance in helping this frustrated soul.

MikeA
Thu Dec 13 13:01:10 PST 2007

First, you should be able to do this all in one Select statement. If you
want to continue to use two separate Select statements, then you may need to
look at INNER JOIN so that you don't get all possibilities when combining
the tables.

Why are you not doing this all in one select statement?

Mike



"Emily" <Emily@discussions.microsoft.com> wrote in message
news:75590517-D69B-4329-BFFD-8D28C300EBF3@microsoft.com...
> Hello,
>
> I am not sure why I can't get my select to work.
>
> this is what I am doing
>
> select *,pmt_date as pmtdate from payment ;
> where payment.pmt_date between sdate and edate
> order by invo_num into dbf (dir_temp+'temp1.dbf')
>
> select * from temp1,invlines ;
> where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')
>
> payment looks like this
>
> paynum invonum pmt_date amount rptrnum
> 1 100 1/1/07 200.00 15
> 2 100 1/1/07 700.00 29
>
>
> invlines looks like this
>
> invonum linenum rptrnum product amount
> 100 1 15 a $50
> 100 2 15 b $150
> 100 3 29 c $500
> 100 4 29 d $200
>
> I want to get this:
>
> invonum pmt_date rptrnum product amount linenum
> 100 1/1/07 15 a $50 1
> 100 1/7/07 15 b $150 2
> 100 1/7/07 29 c $500 3
> 100 1/7/07 29 d $200 4
>
> but I keep getting!!!
>
> invonum pmt_date rptrnum product amount linenum
> 100 1/1/07 15 a $50 1
> 100 1/1/07 29 a $50 1
> 100 1/7/07 15 b $150 2
> 100 1/7/07 29 b $150 2
> 100 1/7/07 29 c $500 3
> 100 1/7/07 15 c $500 3
> 100 1/7/07 29 d $200 4
> 100 1/7/07 15 d $200 4
>
> sorry but i don't even know what topic to search this under if this
> already
> came up. I would search for going to throw the computer out but who knows
> what will come up then. Thank you in advance in helping this frustrated
> soul.
>

MikeA
Thu Dec 13 13:05:50 PST 2007

Should have mentioned with Inner Join I was referring to the select:

I have not tested this but instead of:

> select * from temp1,invlines ;
> where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')

try something like:

select * from invlines inner join temp1 ;
where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')

Then it will use all the found records from invlines and only merge the data
from temp table and not add additional records.

Mike


"Emily" <Emily@discussions.microsoft.com> wrote in message
news:75590517-D69B-4329-BFFD-8D28C300EBF3@microsoft.com...
> Hello,
>
> I am not sure why I can't get my select to work.
>
> this is what I am doing
>
> select *,pmt_date as pmtdate from payment ;
> where payment.pmt_date between sdate and edate
> order by invo_num into dbf (dir_temp+'temp1.dbf')
>
> select * from temp1,invlines ;
> where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')
>
> payment looks like this
>
> paynum invonum pmt_date amount rptrnum
> 1 100 1/1/07 200.00 15
> 2 100 1/1/07 700.00 29
>
>
> invlines looks like this
>
> invonum linenum rptrnum product amount
> 100 1 15 a $50
> 100 2 15 b $150
> 100 3 29 c $500
> 100 4 29 d $200
>
> I want to get this:
>
> invonum pmt_date rptrnum product amount linenum
> 100 1/1/07 15 a $50 1
> 100 1/7/07 15 b $150 2
> 100 1/7/07 29 c $500 3
> 100 1/7/07 29 d $200 4
>
> but I keep getting!!!
>
> invonum pmt_date rptrnum product amount linenum
> 100 1/1/07 15 a $50 1
> 100 1/1/07 29 a $50 1
> 100 1/7/07 15 b $150 2
> 100 1/7/07 29 b $150 2
> 100 1/7/07 29 c $500 3
> 100 1/7/07 15 c $500 3
> 100 1/7/07 29 d $200 4
> 100 1/7/07 15 d $200 4
>
> sorry but i don't even know what topic to search this under if this
> already
> came up. I would search for going to throw the computer out but who knows
> what will come up then. Thank you in advance in helping this frustrated
> soul.
>

Gene
Thu Dec 13 13:18:54 PST 2007

Emily <Emily@discussions.microsoft.com> wrote:

>I am not sure why I can't get my select to work.
>
>this is what I am doing
>
>select *,pmt_date as pmtdate from payment ;
> where payment.pmt_date between sdate and edate
> order by invo_num into dbf (dir_temp+'temp1.dbf')
>
>select * from temp1,invlines ;
> where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')

This is doing what you tell it to. When invo_num matches,
select.

I am guessing that your where clause expression should be
temp1.invo_num=invlines.invo_num and
temp1.rptrnum=invlines.rptrnum

[snip]

>invlines looks like this
>
>invonum linenum rptrnum product amount
>100 1 15 a $50
>100 2 15 b $150
>100 3 29 c $500
>100 4 29 d $200

It would be easier to read if the columns lined up. They might
for you; they do not for me. Using a fixed-width font is best. I
almost gave the wrong columnname, because 15 looks to be under
product.

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Emily
Thu Dec 13 14:22:03 PST 2007

BECAUSE I AM NOT VERY GOOD AT THIS:((
I studied C++ when in college not db programming. I hate it actually and
that is due to the lack of knowledge and the frustration that follows. I
need a very thorough tutorial. Any ideas?



"MikeA" wrote:

> Should have mentioned with Inner Join I was referring to the select:
>
> I have not tested this but instead of:
>
> > select * from temp1,invlines ;
> > where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')
>
> try something like:
>
> select * from invlines inner join temp1 ;
> where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')
>
> Then it will use all the found records from invlines and only merge the data
> from temp table and not add additional records.
>
> Mike
>
>
> "Emily" <Emily@discussions.microsoft.com> wrote in message
> news:75590517-D69B-4329-BFFD-8D28C300EBF3@microsoft.com...
> > Hello,
> >
> > I am not sure why I can't get my select to work.
> >
> > this is what I am doing
> >
> > select *,pmt_date as pmtdate from payment ;
> > where payment.pmt_date between sdate and edate
> > order by invo_num into dbf (dir_temp+'temp1.dbf')
> >
> > select * from temp1,invlines ;
> > where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')
> >
> > payment looks like this
> >
> > paynum invonum pmt_date amount rptrnum
> > 1 100 1/1/07 200.00 15
> > 2 100 1/1/07 700.00 29
> >
> >
> > invlines looks like this
> >
> > invonum linenum rptrnum product amount
> > 100 1 15 a $50
> > 100 2 15 b $150
> > 100 3 29 c $500
> > 100 4 29 d $200
> >
> > I want to get this:
> >
> > invonum pmt_date rptrnum product amount linenum
> > 100 1/1/07 15 a $50 1
> > 100 1/7/07 15 b $150 2
> > 100 1/7/07 29 c $500 3
> > 100 1/7/07 29 d $200 4
> >
> > but I keep getting!!!
> >
> > invonum pmt_date rptrnum product amount linenum
> > 100 1/1/07 15 a $50 1
> > 100 1/1/07 29 a $50 1
> > 100 1/7/07 15 b $150 2
> > 100 1/7/07 29 b $150 2
> > 100 1/7/07 29 c $500 3
> > 100 1/7/07 15 c $500 3
> > 100 1/7/07 29 d $200 4
> > 100 1/7/07 15 d $200 4
> >
> > sorry but i don't even know what topic to search this under if this
> > already
> > came up. I would search for going to throw the computer out but who knows
> > what will come up then. Thank you in advance in helping this frustrated
> > soul.
> >
>
>
>

Emily
Thu Dec 13 14:29:01 PST 2007

THIS DID IT. Can you explain if it isn't too much trouble why it does this?
I just want to understand instead of just following blindly.

And sorry about the font, yes it does look aligned here. I will keep this
in mind next time I have to do this again.



"Gene Wirchenko" wrote:

> Emily <Emily@discussions.microsoft.com> wrote:
>
> >I am not sure why I can't get my select to work.
> >
> >this is what I am doing
> >
> >select *,pmt_date as pmtdate from payment ;
> > where payment.pmt_date between sdate and edate
> > order by invo_num into dbf (dir_temp+'temp1.dbf')
> >
> >select * from temp1,invlines ;
> > where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')
>
> This is doing what you tell it to. When invo_num matches,
> select.
>
> I am guessing that your where clause expression should be
> temp1.invo_num=invlines.invo_num and
> temp1.rptrnum=invlines.rptrnum
>
> [snip]
>
> >invlines looks like this
> >
> >invonum linenum rptrnum product amount
> >100 1 15 a $50
> >100 2 15 b $150
> >100 3 29 c $500
> >100 4 29 d $200
>
> It would be easier to read if the columns lined up. They might
> for you; they do not for me. Using a fixed-width font is best. I
> almost gave the wrong columnname, because 15 looks to be under
> product.
>
> [snip]
>
> Sincerely,
>
> Gene Wirchenko
>
> Computerese Irregular Verb Conjugation:
> I have preferences.
> You have biases.
> He/She has prejudices.
>

Emily
Thu Dec 13 14:29:03 PST 2007

OH THANK YOU VERY MUCH



"Gene Wirchenko" wrote:

> Emily <Emily@discussions.microsoft.com> wrote:
>
> >I am not sure why I can't get my select to work.
> >
> >this is what I am doing
> >
> >select *,pmt_date as pmtdate from payment ;
> > where payment.pmt_date between sdate and edate
> > order by invo_num into dbf (dir_temp+'temp1.dbf')
> >
> >select * from temp1,invlines ;
> > where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')
>
> This is doing what you tell it to. When invo_num matches,
> select.
>
> I am guessing that your where clause expression should be
> temp1.invo_num=invlines.invo_num and
> temp1.rptrnum=invlines.rptrnum
>
> [snip]
>
> >invlines looks like this
> >
> >invonum linenum rptrnum product amount
> >100 1 15 a $50
> >100 2 15 b $150
> >100 3 29 c $500
> >100 4 29 d $200
>
> It would be easier to read if the columns lined up. They might
> for you; they do not for me. Using a fixed-width font is best. I
> almost gave the wrong columnname, because 15 looks to be under
> product.
>
> [snip]
>
> Sincerely,
>
> Gene Wirchenko
>
> Computerese Irregular Verb Conjugation:
> I have preferences.
> You have biases.
> He/She has prejudices.
>

Emily
Thu Dec 13 14:30:01 PST 2007

All I had to was add the rprtr num in the where like Gene said. I was going
to attempt the inner join but since I am not that great with regular selects
I figured I may blow something up :))

thanks again for your post though



"MikeA" wrote:

> Should have mentioned with Inner Join I was referring to the select:
>
> I have not tested this but instead of:
>
> > select * from temp1,invlines ;
> > where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')
>
> try something like:
>
> select * from invlines inner join temp1 ;
> where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')
>
> Then it will use all the found records from invlines and only merge the data
> from temp table and not add additional records.
>
> Mike
>
>
> "Emily" <Emily@discussions.microsoft.com> wrote in message
> news:75590517-D69B-4329-BFFD-8D28C300EBF3@microsoft.com...
> > Hello,
> >
> > I am not sure why I can't get my select to work.
> >
> > this is what I am doing
> >
> > select *,pmt_date as pmtdate from payment ;
> > where payment.pmt_date between sdate and edate
> > order by invo_num into dbf (dir_temp+'temp1.dbf')
> >
> > select * from temp1,invlines ;
> > where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')
> >
> > payment looks like this
> >
> > paynum invonum pmt_date amount rptrnum
> > 1 100 1/1/07 200.00 15
> > 2 100 1/1/07 700.00 29
> >
> >
> > invlines looks like this
> >
> > invonum linenum rptrnum product amount
> > 100 1 15 a $50
> > 100 2 15 b $150
> > 100 3 29 c $500
> > 100 4 29 d $200
> >
> > I want to get this:
> >
> > invonum pmt_date rptrnum product amount linenum
> > 100 1/1/07 15 a $50 1
> > 100 1/7/07 15 b $150 2
> > 100 1/7/07 29 c $500 3
> > 100 1/7/07 29 d $200 4
> >
> > but I keep getting!!!
> >
> > invonum pmt_date rptrnum product amount linenum
> > 100 1/1/07 15 a $50 1
> > 100 1/1/07 29 a $50 1
> > 100 1/7/07 15 b $150 2
> > 100 1/7/07 29 b $150 2
> > 100 1/7/07 29 c $500 3
> > 100 1/7/07 15 c $500 3
> > 100 1/7/07 29 d $200 4
> > 100 1/7/07 15 d $200 4
> >
> > sorry but i don't even know what topic to search this under if this
> > already
> > came up. I would search for going to throw the computer out but who knows
> > what will come up then. Thank you in advance in helping this frustrated
> > soul.
> >
>
>
>

MikeA
Thu Dec 13 15:36:16 PST 2007

Gene was right - I didn't have too much time to analyze the select but you
can do what Gene said all in one select. There is no need to use one
followed by another. It's kind of hard for me to know exactly how to do the
select without knowing what fields rptrnum and invonum are in what tables
but if those keys are in both the payment and invlines table then you would
use both like this:

select * from payment,invlines ;
where payment.invonum = invlines.invonum and ;
payment.rptrnum = invlines.rptrnum .and. ;
Between(payment.pmt_date,sdate,edate) ;
order by invo_num into cursor Temp1

Note that Between() is a function

Mike


"Emily" <Emily@discussions.microsoft.com> wrote in message
news:38CAFB57-1394-4412-B343-34E1D2132A3B@microsoft.com...
> THIS DID IT. Can you explain if it isn't too much trouble why it does
> this?
> I just want to understand instead of just following blindly.
>
> And sorry about the font, yes it does look aligned here. I will keep this
> in mind next time I have to do this again.
>
>
>
> "Gene Wirchenko" wrote:
>
>> Emily <Emily@discussions.microsoft.com> wrote:
>>
>> >I am not sure why I can't get my select to work.
>> >
>> >this is what I am doing
>> >
>> >select *,pmt_date as pmtdate from payment ;
>> > where payment.pmt_date between sdate and edate
>> > order by invo_num into dbf (dir_temp+'temp1.dbf')
>> >
>> >select * from temp1,invlines ;
>> > where temp1.invo_num=invlines.invo_num into dbf
>> > (dir_temp+'temp2b.dbf')
>>
>> This is doing what you tell it to. When invo_num matches,
>> select.
>>
>> I am guessing that your where clause expression should be
>> temp1.invo_num=invlines.invo_num and
>> temp1.rptrnum=invlines.rptrnum
>>
>> [snip]
>>
>> >invlines looks like this
>> >
>> >invonum linenum rptrnum product amount
>> >100 1 15 a $50
>> >100 2 15 b $150
>> >100 3 29 c $500
>> >100 4 29 d $200
>>
>> It would be easier to read if the columns lined up. They might
>> for you; they do not for me. Using a fixed-width font is best. I
>> almost gave the wrong columnname, because 15 looks to be under
>> product.
>>
>> [snip]
>>
>> Sincerely,
>>
>> Gene Wirchenko
>>
>> Computerese Irregular Verb Conjugation:
>> I have preferences.
>> You have biases.
>> He/She has prejudices.
>>

Gene
Thu Dec 13 17:21:40 PST 2007

Emily <Emily@discussions.microsoft.com> wrote:

>THIS DID IT. Can you explain if it isn't too much trouble why it does this?
>I just want to understand instead of just following blindly.

You said to select from two tables. Every row in one table will
be matched against every row in the other table for the condition that
you specify. No condition means every possibility.

Your condition has to be restrictive enough to eliminate the
combinations that do not apply. When do you want to see a
combination? That is your condition.

Commonly, your where condition will include each of the common
column types. Note that I did not write "column names". Two columns
might be of the same type, but have different names.

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Anders
Thu Dec 13 16:40:14 PST 2007

Note also the BETWEEN(date, sdate, edate) is an Xbase function. The SQL
version is
date BETWEEN sdate AND edate
Both work in VFP but as soon as you start communicating with other SQL
databases, only the SQL version works.

-Anders

"Emily" <Emily@discussions.microsoft.com> wrote in message
news:7052B481-FCD0-4F47-B45F-46F3726E3264@microsoft.com...
> OH THANK YOU VERY MUCH
>
>
>
> "Gene Wirchenko" wrote:
>
>> Emily <Emily@discussions.microsoft.com> wrote:
>>
>> >I am not sure why I can't get my select to work.
>> >
>> >this is what I am doing
>> >
>> >select *,pmt_date as pmtdate from payment ;
>> > where payment.pmt_date between sdate and edate
>> > order by invo_num into dbf (dir_temp+'temp1.dbf')
>> >
>> >select * from temp1,invlines ;
>> > where temp1.invo_num=invlines.invo_num into dbf
>> > (dir_temp+'temp2b.dbf')
>>
>> This is doing what you tell it to. When invo_num matches,
>> select.
>>
>> I am guessing that your where clause expression should be
>> temp1.invo_num=invlines.invo_num and
>> temp1.rptrnum=invlines.rptrnum
>>
>> [snip]
>>
>> >invlines looks like this
>> >
>> >invonum linenum rptrnum product amount
>> >100 1 15 a $50
>> >100 2 15 b $150
>> >100 3 29 c $500
>> >100 4 29 d $200
>>
>> It would be easier to read if the columns lined up. They might
>> for you; they do not for me. Using a fixed-width font is best. I
>> almost gave the wrong columnname, because 15 looks to be under
>> product.
>>
>> [snip]
>>
>> Sincerely,
>>
>> Gene Wirchenko
>>
>> Computerese Irregular Verb Conjugation:
>> I have preferences.
>> You have biases.
>> He/She has prejudices.
>>

Mark
Fri Dec 14 00:40:11 PST 2007

Emily

assuming that you are specifically looking for help with VFP SQL then try
http://www.hentzenwerke.com/catalog/tamingvfpsql.htm it is a very well
written guide to how to do this. if you want something more general i
believe there was an SQL for dummies that was a quite good write up on how
to write SQL statements.
Hope this helps with your frustration levels.


"Emily" <Emily@discussions.microsoft.com> wrote in message
news:03019DF1-7996-41DC-9B35-DC217584A815@microsoft.com...
> BECAUSE I AM NOT VERY GOOD AT THIS:((
> I studied C++ when in college not db programming. I hate it actually and
> that is due to the lack of knowledge and the frustration that follows. I
> need a very thorough tutorial. Any ideas?
>
>
>
> "MikeA" wrote:
>
>> Should have mentioned with Inner Join I was referring to the select:
>>
>> I have not tested this but instead of:
>>
>> > select * from temp1,invlines ;
>> > where temp1.invo_num=invlines.invo_num into dbf
>> > (dir_temp+'temp2b.dbf')
>>
>> try something like:
>>
>> select * from invlines inner join temp1 ;
>> where temp1.invo_num=invlines.invo_num into dbf (dir_temp+'temp2b.dbf')
>>
>> Then it will use all the found records from invlines and only merge the
>> data
>> from temp table and not add additional records.
>>
>> Mike
>>
>>
>> "Emily" <Emily@discussions.microsoft.com> wrote in message
>> news:75590517-D69B-4329-BFFD-8D28C300EBF3@microsoft.com...
>> > Hello,
>> >
>> > I am not sure why I can't get my select to work.
>> >
>> > this is what I am doing
>> >
>> > select *,pmt_date as pmtdate from payment ;
>> > where payment.pmt_date between sdate and edate
>> > order by invo_num into dbf (dir_temp+'temp1.dbf')
>> >
>> > select * from temp1,invlines ;
>> > where temp1.invo_num=invlines.invo_num into dbf
>> > (dir_temp+'temp2b.dbf')
>> >
>> > payment looks like this
>> >
>> > paynum invonum pmt_date amount rptrnum
>> > 1 100 1/1/07 200.00 15
>> > 2 100 1/1/07 700.00 29
>> >
>> >
>> > invlines looks like this
>> >
>> > invonum linenum rptrnum product amount
>> > 100 1 15 a $50
>> > 100 2 15 b $150
>> > 100 3 29 c $500
>> > 100 4 29 d $200
>> >
>> > I want to get this:
>> >
>> > invonum pmt_date rptrnum product amount linenum
>> > 100 1/1/07 15 a $50
>> > 1
>> > 100 1/7/07 15 b $150 2
>> > 100 1/7/07 29 c $500 3
>> > 100 1/7/07 29 d $200 4
>> >
>> > but I keep getting!!!
>> >
>> > invonum pmt_date rptrnum product amount linenum
>> > 100 1/1/07 15 a $50
>> > 1
>> > 100 1/1/07 29 a $50
>> > 1
>> > 100 1/7/07 15 b $150 2
>> > 100 1/7/07 29 b $150 2
>> > 100 1/7/07 29 c $500 3
>> > 100 1/7/07 15 c $500 3
>> > 100 1/7/07 29 d $200 4
>> > 100 1/7/07 15 d $200 4
>> >
>> > sorry but i don't even know what topic to search this under if this
>> > already
>> > came up. I would search for going to throw the computer out but who
>> > knows
>> > what will come up then. Thank you in advance in helping this
>> > frustrated
>> > soul.
>> >
>>
>>
>>

Emily
Fri Dec 14 09:31:01 PST 2007

Thank you again

"Gene Wirchenko" wrote:

> Emily <Emily@discussions.microsoft.com> wrote:
>
> >THIS DID IT. Can you explain if it isn't too much trouble why it does this?
> >I just want to understand instead of just following blindly.
>
> You said to select from two tables. Every row in one table will
> be matched against every row in the other table for the condition that
> you specify. No condition means every possibility.
>
> Your condition has to be restrictive enough to eliminate the
> combinations that do not apply. When do you want to see a
> combination? That is your condition.
>
> Commonly, your where condition will include each of the common
> column types. Note that I did not write "column names". Two columns
> might be of the same type, but have different names.
>
> [snip]
>
> Sincerely,
>
> Gene Wirchenko
>
> Computerese Irregular Verb Conjugation:
> I have preferences.
> You have biases.
> He/She has prejudices.
>

Emily
Fri Dec 14 09:32:02 PST 2007

Yeah I inheritted this program and it does have a bunch of old code in there.
Probably why I sometimes have a hard time finding proper help on stuff.
Looking up outdated crap. :))

Thanks.



"Anders Altberg" wrote:

> Note also the BETWEEN(date, sdate, edate) is an Xbase function. The SQL
> version is
> date BETWEEN sdate AND edate
> Both work in VFP but as soon as you start communicating with other SQL
> databases, only the SQL version works.
>
> -Anders
>
> "Emily" <Emily@discussions.microsoft.com> wrote in message
> news:7052B481-FCD0-4F47-B45F-46F3726E3264@microsoft.com...
> > OH THANK YOU VERY MUCH
> >
> >
> >
> > "Gene Wirchenko" wrote:
> >
> >> Emily <Emily@discussions.microsoft.com> wrote:
> >>
> >> >I am not sure why I can't get my select to work.
> >> >
> >> >this is what I am doing
> >> >
> >> >select *,pmt_date as pmtdate from payment ;
> >> > where payment.pmt_date between sdate and edate
> >> > order by invo_num into dbf (dir_temp+'temp1.dbf')
> >> >
> >> >select * from temp1,invlines ;
> >> > where temp1.invo_num=invlines.invo_num into dbf
> >> > (dir_temp+'temp2b.dbf')
> >>
> >> This is doing what you tell it to. When invo_num matches,
> >> select.
> >>
> >> I am guessing that your where clause expression should be
> >> temp1.invo_num=invlines.invo_num and
> >> temp1.rptrnum=invlines.rptrnum
> >>
> >> [snip]
> >>
> >> >invlines looks like this
> >> >
> >> >invonum linenum rptrnum product amount
> >> >100 1 15 a $50
> >> >100 2 15 b $150
> >> >100 3 29 c $500
> >> >100 4 29 d $200
> >>
> >> It would be easier to read if the columns lined up. They might
> >> for you; they do not for me. Using a fixed-width font is best. I
> >> almost gave the wrong columnname, because 15 looks to be under
> >> product.
> >>
> >> [snip]
> >>
> >> Sincerely,
> >>
> >> Gene Wirchenko
> >>
> >> Computerese Irregular Verb Conjugation:
> >> I have preferences.
> >> You have biases.
> >> He/She has prejudices.
> >>
>
>
>

Gene
Fri Dec 14 10:06:30 PST 2007

Emily <Emily@discussions.microsoft.com> wrote:

>Thank you again

You are welcome.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.