PROBLEM: VBA SQL

[Martin \(Martin Lee\)]

I WANT TO:
IF THE OVERDUEDATE has nothing in it, ACCESS will set the RATE as "NORMAL",
as well as set the OVERDUE as "NONOVERDUE"


SO, I WROTE:
DoCmd.RunSQL "UPDATE CUSTOTABLE1 where CUSTOTABLE1.OVERDUEDATE is null SET
CUSTOTABLE1.RATE = NORMAL, CUSTOTABLE1.OVERDUE = NONOVERDUE"

HOWEVER,THIS VBA SQL is unavailable.

MY QUESTION: WHAT'S THE PROBLEM OF THIS VBA? HOW TO CORRECT IT?

THANK YOU!

MARTIN LEE
2007-04-17

 

[John W. Vinson]

SO, I WROTE:
DoCmd.RunSQL "UPDATE CUSTOTABLE1 where CUSTOTABLE1.OVERDUEDATE is null SET
CUSTOTABLE1.RATE = NORMAL, CUSTOTABLE1.OVERDUE = NONOVERDUE"

HOWEVER,THIS VBA SQL is unavailable.

MY QUESTION: WHAT'S THE PROBLEM OF THIS VBA? HOW TO CORRECT IT?

Just the order of the operations. SET comes before WHERE. Well, you need
quotemarks around the text strings too:

UPDATE CUSTOTABLE1 SET
CUSTOTABLE1.RATE = 'NORMAL', CUSTOTABLE1.OVERDUE = 'NONOVERDUE;
where CUSTOTABLE1.OVERDUEDATE is null;

Note that typing in ALL CAPS is hard to read and considered impolite; it's the
text equivalent of shouting.

John W. Vinson [MVP]