Update query not updating 1

[topwaya]

I have the following query:

UPDATE ParticipantListing INNER JOIN tblPersonal ON ParticipantListing.User = tblPersonal.User SET ParticipantListing.[START DATE] = [tblPersonal].[FCSD]
WHERE (((ParticipantListing.[MEMBER STATUS])=". Yes a Member"));

When I run this query, it is supposed to update the tblPersonal.FCSD with the ParticipantListing.[START DATE] for people designated as "members".

When I run the query from design mode, it displays one column with the heading of "START DATE" and a list of dates. When I run the query by doubleclicking it, it says that it is going to update 144 rows, and I say Yes, but nothing is updated.

When I check the tblPersonal table, the FCSD field has no updates.

Any clue as to what I'm doing wrong?

Thanks for any help!

 

[PHV]

it is supposed to update the tblPersonal.FCSD with the ParticipantListing.[START DATE]
... SET [tblPersonal].[FCSD] = ParticipantListing.[START DATE] ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

Thank you for the quick response!

I tried it, and it said it was going to update 144 rows again, I said Yes, but when I check the tblPersonal table, nothing is updated.

When I tried it from design view, it lists an FCSD column with 144 blank fields.

There are no existing dates in the tblPersonal.FCSD field, does that make a difference? If so, how do I work around it? Anything else I can look for?

Thanks so much for your help! I worked on this all day, I think I'm crazy

 

[PHV]

Seems there are null dates in ParticipantListing.
UPDATE tblPersonal INNER JOIN ParticipantListing ON tblPersonal.User = ParticipantListing.User
SET tblPersonal.FCSD = [ParticipantListing].[START DATE]
WHERE ParticipantListing.[MEMBER STATUS])=". Yes a Member"
AND [ParticipantListing].[START DATE] Is Not Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

Again, thanks for your quick response! That makes sense - wish I would've thought of it.

Unfortunately, now when I run it from design view I get 1 blank row in an FCSD column.

When I doubleclick it, it says that it will update 0 rows, so I just cancel.

Anything else I can try?

 

[PHV]

Anything else I can try?
Review the values you have in ParticipantListing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

There are over 1700 rows in tblPersonal and over 900 rows in ParticipantListing.

178 of the rows have matching "User" fields. 144 of those are members.

Didn't know if that made a difference or not.

 

[PHV]

178 of the rows have matching "User" fields. 144 of those are members.
And NONE of those have a valid [START DATE] !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

I have reviewed the values. Exactly what am I to look for? FCSD and [START DATE] both are set to:
Date/Time
Format: Short Date
Input Mask: 99/99/0000;0;_

I scrolled through all the dates, and they visually look OK - is there anything else I can check?

 

[topwaya]

OK, I tried another update query and took out any "date" references and it works fine. So the problem is with the dates, what do I have to do, retype all the dates? Any way to reformat them or something? I don't even know what's wrong with them, they look fine.

Thanks very much for all your help, I was trying to figure this out all day, at least now I know that the dates are the culprit.

If you have any suggestions on how to fix the dates, I'm all ears!

 

[topwaya]

I retyped 5 dates in the [START DATE] column for people that are members. When I run the query it still says it will update 0 rows.

Anything else I can try?

 

[topwaya]

OK, I'm getting closer.

I exported the ParticipantListing table and it had (example):

1/1/2005 0:00

for the [START DATE] field.

So I deleted all the " 0:00" and imported it back. Still doesn't work, but I will see if I can find anything else.

 

[topwaya]

yeah!!!! I figured it out.

After realizing that the 0:00 was *supposed* to be there, I went back to my original excel file that I imported to make the ParticipantListing table. There were a few "#####" where the date was supposed to be. When I clicked it, the date appeared. I imported it again, and the update query worked - YEAH!!!!!

Thank you for helping me find where to look for what was wrong, I was so stumped all day - I really do appreciate it!!!