linked excel table long date to short date conversion (1 Viewer)

[rrohde1]

Thanks for your help in advance. I'm pretty new to Access. I have used it to collect data, move it around, and disply it with forms. But, have new projects to try to get it to "do" stuff. More used to excel for formulas

I have a Access 2010 DB that I am building to run a whole bunch of daily reports. Data is coming out of a different program in excel format. To make it simple for anyone to update the data throughout the day, I created it as a linked table. But, the formatting of the other program export is not good (currencies as numbers, dates as texts, etc)

So, I used a create table querie with all of the fields and not filtering to make a 'working table' and put proper formatting in.

But, the current problem is that one date column imports as a long date xx/xx/xxxx x:xx:xx am. I need to create crosstab and or pivot table queries from this data that sorts "by month." And that doesn't seems to be working with the long date format. Just changing format and input mask doesn't seem to be converting it.

What is the easiest way to convert this? Like I said, I'm fairly new to this. So, if you tell me to use a ChangesomeValue([neverheardof],dontknow) please also tell me where I'm supposed to put it in. Keep running to that problem on on these.

THANK YOU.

 

[spikepl]

If your field in the table is of type Date/Time then it stores a date. 1/1/2012 11:00 and Jan 1, 2012 11:00 AM, are both the same datetimes, just formatted differently. Do not confuse value with format. Format is just how the thing is displayed.

To get the number of the month out of a date, you can call the function Month(myDate).

To get the name of the month, which is of type string and not of type date, you can say Format("mmm", myDate) . This does not convert the value of the date held in the field, but produces a string that contains the name of the month. You can add a column in the query called MyMonth: Format("mmm", myDate), but sort on Month(myDate) to get a sort according to the numerical value of a month, and not alphabetical, because the latter would mess up the sort.

 

[rrohde1]

Ok, thank you. I will give it a try. It just wasn't letting do a "by month" sort of the long datetime format on pivot tables and crosstabs. So, needed it in xx/xx/xxxx format. I just put that in the field row of the query?