Updating query with IN STATEMENT failing.

[sjohnson77]

This is driving me absolutely insane, can anyone find why this won't work?

UPDATE tbl_PrivateMessages SET tbl_PrivateMessages.RecipientStatus = 1
WHERE tbl_PrivateMessages.MessageID IN ([Enter MessageIDs]);

I've tried updating it through ASP with the following:

Execute UPDATE_INBOX_MSG_STATUS 0,26,25

And also just tried running the query from access itself using 25,26 as IDs.

Neither of those seem to work...please advise. Also, I don't even get an error message as to why it didn't update.

TABLE STRUCTURE
MessageID - AutoNumber
MessageUserID - Number
MessageSubject - Text
MessageContent - Memo
MessageAuthor - Number
MessageDate - Date/Time
AuthorStatus - Number
RecipientStatus - Number

Many thanks

Steve Johnson

 

[gromit]

hi Steve -

My first recourse in these situations is to change the update query to a select query just to see what records are returned. Most of the time, the records that I think should be selected are not really included. Getting the right recordset sorted out takes care of most of these.

- g

 

[sjohnson77]

Arg*

Thanks g, but I have done that. Sorry I haven't listed through all the things I have tried. I've also notices that if I only supply 1 value to the variable([Enter MessageIDs]), it updates just fine.

I am perplexed....

Thanks

Steve

 

[gromit]

Steve -

It looks like your use of [Enter Message ID's] is problematic with multiple numbers. I don't use this technique often myself, but it works okay with a single ID, but doesn't seem to interpret multiple ones correctly.

I put [Enter ID #1], [Enter ID #2] as the IN statement and it prompts for two separate fields and seems to work. So it looks like it is an issue of how to pass a parameter list to a query (rather than anything with the UPDATE aspect of it).

Sorry, I'm probably not telling you anything that you don't already know.

Suggest that you re-post and see if someone can you with the parameter input.

- g

 

[sjohnson77]

Thanks Gromit

g-

Thanks for all your help and time looking into this. I was hoping to keep it in "stored procedure/view" form, but have now decided to just pass the entire update string to Access, instead of just the parameters. This method seems to work fine, but still frustrates me that it won't accept the parameters/variables alone...at least not how I've tried doing it.

If anyone else might have a suggestion, I'd surely be open to it.

Many thanks,

Steve