Solved

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

Posted on 2007-03-20
8
392 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
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.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

635 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