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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
HTH
Rory
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
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
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.
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
Regards,
Rory
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 !!!
ASKER