DateDiff Error (help needed ASAP, PLEASE!)

[ad2]

I’m using Access 2000.

I’m trying to find the number of weeks between two dates. Date fields are START_DATE and END_DATE, both on the same table.

I have a query with the two fields and in an empty field cell I’ve put:

DateDiff("ww", [START_DATE], [END_DATE])

This kicks out an invalid syntax error and highlights the comma after the “ww”.

I’ve tried several variations, but keep getting a syntax error. Per Access help the above DateDiff should be correct. Any suggestions??

 

[rjoubert]

I'm really just grasping at straws here, because I added two date fields to a table, created a query and put that same DateDiff function in my query, and it worked fine. The only difference is that I have Access 2003, but that DateDiff function has been around for many versions of Access.

Do you have any other tables in your query? If there's more than one table, try putting the table name in front of the field names...[TableName]![START_DATE]

 

[ad2]

I tested the same in a blank db with just one table and only the two fileds START_DATE and END_DATE and still got the syntax error.

This is driving me NUTS, cause you're right that function been valid for years.

I'll try adding the table name, maybe that'll help (Yikes)

 

[rjoubert]

Have you tried this on another PC? Perhaps there is something wrong with your Access installation.

 

[ad2]

I've tried adding the table name, but it didn't help.

I did try it on another computer, but it has Access 2003, so it did work.

I may just have to upgrade to 2003.

 

[barny2006]

you can also try END_DT - START_DT
(WITH A MINUS SIGN)
this should work in any access platform

 

[rjoubert]

barny...

That would give the difference in DAYS, he's looking for the difference in WEEKS.

 

[barny2006]

can he divide it by 7?
like:
result = INT((END_DT - START_DT) / 7)

 

[rjoubert]

That would work too.

 

[ad2]

Thanks to you all, I really think I must upgrade my version to 2003, I've tried all suggestion, as well as my own veriations and none have worked in 2000. So I think it is a local problem and not with the code.

Thanks again to everyone!!!!!