Update Query saying 0 Records will be update.

[NBVC]

Update Query saying 0 Records will be updated

Hi Guys,

Not sure what I am missing here.

I have a simple Select Query based on one table.

In SQL View, the query is:

SELECT SYSADM_CUSTOMER_ORDER.ID, SYSADM_CUSTOMER_ORDER.STATUS
FROM SYSADM_CUSTOMER_ORDER
WHERE (((SYSADM_CUSTOMER_ORDER.ID) Like 'Q%') AND ((SYSADM_CUSTOMER_ORDER.STATUS)="H"));

so basically getting all records in the CUSTOMER_ORDER table that have ID beginning with Q and the STATUS is H (on hold).

I want to simply update these to change the STATUS to C (closed).

I converted the SELECT Query to an UPDATE Query and added a "C" in the Update To Field.

The SQL View is now:

UPDATE SYSADM_CUSTOMER_ORDER SET SYSADM_CUSTOMER_ORDER.STATUS = "C"
WHERE (((SYSADM_CUSTOMER_ORDER.ID) Like 'Q%') AND ((SYSADM_CUSTOMER_ORDER.STATUS)="H"));

But for some reason Access is telling me that it will update 0 records. There are over 2500 records to update.

Any ideas on what I am missing?

 

[pbaldy]

Try * for the wildcard.

 

[NBVC]

Hi pbaldy,

I am not exactly sure what you mean.

How do I set up the Update Query then?

When I try to create the Update query, Access tells me that you can't run an update query on the * wildcard

(Note:: Access 2003)

 

[pbaldy]

I mean change % to *.

 

[NBVC]

Oh, I see.

Yes, that worked. Thanks.

Can you tell me why, the SELECT worked fine with the %, but the Update did not? And why it works with *?

 

[pbaldy]

Generally speaking, JET/ACE and DAO use *, ADO uses %. I'm a little surprised it worked in the SELECT query, so perhaps something has changed. Is the data in SQL Server?

 

[NBVC]

The data is actually in ORACLE.

 

[pbaldy]

Here are the basics:

http://office.microsoft.com/en-us/access-help/access-wildcard-character-reference-HA010076601.aspx

I tested using % and it failed against an Access table but worked against a linked SQL Server table, so I guess sometimes the wildcard is getting passed through to the back end, sometimes not.

 

[NBVC]

Ok. Great. I'll keep that in mind.

Thanks again for the quick help.