使用VBA在Access表中插入当月的每个日期记录 [英] Insert each date record for current month in Access table using VBA

问题描述

我是VBA编程的新手.我设计了一种有按钮的表格.单击此按钮后，我想在具有一个日期列的表中插入记录.虚拟记录的数量应等于当月的天数.如果月份为2016年5月，则会插入日期为1/5/2016的记录， 像这样的2016年2月5日............ 2016年5月31日.

I am new with VBA programming. I have designed one form in which there is a button. After clicking this button I want to insert record in my table having one date column. The number of dummy record should be equals to number of days in current month. If Month is May 2016 then record will inserted with date 1/5/2016, 2/5/2016.......31/5/2016 like that.

谢谢.

Private Sub Command0_Click()
    Dim strQuery As String
    Dim currDateTime As Date
    currDateTime = Now()
    strQuery = "INSERT INTO tbl_ShipOrders (IDate ) VALUES (" & currDateTime & " )"
    CurrentDb.Execute (strQuery)
End Sub

推荐答案

以下表达式将为您提供一个整数，即当月的天数:

The following expression will give you an integer, the number of days in the current month:

Day(DateSerial(Year(Date()), Month(Date())+1, 0))

这是下个月的第零天，也就是当月的最后一天.如果从12月移至1月，此表达式仍然有效.

This is the zeroth day of the following month, which is the last day of the current month. This expression will still work if moving from December to January.

将其存储在变量中，例如lastDay，然后使用循环For x = 1 To lastDay进行插入.

Store this in a variable, say lastDay then use a loop, For x = 1 To lastDay to perform the inserts.

在循环中，此表达式

DateSerial(Year(Date()), Month(Date()), x)

将为您提供日期1/5/2016、2/5/2016，..，31/5/2016.

will give you the dates 1/5/2016, 2/5/2016,.., 31/5/2016.

插入时，还应在日期周围加上日期定界符#.将此与日期yyyy-mm-dd的ISO格式结合使用(这样就不会将月份解释为天):

You should also surround the dates with date delimiters # when inserting. Combine this with ISO formatting of the date yyyy-mm-dd (so that months won't be interpreted as days):

VALUES (#" & Format(dtValue, "yyyy-mm-dd") & "#)"

其中dtValue是使用先前的DateSerial表达式刚刚形成的日期.

where dtValue is the date you've just formed using the previous DateSerial expression.

这篇关于使用VBA在Access表中插入当月的每个日期记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！