Solved

access form date format error

Posted on 2011-09-08
10
317 Views
Last Modified: 2012-05-12
I have a text box in a form.  On GotFocus this code is executed:
Private Sub txtThk_Res_Smpl_Lot_Measure_DateTime_In_GotFocus()
    If IsNull(txtThk_Res_Smpl_Lot_Measure_DateTime_In) Then
        Me.txtThk_Res_Smpl_Lot_Measure_DateTime_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
0
Comment
Question by:gbnorton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 75
ID: 36506155
Try this:

Me.txtThk_Res_Smpl_Lot_Measure_DateTime_In = Format(Now(),"mm/dd/yy hh:nn ampm")    
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36507417
<No points wanted>

Or:
"mm/dd/yy hh:nn AM/PM"
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 36509508
Format returns a string to your date field. Thus:

Private Sub txtThk_Res_Smpl_Lot_Measure_DateTime_In_GotFocus()
    If IsNull(Me!txtThk_Res_Smpl_Lot_Measure_DateTime_In) Then
        Me!txtThk_Res_Smpl_Lot_Measure_DateTime_In = Now
    End If
End Sub

Apply the format you wish to the Format property of Me!txtThk_Res_Smpl_Lot_Measure_DateTime_In

/gustav
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:gbnorton
ID: 36510438
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
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 36510621
> 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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36510749
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36510809
as a final, unrelated note...

You might want to reconsider your naming convention:
    txtThk_Res_Smpl_Lot_Measure_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...

0
 

Author Closing Comment

by:gbnorton
ID: 36510978
Thanks for your help!
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 36511094
You are welcome!

/gustav
0
 

Author Comment

by:gbnorton
ID: 36511134
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
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

726 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