gbnorton
asked on
access form date format error
I have a text box in a form. On GotFocus this code is executed:
Private Sub txtThk_Res_Smpl_Lot_Measur e_DateTime _In_GotFoc us()
If IsNull(txtThk_Res_Smpl_Lot _Measure_D ateTime_In ) Then
Me.txtThk_Res_Smpl_Lot_Mea sure_DateT ime_In = Format(Now(),"mm/dd/yy"", ""hh:nn ampm")
End If
End Sub
but get the error "The value you entered isn't valid for this field".
Runtime error -2147352567 (800200009)
The field type is Date/Time.
Thanks,
Brooks
Private Sub txtThk_Res_Smpl_Lot_Measur
If IsNull(txtThk_Res_Smpl_Lot
Me.txtThk_Res_Smpl_Lot_Mea
End If
End Sub
but get the error "The value you entered isn't valid for this field".
Runtime error -2147352567 (800200009)
The field type is Date/Time.
Thanks,
Brooks
<No points wanted>
Or:
"mm/dd/yy hh:nn AM/PM"
Or:
"mm/dd/yy hh:nn AM/PM"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
cactus data,
Can you please explain the difference between using "Me." and "Me!" ?
DatabaseMX, boag2000,
The error went away after using your code. Still did not get the results i wanted. I would like the date to appear 9/8/11 7:20 AM. I am getting the full date/time 9/8/2011 7:20:12 AM
Thanks,
Brooks
Can you please explain the difference between using "Me." and "Me!" ?
DatabaseMX, boag2000,
The error went away after using your code. Still did not get the results i wanted. I would like the date to appear 9/8/11 7:20 AM. I am getting the full date/time 9/8/2011 7:20:12 AM
Thanks,
Brooks
> Can you please explain the difference between using "Me." and "Me!" ?
There may not be one in real life, and this is a lengthy discussion, but . is for properties while ! is specifically for fields.
> DatabaseMX, boag2000,
> The error went away after using your code.
But that is not how to do it. You first have Now which is of data type DateTime, then convert it to a string expression for a date, then Access will have to cast it back to data type DateTime before assigning it as Value for the field.
> I would like the date to appear 9/8/11 7:20 AM.
Then, as I wrote, apply the format to the Format property of the textbox:
m/d/yy h:nn AM/PM
Note please the it should be AM/PM not ampm.
/gustav
There may not be one in real life, and this is a lengthy discussion, but . is for properties while ! is specifically for fields.
> DatabaseMX, boag2000,
> The error went away after using your code.
But that is not how to do it. You first have Now which is of data type DateTime, then convert it to a string expression for a date, then Access will have to cast it back to data type DateTime before assigning it as Value for the field.
> I would like the date to appear 9/8/11 7:20 AM.
Then, as I wrote, apply the format to the Format property of the textbox:
m/d/yy h:nn AM/PM
Note please the it should be AM/PM not ampm.
/gustav
Then try something like this:
"m/d/yy h:nn AM/PM"
You can see the help files on "date format" for more info on date formats than you could ever dream of.
...But remember here the issue that surrounding the year 2000...
If you do this: 2/15/27
...is this 1927 or 2027
What about this: 2/15/20?
What is the cut off date for two digit years in your environments?
In MS it is 1929-1930, in some other applications it is 1950.
So why not move up to the more modern 4 digit year and avoid running the chance of an embarrassing miscalculation, just because you used an outdated date format.
In other words, the two digit year syntax is being phased out in most programs in favor of the less ambiguous 4 digit year.
Up to you as the developer...
On Me. vs Me!...
There are many technical explanations for this.
But the one I like is from GRayL
".", Microsoft made it
"!", You made it.
Most developers will use "." because it will autolist the members for you.
;-)
Jeff
"m/d/yy h:nn AM/PM"
You can see the help files on "date format" for more info on date formats than you could ever dream of.
...But remember here the issue that surrounding the year 2000...
If you do this: 2/15/27
...is this 1927 or 2027
What about this: 2/15/20?
What is the cut off date for two digit years in your environments?
In MS it is 1929-1930, in some other applications it is 1950.
So why not move up to the more modern 4 digit year and avoid running the chance of an embarrassing miscalculation, just because you used an outdated date format.
In other words, the two digit year syntax is being phased out in most programs in favor of the less ambiguous 4 digit year.
Up to you as the developer...
On Me. vs Me!...
There are many technical explanations for this.
But the one I like is from GRayL
".", Microsoft made it
"!", You made it.
Most developers will use "." because it will autolist the members for you.
;-)
Jeff
as a final, unrelated note...
You might want to reconsider your naming convention:
txtThk_Res_Smpl_Lot_Measur e_DateTime _In
...in addition to having to worry about spelling, Long names may cause issue because in certain situations there is a limit on how long an expression can be...
You might want to reconsider your naming convention:
txtThk_Res_Smpl_Lot_Measur
...in addition to having to worry about spelling, Long names may cause issue because in certain situations there is a limit on how long an expression can be...
ASKER
Thanks for your help!
You are welcome!
/gustav
/gustav
ASKER
Jeff,
I agree on about the 4 digit year, but... in this particular case we have a very busy form that is causing us to compact in every way we can...
Thanks for your suggestions.
Brooks
I agree on about the 4 digit year, but... in this particular case we have a very busy form that is causing us to compact in every way we can...
Thanks for your suggestions.
Brooks
Me.txtThk_Res_Smpl_Lot_Mea