Link to home
Start Free TrialLog in
Avatar of cstraim
cstraim

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of stevbe
stevbe

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cstraim
cstraim

ASKER

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.
Avatar of Rory Archibald
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
Avatar of cstraim

ASKER

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
Avatar of cstraim

ASKER

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
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.
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
Avatar of cstraim

ASKER

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 !!!