[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Date field on a form

Posted on 2012-09-17
12
Medium Priority
?
377 Views
Last Modified: 2012-10-04
I am experimenting with converting an Access B/E file to a SQL server B/E.  In my F/E Access database I have a form with a calculated date field.  After converting the B/E to SQL the date shows up as a bunch of #################'s probably because the filed isn't wide enough on the form.  But all I really want is to have the form display the Month, Day, Year like "09/17/2012" for example.  How can I make this happen?

--Steve
0
Comment
Question by:SteveL13
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38405542
In the Format property of the textbox, try adding mm/dd/yy
0
 

Author Comment

by:SteveL13
ID: 38405552
That did not work.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38405561
Can you widen the textbox and see what it actually shows?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 3

Expert Comment

by:Oliver Wastell
ID: 38405596
On your form in the Control Source property for your textbox use:
=Format(myDateField, "mm/dd/yyyy")
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38405650
I don't work with Access much, but

convert(varchar, <datefield>,101)

will display the date as mm/dd/yyyy in SQL.
0
 
LVL 3

Expert Comment

by:Oliver Wastell
ID: 38405687
I have found that in Access, dates on forms are rendered in the date format set under the Windows Region and Language settings.  This is very annoying when space on a form is limited and the date field is just big enough for a 6 digit date format, but the host computer's date format (under Reigon and Language settings) is set to 8 digits, which results in a field full of #######s.  I therefore use Format(myDateField, "mm/dd/yy") to force 6 digits no matter what the settings of the host computer.
0
 

Author Comment

by:SteveL13
ID: 38405694
If I widen the text box I see...   2012-08-02 00:00:00

and

=Format(myDateField, "mm/dd/yyyy")  did not work
0
 
LVL 3

Expert Comment

by:Oliver Wastell
ID: 38405706
What is the name of the field?  If the field name is myDateField it should work, if it's not substitute myDateField for the actual name of the field.
0
 
LVL 3

Expert Comment

by:Oliver Wastell
ID: 38405717
I've just noticed that you are using a calculated date field - substitute myDateField with your calculation.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38405731
Are you storing the dates in SQL server with data type “Date” or “Date2”?  If so, try changing them to “DateTime”
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38405785
Either that or use this in your textbox:
=Format(CDate(NameOfField),"mm/dd/yy")
0
 
LVL 3

Accepted Solution

by:
Oliver Wastell earned 2000 total points
ID: 38405824
If your textbox has the same name as your field you will get an error, so make sure that the textbox is named differently i.e. txbMyDateField if the field is myDateField - watch out though as the formula can change when the field name is changed, check that you still have
=Format([myDateField],"mm/dd/yyyy")
after the textbox name change rather than
=Format([txbMyDateField],"mm/dd/yyyy")
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question