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 MurphyIndependent Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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
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.

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??
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Mike EghtebasDatabase and Application DeveloperCommented:

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

SQL Sever outputs it as text.

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

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
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 :)
Mike EghtebasDatabase and Application DeveloperCommented:
Putting (in an unbound) textbox: txtDate2


or even, if necessary:


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.

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

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!!!
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.