Web Development

Ask something
Discussion Locked

DateDiff in Access

0 helpful
15 replies

by bambamz

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a Field Named RTDDate and a Field that is FURDate and they are both set as Date/time in the properties. I then have a total field that is currently set as Number. I input the following code in the FURDate field after update CtlTotal = DateDiff("D", "RTDDate", "FURDate"). I want Total field to show me the number of days between these two dates. I get a Runtime Error '13' type mismatch. HELP. I don't care if the formula is in the FURDate after update or the Total field upon entry or what I just need to see the difference between the two dates.

15 replies

by bambamz

This didn't work. If I only put this formula then it gives me an error unexpected something or other. Does it make a difference that the formula and fields are in the sub form? If my two date fields are in date format what format should the total field be in?

by techrep

RTDDate and FURDate definitely should not be in quotes. If you are working with a form, try this:

CtlTotal = DateDiff("D", Form.RTDDate, Form.FURDate)

Or for a report:

CtlTotal = DateDiff("D", Report.RTDDate, Report.FURDate)

by bambamz

Does it make a difference that the formula and fields are in the sub form? If my two date fields are in date format what format should the total field be in?

by adasys

try this:
dim ctltotal
ctltotal=datediff("d",rtddate,furdate)

Make sure both dates does not have a null value

by adasys

Ithing you have not yet open your db file.
try this again in vbs.
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "yourdata.mdb" 'paths are acceptable
set rs=Server.CreateObject("ADODB.recordset")
rs.Open "Select * from table_name", conn
dim ctlTotal
ctlTotal = datediff("d",rs("RTDDATE"),rs("FURDATE"))
wscript.echo ctltotal

by bambamz

This probably makes sense but it is way over my head. Are you saying put this in before I open the database? how would I do this? I have the forms open and input this in the FURDate field after update as an event procedure.

by adasys

Please give me some of the details of your form.
Where does FURDATE and RTDDATE came from.
If you say there are the field names how is it being save there? Is it via form?
the you better calculate it from the form.objectname before you save the ctltotal then.

by bambamz

I have a table on a server then I have the front end forms on the hard drive. I link the tables to the forms. Then I created the main form and then a subform on the main form. The sub form is linked by the SSN and I may have multiple dates for FURDate and RTDDate and then I need to know the total. So I am trying to input he formula on the subform where it calls these fields and the total field is on the same sub form and same table.

by bambamz

i am so sorry to day that this did not work either. I don't know what I am missing.

by bambamz

I suppose you can't idiot proof an idiot. All of these worked and I wish that I could go back and give you all points. I had the dang CtlTotal field named Total instead of Ctl Total. Don't hate me because I'm stupid.

This discussion is closed to new replies.