Trouble updating a field in another table with query

[superjeff]

First of all thank you for your patience.. I have been tasked to Access without any experiance.

So.. I have a field in table A (used as my main form) that I would like to update with a unique yet understandable name. I have created a query that takes the first few characters of two other fields and the event date and concatenates them to a unique name.

Challange is the three fields that create the unique name are in table B.

So I tried the folowing in query design just to see if I could update a field in the same table

 Expr1: IIf(([EVENT_TBL].[EVENT]="Jacksonville" And [EVENT_TBL].[EVENT_DATE]="4/17/2011" And [EVENT_TBL].[EVENT_TYPE]="PRE-DEPLOYMENT"),([EVENT_TBL].[EVENT_TYPDTE_ID]="PREJACK170411"))

The results were #Error# in the cells.

I greatly appreciate your time and guidance.
Respectfully,
Jeff

[pbaldy]

Try this (I'm assuming the event date is a date/time field)


Expr1: IIf([EVENT_TBL].[EVENT]="Jacksonville" And [EVENT_TBL].[EVENT_DATE]=#4/17/2011# And [EVENT_TBL].[EVENT_TYPE]="PRE-DEPLOYMENT","PREJACK170411", "WhoKnows")

[RayMilhon]

Why have in IIF? Just do the conversion. You can use the criteria of each individual field to make sure that each element provides the correct detail.

Expr1:left(event_tbl],[event_type],3) & Left([Event_tbl].[event],4) & format([event_tbl].[event_date],"ddmmyyyy")

[superjeff]

Gentlemen,
Thank you very much for your quick response. Paul, that did elimante the errors but the query shows now results... the event_tbl though does have plenty of those fields. Any ideas? Ray, I tried pasting that into the query but there were syntax errors. I'm probably not putting it in th eright spot.
Thank you again for your time and patience,
Jeff

[pbaldy]

I would certainly agree with Ray that the IIf() is not the most dynamic solution; I was trying to help you find your error. Try this, as there was a typo in there:

Expr1:left(event_tbl].[event_type],3) & Left([Event_tbl].[event],4) & format([event_tbl].[event_date],"ddmmyyyy")

[superjeff]

Oh I see Ray.. Yes the reformatting of the date is nice and a much cleaner concat..thank you. Now I need to update another field in the Employee table [EMPLOYEE].[Event_Typdte]. How do I check the [Event_tbl][event] which can be any city and the [Event_tbl][Event_Type] has 3 choices and of course the [Event_tbl][event_date] so that when the employee table is updated it puts in the correct Event_TypDte?
Thank you!
Jeff

[superjeff]

Holy Bat Crap! I did it... You guys are the best. Now I can go home!! YAY!! I really appreciate the selflessness gentlemen like you have to help schlubs out like me. Hopefully one day I will be able to reciprocate the favor...though doubtfully with anything Access related.

[pbaldy]

LOL! We were happy to help.

[RayMilhon]

Oops didn't even notice that typo. That's what I get for being blind with fat fingers. (Did you buy that excuse? If not, I've got others) Actually, it's called rushing and not checking my typing.

Always happy to help.

[pbaldy]

Funny thing is that I just realized I left a different typo in there (opening bracket on the first table name).

Amazing Jeff was able to get past all of them.

[RayMilhon]

Nothing like a typo to make us godly Access Experts seem human. (of course in the real world outside of my own mind I'm neither godly or (some say) human but that's another story)

[superjeff]

Dear Smart People,
So, I have this awesome gift you gave me

Expr1: Left([EVENT_TBL].[EVENT_TYPE],3) & Left([EVENT_TBL].[EVENT],4) & Format([EVENT_TBL].[EVENT_DATE],"ddmmyyyy")

but I have been trying to run it in an update query and I keep getting a type conversion failure. Is that because one of the fields is a date? Is there a way around that? I'm thinking I need this unique name so that I am not overwriting existing records.
Thank you for your time and consideration,

Jeff

[superjeff]

Wait! Disregard that one! I want to save that question and use it at a later time for a different perplexity! I figured out that it didn't like me trying to save it to a field in the same table so I tried it on the FK in the junction table and waalllaa! So... thanks for your time and sorry to be such a bother. It is a great confidence booster however, to know that kind people like you are out there!
Respectfully,
Jeff

[pbaldy]

Glad you were able to sort it out. We stand by for the really tough one!

[superjeff]

MAY DAY - MAY DAY! I have a main form called VISIT with subforms Lodging, Soldier, Event and FM_Visit (which has a subform Family). I can search from the [Visits].[SSN] field and I will pull the correct info for all of the fields on the form. When I try and Add Record however, the Save Record button does not save record. Furthermore, I have found a few instances where I will search and not find the record but if I try and Add Record with the same SSN from the SOLDIER subform I receive an error "The changes you requested to the table were not successfull becuase they would create duplicate values in the index..." ..No problem, I just have to have this workinging by tomorrow... Any one hiring for a terrific janitor? Below I have a picture of my relationships, I hope you can see the picture.
I appreciate your guidance, jeff