DateAdd Problem

[Bruno]

I've been looking for the solution for my problem in the forums but could'nt find what I need. I've read some posts where many of you answer that we should not try to store calculated values in tables but...

Here what I'm trying to do.

I've a table where I store some recurrent billings like let's say insurance, water, electricity, phone expenses, etc. Those come always at a recurrent date with an expected value. I've set up a form so I can see the records already in the table and insert new ones manually. Now, I want to be able to set up a report where I can see all the expenses for the next year (or whatever interval of time I choose) for that I would like from, let's say this month phone bill to create automatically the next twelve months already populated with dates, values, etc.

So when the bill comes we'll only have to insert some data (Ref. number, etc.) and change the status from let's say "expected" to "paid"

I've created a button on the form that runs a procedure that will ask how many records I want to append to the table

Some values are not to be changed so I do something like:

rst![Documento] = Doc

and there is no problem but when I try

rst![Data Início] = DateAdd(m, nPer, StartDate)

where StartDate = Me.Data Início (input from the form)

I get "Run-time error '5': Invalid procedure call or argument"

Probably the code doesn't look so well for experienced programmers but shouldn't it work?

Thanks for your help.


Code:

Private Sub Orçamentar_Click()
Dim db As DAO.Database, rst As DAO.Recordset
Dim i As Integer, StartDate As Date, EndDate As Date, EmDate As Date, VenDate As Date, nRegistos As Integer
Dim Seg As String, Doc As String, nDoc As String, Val As Currency, Sit As String, Per As String, nPer As Integer


' Point to this database
Set db = CurrentDb


' Open a recordset on the table
Set rst = db.OpenRecordset("Fidelidade Mundial", dbOpenDynaset, dbAppendOnly)


'Ask for input How many records do you want to create
nRegistos = InputBox("Quantos registos quer criar", "My Inputbox")

'Grab the data off the form
Seg = Me.Seguro
Doc = Me.Documento
nDoc = ""
StartDate = Me.Data_Início
EndDate = Me.Data_Fim
EmDate = Me.Data_de_Emissão
VenDate = Me.Data_Vencimento
Val = Me.Valor_a_Pagar
Sit = "Orçamentado"
Per = Me.Periodicidade

If Per = "Mensal" Then nPer = 1
If Per = "Bimensal" Then nPer = 2
If Per = "Trimestral" Then nPer = 3
If Per = "Semestral" Then nPer = 6
If Per = "Anual" Then nPer = 12


'Loop to add records
For i = 1 To nRegistos

'Add a new record
rst.AddNew
'Populate the fields of the new record
rst![Seguro] = Seg
rst![Documento] = Doc
rst![Data Início] = DateAdd(m, nPer, StartDate)
rst![Data Fim] = DateAdd(Dateinterval.Month, nPer, EndDate)
rst![Data de Emissão] = DateAdd(Dateinterval.Month, nPer, EmDate)
rst![Data Vencimento] = DateAdd(Dateinterval.Month, nPer, VenDate)
rst![Situação] = Sit
rst![Periodicidade] = Per
rst.Update
rst.Close
nPer = nPer * i
Next

Set rst = Nothing
Set db = Nothing
End Sub

 

[Bruno]

ok, when reviewing the post I realised that for example nDoc is doing nothing in there but it shouldn't be a problem anyway. nDoc is for the number of the document (the bill) witch is one of the fields we'll have to insert when we receive the bill.
Until then it will remain empty.

 

[DrallocD]

You need to fix the following line to put double quotes around the m or change to Dateinterval.Month if that works.

rst![Data Início] = DateAdd(m, nPer, StartDate)

rst.Close needs to happen after the Next, not before it.

you can't use nPer = nPer * i (remove and use nPer * i in the DateAdd, or use another variable to hold the result)

 

[Bruno]

Hi! I'll try to change it back but actually I had it with Dateinterval.Month first. I'll tell you if it fixes it.
For the other two, you're certainly right, tanks. I'll use another variable to store the nPer*i
and put the rst.Close where it belongs.

Thanks again.

 

[Bruno]

well, I did the changes you told me and now I'm getting an "object required" in that same line and rst![Data_Início] = <object not found in this collection>

It can't be a spelling problem since the Me.Data_Início woks and the form was created from the table (same names). I've chabge all the names like "Data Início" to "Data_Início" just to be sure.

 

[DrallocD]

Did you try using "m" rather than Dateinterval.Month ?

Also try assigning:

rst![Data Início] = Now

just to check that the problem is not related to the fieldname / table.

 

[Bruno]

yes I tried to use "m",
Also switched this line with the next n the code and the problem switched acordingly. It seems the problem is no the fieldname.
Now I tried rst![Data Início] = Now as you suggested and I got error 3265 Item not found in this collection.
I first though the problem was with the recordset or the table but why is there no problem with the constants (I mean like with rst![Seguro]=seg when seg=Me.[Seguro])?

 

[Bruno]

I tried this:
For i = 1 To nRegistos

' Add a new record
rst.AddNew
xPer = nPer * i
newEmDate = DateAdd("m", xPer, EmDate)
newStartDate = DateAdd("m", xPer, StartDate)
newEndDate = DateAdd("m", xPer, EndDate)
newVenDate = DateAdd("m", xPer, VenDate)
rst![Seguro] = Seg
rst![Documento] = Doc
rst![Nº] = ""
rst![Data_de_Emissão] = newEmDate
rst![Data_Início] = newStartDate
rst![Data_Fim] = newEndDate
rst![Data_Vencimento] = newVenDate
rst![Situação] = Sit
rst![Periodicidade] = Per
rst.Update
Next

So the DateAdd woks and the newDates are ok but the problem is when it tries to creatwe the fields rst![Date], also no problem with the rst![strings].

I'll try to change the Date format to check

 

[DrallocD]

To confirm that the problem is not with the table, try create the recordset using:

Set rst = db.OpenRecordset("SELECT [Seguro], [Documento], [Data Início], [Data Fim], [Data de Emissão], [Data Vencimento], [Situação], [Periodicidade] FROM [Fidelidade Mundial]", dbOpenDynaset, dbAppendOnly)

or use underscores if you have now update the field names.

 

[Bruno]

It changes nothing.
I also tried to create the records only with the fields that are not dates and they are created ok, just doesn't work with dates!!!!
So until now the records are created ok, the DateAdd works but it won't create accept the data in the date fields either because It doesn't like the names other because of the format.

I can't think of anything else, I'll try to create fields with simple names (like "Dia") and see if it works

 

[Bruno]

I chabged the names from Início to Inicio and Emissão to Emissao and it works now!!!!!
Still think it's bizarre anyway because there is no trouble with "Situação"
Sorry for the time you spent I should have thought about that.

Tanks DrallocD!

 

[DrallocD]

I am glad to hear that it is now working.

One last thing you could try to avoid renaming would be to use:

rst.fields("Data_Início") rather than rst![Data_Início]