WHERE CLAUSE HELP 3

[lashwarj]

Heres the code I got so far but it still does the same thing. If you insert one and only one record into the list box it works great, but if you insert more then one record it will dump more then one record into the secord process table. I * out process 2 and it runs great, my values from the list box are dumped. Then is I undo the * it dumps the desired values, and when it comes time to dump the values where the one table doesnt match the second table it dumps all the ones that dont match and a few that do match.


PROCESS 1

WITH ThisForm.Agenda_List
FOR indx = 1 to .ListCount
SELECT agenda_order_info
M.MEETING_DATE = meetingdate
M.PM_ID = VAL(.ListItem(.IndextoItemID(indx), 1))
M.AGENDA_ORDER = indx
INSERT INTO agenda_order_info FROM MEMVAR
NEXT
ENDWITH


PROCESS 2

SELECT pm_id, meeting_date FROM agenda_order_view;
WHERE agenda_order_view.meeting_date = meetingdate ;
AND agenda_order_info.pm_id <> agenda_order_view.pm_id ;
INTO ARRAY laArray
INSERT INTO agenda_order_info FROM ARRAY laArray

 

[AirCon]

lashwarj

WHERE agenda_order_view.meeting_date = meetingdate

What is the second &quot;mettingdate&quot; ? You must specify what it is, so it doesn't confuse the system.
If you have from memory variable use &quot;M.&quot;
If it is from table, then use tablename in front of it.

Try it and run again.

-- AirCon --

 

[lashwarj]

meetingate is a variable off my form, and the first part works it where Process 2 kicks in

 

[AirCon]

Ahh.. didn't notice that. Sorry I got confuse with your code (too many meetingdate )

Please try this:
SELECT a.pm_id, a.meeting_date ;
FROM agenda_order_view a, agenda_order_info b;
WHERE a.meeting_date = meetingdate ;
AND b.pm_id <> a.pm_id ;
INTO ARRAY laArray


-- AirCon --

 

[lashwarj]

nope no go, actually dumps more records then........

 

[ramani]

HI

SELECT pm_id, meeting_date FROM agenda_order_view;
WHERE agenda_order_view.meeting_date = meetingdate ;
AND agenda_order_info.pm_id <> agenda_order_view.pm_id ;
INTO ARRAY laArray
INSERT INTO agenda_order_info FROM ARRAY laArray

Aircon suggestion will work, if the comarison is related to all records of agenda_order_info.

However, if your intention is to only use the current record of agenda_order_info.. then.. transfer it to a variable at the appropriat place and use that variable. The error could be because the record pointer is not at the correct record of that table when this SQL is excuted.



ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[lashwarj]

Nope no. It dumps section one correctly its when you go to section two.

EX. you have 10 record.
you put 3 in a list box, then when you click save it indexs on the position and assigns a number.
Then in section 2 is searches the entire table for values = meeting_date but not already in the table, then dump them into the table, with no position.

It dumps section 2, but doesnt do only where, it grabs a few record that are already in the table you are dumping too.

MY ID field is an autonumber field in access, would that have any issues related. (Dont think it does but had to ask)

 

[AirCon]

I'm not really follow your explanation, but I think second suggestion from Ramani will work for you.

Try again with this:

process 1

cWhere = ''
with thisform.Agenda_List
for indx = 1 to .listcount
select Agenda_Order_Info
m.meeting_date = MeetingDate
m.pm_id = val(.listitem(.indextoitemid(indx), 1))
m.agenda_order = indx
insert into Agenda_Order_Info from memvar
cWhere = cWhere + ', ' + alltrim(str(m.pm_id))
next
if !empty(cWhere)
cWhere = '!inlist(Agenda_Order_View.pm_id' + cWhere + ')'
endif
endwith


process 2

select pm_id, meeting_date from Agenda_Order_View;
where Agenda_Order_View.meeting_date = MeetingDate ;
and &cWhere ;
into array laArray
insert into Agenda_Order_Info from array laArray


-- AirCon --

 

[lashwarj]

ok that works great, but could you tell me what I would run a test on to find if there where no records not equal. Right now if it finds there are records in the one table and not the other it runs great, if it finds there are no records missing from one it gives a can not find LaArray.

So

IF table1.pm_id <> table2.pm_id =====OK
IF table1.pm_id = table2.pm_id ===== Can not find LaArray

 

[MrFancyteeth]

try

if type(&quot;larray&quot#&quot;U&quot;
insert into Agenda_Order_Info from array laArray
endif

mrF

 

[lashwarj]

Bingo, but can you explain what that does, I mean it works, but whats it break down to being in simple terms

 

[lashwarj]

oh wait, it doesnt, its not doing the insert at all for the <> values, sorry about that... If i do the code you put it only does the process from the listbox

 

[lashwarj]

Got it, spelling error, I should have double checked the code before running it, thank you so much guys, you are all gods