select array? 3

[juandiegomc]

Hello,

I am trying to copy the child's teacher's name into his fields called dex.teachernam and dex.teacherstr. This pulls the teachers name from the same dex table but I want to pull the teachers street too. What is messing me up when I add the second part on for the teacherstr (teacherstreet) to copy or replace this steeet in the kid's teacherstr field. Foxpro keeps asking me for a comma in the second part. What is missing here?

===========================

IF lnDexID # 0
SELECT DEX
SELECT ALLT(FirstName)+' '+IIF(!EMPTY(MiddleName),ALLT(MiddleName)+' ','') +ALLT(LastName) AS FullName ;
FROM DEX ;
WHERE DEX_ID == lnDexID ;
INTO ARRAY laPerson[1]
SELECT ALLT(Street)+' ',' '+IIF(!EMPTY(Town) AS AddressName ;
FROM DEX ;
WHERE DEX_ID == lnDexID ;
INTO ARRAY laPerson[2]

SELECT Dex
IF _TALLY # 0
REPLACE TEACHERNAM WITH laPerson[1]
REPLACE TEACHERSTR WITH laPerson[2]


juandiegomcc@afirmacion.com

 

[Mike Lewis]

Juan,

SELECT ALLT(Street)+' ',' '+IIF(!EMPTY(Town) AS AddressName ;
FROM DEX ;
WHERE DEX_ID == lnDexID ;
INTO ARRAY laPerson[2]

This is bad syntax. The IIF() function needs three arguments. You only have one, and you don't have a closing parenthesis.

Mike



Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[ramani]

HI

IF lnDexID # 0
SELECT DEX
SELECT FirstName - [' '+MiddleName] - ;
[' '+LastName] AS FullName ;
FROM DEX ;
WHERE DEX_ID == lnDexID ;
INTO ARRAY laPerson[1]
SELECT Street - ',' - Town AS AddressName ;
FROM DEX ;
WHERE DEX_ID == lnDexID ;
INTO ARRAY laPerson[2]

The reason, I suggested the above is that, when you use
ALLT(FirstName)+' '+IIF(!EMPTY(MiddleName),ALLT(MiddleName)+' ','') +ALLT(LastName) AS FullName

You are not sure of what is the net length of the first record picked up by the select, and SQL will restrict the rest of the records to the length of this first record picked up. SO if you have a short first address, it is likely, you ended up with truncated selected fields.

If your second SELECT is street as one field and then town as another field, modify my SQL suitably.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[juandiegomc]

Yes Ramani,

If your second SELECT is street as one field and then town as another field, modify my SQL suitably.

The fields are street and another is town and I want them to be replace the field of the child's row field called
(teacher's) street+town in one single field called teach.str but actually the field will contain the street + the town in the child's row field called teachstr,

now you say I must modify the sql? How? On the query we already have pulled up the teacher's dex_id and his full name so I just thought it would be a question of copy-replace-past into the child's field along with the teacher's fullname? Not. Maybe I am missing something.

juandiegomcc@afirmacion.com

 

[juandiegomc]

Hello,
Here is the change added, and how does it look?

IF lnDexID # 0
SELECT DEX
SELECT ALLT(FirstName)+' '+IIF(!EMPTY(MiddleName),ALLT(MiddleName)+' ','') +ALLT(LastName) AS FullName ;
FROM DEX ;
WHERE DEX_ID == lnDexID ;
INTO ARRAY laPerson[1]
SELECT Street - ',' - Town AS AddressName ;
FROM DEX ;
WHERE DEX_ID == lnDexID ;
INTO ARRAY laPerson[2]

SELECT Dex
IF _TALLY # 0
REPLACE TEACHERNAM WITH laPerson[1]
REPLACE TEACHERSTR WITH laPerson[2]


juandiegomcc@afirmacion.com

 

[juandiegomc]

Hello,

Can I simplify this array by putting both the fullname and the street+town into the same array?

Or does one have to make two arrays?

juandiegomcc@afirmacion.com

 

[ramani]

HI

SELECT ALLT(FirstName)+' '+IIF(!EMPTY(MiddleName),ALLT(MiddleName)+' ','') +ALLT(LastName) AS FullName, ;
Street - ',' - Town AS AddressName ;
FROM DEX ;
WHERE DEX_ID == lnDexID ;
INTO ARRAY laPerson



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[juandiegomc]

Hello Ramini,

Ok, I send what I think is the complete code, and see if
it is correct?

SELECT ALLT(FirstName)+' '+IIF(!EMPTY(MiddleName),ALLT(MiddleName)+' ','') +ALLT(LastName) AS FullName, ;
Street - ',' - Town AS AddressName ;
FROM DEX ;
WHERE DEX_ID == lnDexID ;
INTO ARRAY laPerson

=================
SELECT Dex
IF _TALLY # 0
REPLACE TEACHERNAM WITH laPerson.FullName
REPLACE TEACHERSTR WITH laPerson.AddressName
ENDIF

=================
I believe this is the code that will be correct to paste-replace the teacher's full name into its field on the child's row and then the techer's full address also into the field on the child's row for the teacher's address.

juandiegomcc@afirmacion.com

 

[AirCon]

Hi juandiegomc,

I think you were wrong. SELECT ... INTO ARRAY results an array, not an object. So you can't do that
Change with this:

SELECT ALLT(FirstName)+' '+IIF(!EMPTY(MiddleName),ALLT(MiddleName)+' ','') +ALLT(LastName) AS FullName, ;
Street - ',' - Town AS AddressName ;
FROM DEX ;
WHERE DEX_ID == lnDexID ;
INTO ARRAY laPerson

=================
SELECT Dex
IF _TALLY # 0
REPLACE TEACHERNAM WITH laPerson[1,1]
REPLACE TEACHERSTR WITH laPerson[1,2]
ENDIF



-- AirCon --

 

[juandiegomc]

HELLO,

I want to give a big star to you both, Ramani and AirCon

for this fine pointer. It works fine.

juandiegomcc@afirmacion.com