General discussion

Locked

DateDiff in Access

By bambamz ·
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.

This conversation is currently closed to new comments.

15 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by DKlippert In reply to DateDiff in Access

Try:
DateDiff("d", [RTDDate], [FURDate]).

http://tinyurl.com/2hb74

Collapse -

by bambamz In reply to

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?

Collapse -

by techrep In reply to DateDiff in Access

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)

Collapse -

by bambamz In reply to

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?

Collapse -

by adasys In reply to DateDiff in Access

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


Make sure both dates does not have a null value

Collapse -

by bambamz In reply to

Oh darn this didn't work either.

Collapse -

by adasys In reply to DateDiff in Access

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

Collapse -

by bambamz In reply to

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.

Collapse -

by bambamz In reply to DateDiff in Access

Point value changed by question poster.

Collapse -

by adasys In reply to DateDiff in Access

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.

Back to Web Development Forum
15 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums