Perimeter Value

[Hecronis]

I'm sure this is a round about way of doing something simple but it's my first time trying something like this and it's the best I could come up with.

I have to append to and update 5 fields ("Resident", "Meeting Date", "In House", "Outside", and "WHU") in a table named "Meetings". The record set I'm using for my "update to" is a query called "Current Residents" which have 8 fields in it ("Resident", "Meeting Date", "In House", "Outside", and "WHU" and also "In House 2", "Outside 2", and "WHU 2"). On a form I have all of my current residents displayed where using "In House", "Outside", and "WHU" I can enter the number of meetings they have gone to for a given day. A code is set up to check if that date has been added already, run a query to append them to the "Meetings" table if it hasn't yet. If the date has been added already, I had to be able to add to any numbers that may already be in the "Meetings" table to the numbers that were being added. To do that I set up an update query to copy the numbers from "Meetings" for that date to "In House 2", "Outside 2", and "WHU 2" in the "Current Resident" table. Sum that and the new numbers being added and send the new totals back to the "Meeting" table. Everything actually works the way it's supposed to except I get an Enter Perimeter Value message for "Current Residents!Outside 2" when ever it runs the update. It's only that one that does that and everything is set up the same throughout the whole deal. I'm at a loss.

 

[Isskint]

Hi

field name Outside 2 has a space in it. You need to surround it in square brackets, so [Outside 2]. Or is the field name actually Outside_2?

 

[Hecronis]

Just checked it again. Brackets are where they should be and the spelling is correct.

 

[Isskint]

Could you post the SQL of that query please?

 

[Hecronis]

UPDATE [Meetings 1] RIGHT JOIN [Current Residents] ON [Meetings 1].Resident = [Current Residents].ID_Number SET [Meetings 1].[In House] = Nz([Current Residents]![In House],0)+Nz([Current Residents]![In House 2],0), [Meetings 1].[WHU] = Nz([Current Residents]![WHU],0)+Nz([Current Residents]![WHU 2],0), [Meetings 1].Outside = Nz([Current Residents]![Outside],0)+Nz([Current Residents]![Outside 2],0)
WHERE ((([Meetings 1].[In House])>0)) OR ((([Meetings 1].[WHU])>0)) OR ((([Meetings 1].Outside)>0));

 

[Minty]

Why have you used ! and not . ?

 

[Hecronis]

I wouldn't be able to tell you. I don't know anything about SQL. I just use what ever access builds when I'm in query design haha

 

[Minty]

Is Current Residents a form, a table or both ?

If its both I would be tempted to rename the form as there may be some ambiguity, while you are at it remove all the spaces from your names and you will have much easier to read code, queries etc..

 

[Hecronis]

It's a query. I learned not to use spaces in names only recently. By then I was most of the way into building this thing. It does get a little frustrating at times but I'm getting very used to when to use brackets, underscores, and/or quotations. The idea of having to change all of the code, tables, queries, expressions, etc. is a little daunting. :banghead:

 

[BeeJayEff]

One reason for a lack of responses to your question is that it is extremely difficult to read your original post. Try using the Enter key to break up the block of text into paragraphs.

 

[Hecronis]

Sorry I haven't been on too much this week. I apologize, I'm not too good at explaining myself on here. I was just going to up load what I had so you could see but some how (I'm still not sure what I did) I got that message to stop coming up.