Solved

Date and Time picker supercedes table input mask and format for short date

Posted on 2007-03-20
8
391 Views
Last Modified: 2012-06-27
I am using microsoft date and time picker in my forms for users to populate date fields.  Problem is I forgot to set the format to "short date" in the form.  I did, however, set the input mask on the table to be short date.

Unfortunately, the input mask did not mask out the time portion of the long date.  When the users choose a date from the calendar on the form, it adds the time. now when I try to do reporting, instead of having a 1 short date for each record, i have multiple date and times for the same day.

I'm not sure if I need to do a sweeping change of the format in the date fields, or if I can just adjust my excel pivot table somehow to look at each day's entry as a single date.

For example

3/19/2007 - 1:00
3/19/2007 - 1:02

i need to be able to choose 3/19/2007 in my pivot table to determine which records have that date.  instead it is giving me hundreds of choices for 3/19/2007 with different times throughout the day.

Anyone know how i can fix this?
0
Comment
Question by:cstraim
[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
  • 4
  • 2
  • 2
8 Comments
 
LVL 39

Accepted Solution

by:
stevbe earned 500 total points
ID: 18756586
the mask will not chnage the actual data that is being stored ... time is always stored in date fields but I didn't think the calendar provided a specific time (typically it returns the date  at midnight. You can fix up the display on your reports a couple of ways, If you are grouping on the date then you can chnage the Group On property to be Day or you can strip the time off of the field in the query your report is based on by using the Int function ...

SELECT Int([DateField]) As Mydate

Steve
0
 

Author Comment

by:cstraim
ID: 18756598
To summarize.  The table the data is sitting in has a format of "short date" and an imput mask of mm-dd-yyyy.  The table only shows mm-dd-yyyy but when try to run a pivot table from excel using the data from access, the pivot table will list the mm-dd-yyyy hh:mm format as choices, making it impossible for me to look at a specific days records all at once.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18756625
You could run an update query on your data to set the date field to Int([datefield]) to remove the time part. If your pivot table is based directly on the table, this is probably your best bet; if it's based on a query, you can perform the Int() conversion in the query.
HTH
Rory
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:cstraim
ID: 18756662
Im trying it out now. I noticed that when I did it, it converts it to the date serial number in the query, I have to see how it imports into excel.  I am running the pivot table from a query
0
 

Author Comment

by:cstraim
ID: 18756733
The query in access shows the serial number dates as i mentioned earlier, but when I use the query to build a pivot table in excel, it still lists the date/time format as a choice.  To further explain.  The date field is a "page" choice in the pivot table.  The idea is to pick a particular days data in the report.  

I'm not sure what else to do
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18756750
another functrion that does the same thing as Int but is a real Date manipuklation function is
DateValue([DateField]) As MyDate

You should see if you can figure out how your data is getting a time other than midnight.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18756782
I don't understand - if your query returns the Int([datefield]) value, then there is no time part (times are stored as decimal parts of a day). So either all of them will be dd/mm/yyyy 00:00:00 (i.e. midnight for any given day) in which case you only have to worry about the day portion anyway, or you have the wrong field in the pagefield?
Regards,
Rory
0
 

Author Comment

by:cstraim
ID: 18763563
Using INT in the SQL query completely worked.  The formatting was just removed.  I've been so busy I didnt notice the date, just the numbers which "appeared" to be a serial number.  Everything is working great.  Thanks for your help !!!
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
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…

752 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