Access Converts Date to String....Why?

[rgutshall]

Though I am not new to access....I'm stumped by this. Using access 2007 I have a form based on a table that has 3 Date fields. While in the form and filling it out, if I put the date 1/1/2013 in and tab to the next field the form converts the date to a number string. Why?? and how can I tell it NOT to do that?

The settings on the date field are as follows:

Format = Short Date
Input Mask = 99/99/0000;0;_

The rest of the settings are defaults.



Thanks in advanced.

Rob

[Missinglinq]

How do you enter

1/1/2013

with an Input Mask of

99/99/0000;0;_

Doesn't it require leading zeros, i.e.

01/01/2013?

Is this behavior present in all Date Fields or just in one?

Linq ;0)>

[rgutshall]

Just for kicks I took the input mask out and it still does it. I originally thought it was all 3 date fields but I was wrong....2 of the 3 do the convert. I looked at the field properties for all and they match now. (no input format)

[orange]

Hmm?? So problem solved, not solved?

[Missinglinq]

I expect we're going to have to lay our hands on the app to do any further trouble-shooting. Can you ZIP it up and attach it to a post, here, and identify the errant Form?

Linq ;0)>

[rgutshall]

Here's the file....please be gentle with my "DB" skills. Just click on the "Incident Entry Form" button. there are 3 date fields, the first 2 are converting the date to a number. The last doesn't.

Thanks

Rob

[Missinglinq]

In both the DOB and the Date of Incident Textboxes, the two you're having this problem with, you have their Formats set to @! I have no idea what you were trying to do, because I can find no reference to this character being used for anything, Formatting-wise, except vis-à-vis creating a shortcut in a Command Button's caption.

Simply changing the Format to 'Short Date,' or another date-specific Format, resolves the problem.

Also note that your Incident Time and Date of Incident Fields are name 'Time' and 'Date,' both of which are Reserved Words, in Access, and both of which, if you keep these names, are going to jump up and bite you in the rump, sooner or later! Change these Fields to something like IncidentTime and IncidentDate; it'll not only prevent problems, down the line, but it'll make it easier to understand which Time/Date you're looking at!

Linq ;0)>

[orange]

Or IncidentDateTime and make it General Date and everything is in 1 field

[rgutshall]

In both the DOB and the Date of Incident Textboxes, the two you're having this problem with, you have their Formats set to @! I have no idea what you were trying to do, because I can find no reference to this character being used for anything, Formatting-wise, except vis-à-vis creating a shortcut in a Command Button's caption.

Simply changing the Format to 'Short Date,' or another date-specific Format, resolves the problem.

Also note that your Incident Time and Date of Incident Fields are name 'Time' and 'Date,' both of which are Reserved Words, in Access, and both of which, if you keep these names, are going to jump up and bite you in the rump, sooner or later! Change these Fields to something like IncidentTime and IncidentDate; it'll not only prevent problems, down the line, but it'll make it easier to understand which Time/Date you're looking at!

Linq ;0)>

Linq

That solved the issue and thank you very much for the reccomendations, I'll work on changing what you suggested. Another issue cropped up, in the entry form I have a drop down for School/Building and a dropdown for Location. When I enter a new record the dropdowns show the correct names of buildings. Once I choose a building or location what gets written to the DB is the ID number instead of the actual name of the building. Not sure why....should I edit that table and delete the ID column?

Thanks

Rob

[Missinglinq]

When creating a Combobox, Access, by default, makes the ID number the Bound Field or Column. You simply need to assign the correct Column to the Control in your Form that holds the location Field. Here's some boilerplate code for doing this:

If in the Combobox they appear as

Field1 | Field2 | Field3

the code would be

Private Sub YourComboBox_AfterUpdate()
   Me.txtField1 = Me.YourComboBox.Column(0)
   Me.txtField2 = Me.YourComboBox.Column(1)
   Me.txtField3 = Me.YourComboBox.Column(2)
End Sub

Notice that the column index is Zero-based.

And in the future, please start a separate thread when posting a new, unrelated topic, giving it an appropriate title. Doing so

• Allows other members, who are currently in the forum and who may have the answer you need, to see your question.
• Allows members who have a similar problem to find the thread, when using the 'search' feature, and possibly benefit from it.

Linq ;0)>

[rgutshall]

Linq

That did the trick......Thanks for all your help.

Rob

[Missinglinq]

Glad we could help!

Good luck with your project!

Linq ;0)>