PROBLEM: VBA SQL

  • Thread starter Martin \(Martin Lee\)
  • Start date
M

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
 
J

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?
Click to expand...

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]
 
Top