VBA SQL Autofill Table Records?

[DanBurn]

Hi First post here so hope the topic is in the right place. I have inherited a DB and I am trying to automate alot of old stuff and convert the macros into better functioning VBA.

My problem is one of the tasks that is performed is relying on a table that simply has dates listed to drop into another table see below.

ntblMonths
Month,Year,PeriodStart,PeriodEnd
1 2007 01/01/2007 31/01/2007
2 2007 01/02/2007 28/02/2007
etc.....................................................

Rather than re write the whole thing i was wondering has anyone got an idea of how to call SQl to accept a start date and an end date variable and then wright all those records inbeween into the table?

 

[namliam]

Why would you have this information in a table anyway... It is very easy to calculate
PeriodStart: Dateserial(year, month,1)
PeriodEnd: Dateserial(year, month+1,0)

Also having columns named Month and Year is not the best thing, they are reserved words (functions)

You can use something like" Between startdate and enddate
to filter and get a period...

 

[DanBurn]

Ill try explain a bit more the DB was created by someone else who had no knowledge of vba so thay have used poorly constructed macros with long workarounds to solve simple problems..

I have re written most of it but at the core of it is this table that you manually add dates into and it is copied into another table so that the end result is like this

Figs
Site number, Month, Year, Col Fatal, Col Serious, Col slight,
C001 1 2005 1 0 3
C001 2 2005 0 3 0

There is over 100 sites and they need to have a month number and year listed showing how many Collisions(COL) happened for every month for that site for the period you have asked for(the original data is pulled from map info).

Its hard to explain

 

[DanBurn]

Ill try explain a bit more the DB was created by someone else who had no knowledge of vba so thay have used poorly constructed macros with long workarounds to solve simple problems..

I have re written most of it but at the core of it is this table that you manually add dates into and it is copied into another table so that the end result is like this

Figs
Site number, Month, Year, Col Fatal, Col Serious, Col slight,
C001 1 , 2005 , ,1 , 0 , 3
C001 2 , 2005 , , 0 , 3 , 0

There is over 100 sites and they need to have a month number and year listed showing how many Collisions(COL) happened for every month for that site for the period you have asked for(the original data is pulled from map info).

Its hard to explain why but having it auto fill out the records would be the most simple solution.

 

[namliam]

So??
You have a month/year in one table that is related to another table with the same fields to get the start and end dates of those periods.

I dont see the problem/issue? Maybe I just dont understand?

 

[DanBurn]

I know i dont get why it has been done this way..but what is happening is there are multiple append queries going on and the only reason i can see why, is to get lots of information from lots of different tables that are unrelated to each other into one table so that it can be displayed on one report... The information is completly unrelated and i think thats where the problem comes in... The end result is a report that you would not be able to create using relating data it would need multiple subreports and that would just look messy...I dont think i can explain anyfurther...Can you just forget about the why and suggest a solution which is to get SQl to write multiple records to a table when a user gives two dates.
I.e user inputs 01/01/2006 and 31/12/2007 and the result table would be

Month, Year
1, 2006
2,2006
3,2006
etc......to
12,2007

 

[namliam]

At the risk of repeating myself....

You can use something like Dateserial(year, month,1) to convert your Month/Year setup into a real date then use the user input to limit the return of the records on the dateserial field.

Alternatively you can convert the user entry to your Month/Year setup.
In order to do that ... you have to split it into 2 seperate parts
a) For multi year selections
Split this into 3
1) Start year/month
2) End year/month
3) Any full years inbetween
b) For same year selections

(   Year(startdate) < year(enddate)
and (  (Month >= Month(startdate) and year = Year(StartDate))
    or (Month <= Month(enddate) and year = year(enddate))
    or (year > Year(startdate) and Year < Year(startdate)
    )
or 
(   Year(startdate) = Year(enddate)
and Month >= Month(startdate) 
and Month <= Month(enddate)
)

I would say the dateserial solution is probably easier.

 

[DanBurn]

Cheers mate ill give it a go..I didn't see it before in all the confusion

 

[DanBurn]

I probably took along route around here but i can only work with my own knowledge... Heres how i got around it... Just for info

Sub Dan1()
Dim cnEnforce As ADODB.Connection
Dim rsMonths As ADODB.Recordset
Dim datestart As Date
Dim dateend As Date
Dim strSQLNMD As String
DoCmd.SetWarnings False
'Connect to record set and allow editing
 
Set cnEnforce = CurrentProject.Connection
 
Set rsMonths = New ADODB.Recordset
With rsMonths
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open "ntblMHold", cnEnforce
End With
'Delete all Records in Output Table
    strSQLNMD = "Delete * FROM ntblMHold;"
    DoCmd.RunSQL strSQLNMD
 
'Goto First Record
If Not rsMonths.BOF And Not rsMonths.EOF Then
rsMonths.MoveFirst
End If
'Grab dates from Form
datestart = #1/1/2000# '(change to form later)
dateend = #12/31/2007# '(change to form later)
'Adds New record (startdate) then adds one month to (startdate) and loops until (startdate = enddate).
For datestart = datestart To dateend
    rsMonths.AddNew "PeriodStart", datestart
 
    datestart = DateAdd("m", 1, datestart)
 
    rsMonths.MoveNext
 
Next datestart
'Close connections and empty
cnEnforce.Close
Set rsMonths = Nothing
Set cnEnforce = Nothing
DoCmd.OpenQuery "qryNtblMonth ", acViewNormal, acEdit
DoCmd.SetWarnings True
End Sub

 

[namliam]

But this is not working with Year/Month at all?? I dont get it?

 

[DanBurn]

This just fills the table with the first day of each month...If you notice at the end of the code there is a query I just used that with (datepart()) to get year and month and for end of month i used (PeriodEnd: DateSerial(Year([PeriodStart]),Month([PeriodStart])+1,1)-1) like you suggested

 

[namliam]

So the whole problem was/is that you want to convert any old average date into a period/month??

I.e. This will do that for you...
BeginMonth: YourDate - day(YourDate) + 1
EndMonth: Dateadd("M",1,YourDate - day(YourDate) + 1)-1

If you want month and year... Month(yourdate) and Year(yourdate) will do that....

I dont understand the point of the ntblMonths table.

 

[DanBurn]

I wish i could send you my DB for you to look at then you could see why but is 600mb big...