Solved

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

Posted on 2007-03-20
8
390 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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

820 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