DateDiff problem

[darksniper]

I am using Expr1: DateDiff("d";Date();[Expiry Date])

I am having a problem , the date diff uses the date in this format " mm,dd,yyyy"

but in my program I enter the date dd,mm,yyyy, is there is way to change it, I know that in sql you can do it, ms access is abit different, any solutions to that.

 

[EMP]

It sounds like [Expiry Date] is a text field, not a date/time field.

You can try using the Mid(), Left() and Right() functions to convert dd,mm,yyyy to mm,dd,yyyy.

DateDiff("d", Date(), Mid([Expiry Date],4,3) & Left([Expiry Date],3) & Right([Expiry Date],4)

^

 

[Rich]

change your Regional Settings in Windows

 

[darksniper]

the regional setting on my computer are easter canada, and for some reason, it doesnt give me the option to choose day, month, year. only month day year. But at school on when I try to run it and set it up, my short date option is different, it goes by day, month, year, so when ever I bring my program to school it works fine, when ever I bring it home, I get that problem.

I'll try using DateDiff("d", Date(), Mid([Expiry Date],4,3) & Left([Expiry Date],3) & Right([Expiry Date],4) and see if that helps to fix my problem.

 

[namliam]

1) If your Epire date is a real date field you shouldnt have any problems.
2) Access (internaly) allways uses mm/dd/yyyy be aware of that when creating queries and such
3) You should never ever use spaces in column names or anything for that matter. Just use ExpiryDate or Expiry_Date (use the _ if you must..., but NO spaces ) This should have been your very FIRST lesson I allready dislike your teacher...

GL @ School

 

[Rich]

I am using Expr1: DateDiff("d";Date();[Expiry Date])

should be Expr1: DateDiff("d",Date(),[Expiry Date])

 

[EMP]

I'll try using DateDiff("d", Date(), Mid([Expiry Date],4,3) & Left([Expiry Date],3) & Right([Expiry Date],4) and see if that helps to fix my problem.

If your system uses ; as the argument separator rather than the comma, you should try using:-

DateDiff("d"; Date(); Mid([Expiry Date];4;3) & Left([Expiry Date];3) & Right([Expiry Date];4)

^