Guests - If You want access to member only forums on FM. You will gain access only when you Sign-in or Sign-Up on Fishing Minnesota.

It's easy - LOOK UPPER right menu.

Sign in to follow this  
Followers 0
Whoaru99

MS Access / Excel experts?

5 posts in this topic

Normally I can figure out most of the stuff I need to do, but in this case I'm stumped.

I have several linked MS Access tables that I query and then export to Excel. Several of the columns that are exported are dates. In Excel I need to change the date format from mm/dd/yyyy to dd-mm-yyyy.

The problem I'm having is that for three of the four date columns, I can merely change the column/cell format and the date formats change, but on the fourth column, the only way I can get the date to change format is to change the column/cell format THEN click into a cell, hit "enter", and only then the date format changes.

What am I overlooking??

Share this post


Link to post
Share on other sites

Hmmm, I am not quite sure. It appears it should just work like the other 3 columns. Is the data highlighted when you change the format?

Did you try this? : Get the first cell in the correct format and then click and hold down the lower right corner of the first cell(when you get the black + sign) and just drag down to the bottom of your data. That should change the format of the other cells to the format of the first cell.

Good Luck

Share this post


Link to post
Share on other sites

Yeah, I just select/highlight the whole date columns and then change the cell formatting. Three of the four change no problem. Nothing seems to change the format of the dates in the fourth column until I select the cell, click the cursor into the cell, and then hit enter to exit the cell. As soon as I exit the cell using the enter key, the format changes.

Also, if it change the date value in any of cells that would not reformat, the new entered value takes on the desired format.

It's like somehow the format is locked on the data in that column.

I've tried changing to date serial number/value and back again, nothing seems to work...

Don't recall if I've tried to drag the format, but I'll give that a shot Monday morning.

Share this post


Link to post
Share on other sites

Another thing you could try is to insert a new blank column next to the 4th column and then copy and paste the 4th column into it. But instead of just pasting it, select "paste special" and then "values". This will get rid of anything that the format might be referencing. Then you could format the new column and delete the old one.

Share this post


Link to post
Share on other sites

I would compare the formats of the columns in Access and see if there is any difference. I am assuming that you are importing into a blank Excel spreadsheet if not check the format of the columns before you import the data. Also try the format painter. It is the icon with the paint brush.

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.
Sign in to follow this  
Followers 0



  • Posts

    • Jim Uran
      What's your fish of choice to pursue? A weekday fish you could find after work are crappies, the basin bite on your local lake/lakes could make for a fun night bite. Panfish in general are fun to chase anytime, northerns are fun to target too but that's more of a weekend thing until we start gaining more daylight. Walleyes are punks to me and I've never been patient enough to target them unless I'm on LOW or Upper Red lol. 
    • monstermoose78
      Wow nice fish
    • MidCoast
      How do you even get on this lake to ice fish?  Seems to be surrounded by Private land. 
    • ZachD
      Well Friday was a little slow picked up some nice gills here and there no crappies. Sat morning picked up a couple 10 inch gills and a couple more for the bucket here and there. Sat night 930 the crappies came through I caught 17 biggest was 12. Sunday picked a few more gills up for the bucket before heading off to a different lake for walleye. again fish wanted nothing to do with minnows only caught one with them. All other fish caught on a tungsten orange and green glow tipped with only a wax worm.   we were in 12 fow the sunfish were 2 off the bottom crappie cruising 4 off bottom Side note tried walleye tonight missed 3 of them! Now I remember why I don't walleye fish much stupid things
    • Big-B
      I had the showdown as a hole hopping sonar but found I used my LX5 a lot more.  I went to the 7 and did not like it because it was bulky and hard to get used to both hopping holes and room inside one man Frabile.  Sold the 7 and bought another LX5 and do not see me going back to any other.  I have a wheel house and found is the only time I used the 7.  If you do a lot of fishing outside and like to move around.  I would stick with the 5, I now own two of them and don't see me using anything else.