Access Front-end / SQL Server Back-end Date Format issues...

Hi Experts!

I have an issue that I can't seem to resolve. I changed a data type on the SQL side from date to smalldatetime - in the hope that I could fix formatting issues on the Access side --- dates display as YYYY-MM-DD and they want to see MM-DD-YYYY.

Well nothing I did seemed to work. One recommendation was to open the table on the Access side in Design mode (despite the fact that no changes can be made on linked tables) and the date field shows it as a TEXT field. What the heck am I doing wrong.

I verified that all other tables with smalldatetime formatting show as Dates on the Access side.

Thanks a bunch in advance.

Eileen
Eileen MurphyIndependent Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
If you use Format(MyDate, "MM-DD-YYYY") in your front end (in a query report, or form) what difference does it makes how does it displayed in SQL or linked tables?

At database level, dates are stored independent of format as numbers. see http://blogs.lessthandot.com/index.php/datamgmt/datadesign/how-are-dates-stored-in-sql-server/
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Stick to Datetime in SQL and control the output with a Format() call like Mike said.

Smalldatetime can give you issues if you use it.

Jim.
1
Eileen MurphyIndependent Application DeveloperAuthor Commented:
But --- I tried all the formatting on the form in the front end -- am accustomed to doing that -- but WHY or WHY does the format show as text on the Access side? I think this is driving the fact that Access is ignoring all my formatting settings.

All other tables with smalldatetimes on SQL show as dates on the Access side -- this table doesn't though --- it shows as text??
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mike EghtebasDatabase and Application DeveloperCommented:
try:

Format(CvDate(MyDate), "MM-DD-YYYY")

SQL Sever outputs it as text.

Mike
0
Eileen MurphyIndependent Application DeveloperAuthor Commented:
New records formatted correctly -- prior records are not.


Screen
0
Mike EghtebasDatabase and Application DeveloperCommented:
Check the format property of the text box, it maybe overwriting it.

If that didn't work then have:

txtDate1 bound to your datafield. Make this text box invisible.

have another text box txtDate2 with control source property set to
=Format(txtDate1,"MM-DD-YYYY")
0
Eileen MurphyIndependent Application DeveloperAuthor Commented:
This is a bound/editable field. I just want to understand why -- why is this one table showing this date field as a text field from the Access side when I open the table in un-editable/design view -- when none of the other tables do.

I may create a new field and populate it with the data -- I have the weekend to screw around with this, but it's beyond frustrating to me. I hate when I can't figure something out :)
0
Mike EghtebasDatabase and Application DeveloperCommented:
Putting (in an unbound) textbox: txtDate2

=Format(txtDate1,"MM-DD-YYYY")

or even, if necessary:

=Format(cvdate(txtDate1),"MM-DD-YYYY")

where txtDate1 is bound to your date field surely would work.

After it works, then we will be in a better position to figure out why.

Have a nice weekend.

Mike
0
Chris BRetiredCommented:
Access only understands datetime, not SQL Date or Time. Anything not understood will be linked as text. As it is text you will need to convert it to date type before format will work correctly - datevalue should work fine.

Chris B
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eileen MurphyIndependent Application DeveloperAuthor Commented:
I appreciate all the direction. I am still unclear why all their other tables with smalldatetime fields are formatted correctly on the Access side of things except this one table which treated it as text. I changed it to a datetime field and it is formatting correctly now.

Thanks everyone!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.