Date Calculation on Form -- Please Help

[joe789]

Greetings,

I have created a front-end access form that pulls data from SQL Server. The form is working OK and users can add, modify, and delete data. However, one field on the SQL Server end should be calculated as a date. It is currently in the format of MMDDYY. I am trying to create a textbox calculation that subtracts the current date from the SQL Server date being pulled into the form. It does not work and returns an error in the textbox: "#NAME". I have tried everything from making sure proper references are checked to changing the format of the date. Nothing seems to work. I have ever tried performing a similar calculation on a stand-alone form and it works just fine. It is only when pulling SQL Server data that it does not work?? I think the problem may be the fact that DATE() returns the date in MM/DD/YY format, and the SQL Server field is in MMDDYY format (as text). I am not allowed to go into the SQL Server side and change this field to a date field; but I have tried to change the format on the Access side. Is there anything that can be done?? I am currently using the calculation of =(Date()-[SDate]) in the textbox and it just renders an error #NAME. However, the same formula works on the form if no SQL Server data is introduced. I am thinking of a way to force the format of the SQL Server date from text of MMDDYY to a date format of MM/DD/YY. But I tried everything and nothing will format the field to the date format (I can only format the field from the Access side). Thank you for helping me!!!

JM

 

[Abby N]

Hi Joe. I'm also guessing that it's not recognizing MMDDYY as a date. If that's the case then this should do it.

=Date()-CDate(Left([SDate],2) & "/" & Mid([SDate],3,2) & "/" & Right([SDate],2))

Hope this helps.
~Abby

[This message has been edited by Abby N (edited 04-25-2001).]

 

[joe789]

Thank you Abby!! I have successfully formated the text field to a text field that looks like MM/DD/YY. Much better. But the equation still does not work?

Thanks to your help, I did manage to format the text formatted date field into MM/DD/YY format by using:

=SDate(Left([SDate],2) & "/" & Mid([SDate],3,2) & "/" & Right([SDate],2))

But I still cannot perform the needed calculation. What can I do to FORCE this text date into a real date format on the Access End to perform this calculation without changing the SQL Server fields? Does some function exists that can change text into date?

Thank you very much,

Joe

 

[Abby N]

Hi Joe. The command to convert text to date is CDate([Text_Field]). If you change the 'SDate' at the beginning of your formula to 'CDate', the result should be a date data type.

=CDate(Left([SDate],2) & "/" & Mid([SDate],3,2) & "/" & Right([SDate],2))

Good luck.
~Abby

 

[AccessUser]

Is the name of your textbox is SDate? If that is the case, change it to something like txtSdate. If you use an expression in the control source and have the textbox named the same as a field in the expression, Access gets confused.