使ç¨ACCESSæ¥è¯¢å¯¹è±¡æ¯æ æ³ä¸æ¬¡è¿è¡å¤ä¸ªSQLè¯å¥çï¼ä½æ¯éè¿ACCESS VBA代ç å¯ä»¥å®ç°ä¸æ¬¡è¿è¡å¤ä¸ªSQLè¯å¥ãä¸é¢æ¯å
·ä½å®ç°æ¹æ¡;
1)æ°å»ºä¸ä¸ªACCESSçªä½ï¼çªä½æ·»å ä¸ä¸ªå½ä»¤æé®ï¼
2ï¼ç¼åæé®åå»äºä»¶è¿ç¨
1
2
3
4
5
6
7
Private Sub Command1_Click()
Dim strSql as string
strSQL="Update Policy SET LatestDueDate=Year(Date()) & '-' & Format(PolicyDate,'mm-dd')"
DoCmd.RunSql strSQL 'è¿è¡ç¬¬ä¸ä¸ªæ´æ°è¯å¥
strSQL="UPDATE Policy SET LatestDueDate= DateAdd('yyyy',1,LatestDueDate) WHERE (((Month(Date())-Month(LatestDueDate)) > 6) and(PaymentMode='H'))"
DoCmd.RunSql strSQL 'è¿è¡ç¬¬äºä¸ªæ´æ°è¯å¥
End Sub
è·çç¹å»è¯¥å½ä»¤æé®å°±å¯ä»¥ä¸æ¬¡æ§è¡ä¸¤æ¡è¯å¥äºã
*******ä¸è¿è¿éå¿
é¡»æåºæ¥¼ä¸»è¿ç§éè¿UPDATEæ´æ°æ¥è·åç¼´è´¹æéçåæ³ç»å¯¹æ¯ä¸ç§é常ç³ç³ç解å³æ¹æ¡ï¼å
¶åå¤é¤äºä½ 说çï¼æå¯ä»¥æåºNå¤æ¡ï¼å
·ä½å°±ä¸è¯´äºã******
å
¶å®è¦è·åä½ è¦è·åä¿åç»ææéæ¥æ,æ éå两æ¥åªè¦ä¸å¥SELECTè¯å¥å°±è½æå®ï¼å®å
¨æ²¡å¿
è¦å»æ´æ°æ°æ®è¡¨ã
å°ä¸åSQLè¯å¥ä¿å为ä¸ä¸ªå为âRenewAlertPoliciesçéæ©æ¥è¯¢
1
2
select PolicyNo,PolicyDate,DateAdd('yyyy',1,PolicyDueDate) as LatestDueDate
from Policy where DateDiff('m',PolicyDate, Date()) > 6
order by PolicyDate desc;
注æï¼ä¸é¢âä¿åå·âå段åæ¯æèæçï¼è¯·æ¿æ¢ä¸ºå®é
çä¿åå·å段åãå¦æä¸æ³ç太æ§çä¿åï¼çéå¯ä»¥ç¨æ¶é´æ®µæ¥éå¶ï¼ä¾å¦...where DateDiff('m',PolicyDate, Date()) between 7 and 12 ....
å½ä½ è¦æ¥çéè¦æ示ç»ä¿çä¿åå表æ¶ï¼ç®åå°åå»è¯¥æ¥è¯¢å°±å¥½äºï¼ä½ å¯ä»¥éè¦éæ¥ï¼é常æ¹ä¾¿ï¼æ¥è¯¢ç»æå°ä¼å±ç¤ºææ°çæ
åµï¼å®å
¨æ²¡å¿
è¦å»èèå¦ä½æ´æ°LatestDueDateçé®é¢ã