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

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?
cstraimAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stevbeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cstraimAuthor Commented:
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
Rory ArchibaldCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

cstraimAuthor Commented:
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
cstraimAuthor Commented:
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
stevbeCommented:
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
Rory ArchibaldCommented:
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
cstraimAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.