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

    • 12345
      Im staying at Arrowwood July 2-5 and looking to catch some 'eyes for a fish fry or 2. Never been to the area before so I dont have much to go by other than my overall knowledge of Walleye fishing elsewhere. Ill probably be fishing early mornings and dusk for obvious reasons, but also because my boat will be used for the majority of the days for entertaining the girlfriends family. Im open to fishing not only Darling, but Carlos and Le Homme Dieu as well. 

      From what ive read and can see on Google Earth, the lakes are pretty clear, and from experience, usually means tough walleye fishing for this time of year. -I grew up fishing Woman Lake in the Longville/Hackensack area and that lake is as clear as they come. The only way we can snag some 'eyes in the summer is to fish the deep weed lines. Is that the approach I should be taking on these lakes? Does dragging rapalas across mid-lake flats produce? Any help would be greatly appreciated!

      Thanks,

      Gavin.
    • TNtoMN_HuntFish
      Ha, I've read stories about that lake being filthy. And I know about those warnings on the link you posted I just wonder if this stuff is blown out of proportion. Reason being I can't find any confirmed cases of people having mercury poisoning from eating freshwater fish. I know mercury poisoning does exist but can we really get it from fish? You'd think if we could then there would be cases down south where folks are eating catfish from the Mississippi.    We ate a few 30+" Pike during the ice season, I ate more than my wife, and I only did that from lakes that are safer according to DNR and MDH fish tissue surveys. I kept it to once a week for her and I ate the rest. I went over the one meal a week rule several times for myself, but then went a while without taking home fish while the season was closed. I'm not glowing yet haha.    I also don't understand why walleye are always the riskiest fish to eat per those surveys when Pike are above them on the food chain. 
    • h8go4s
      Surely they have a Terrova on the floor. Doesn't matter what size THEY ALL LOOK THE SAME. Also, boat dealers have showrooms with all kinds of stuff mounted on boats.
    • BringAnExtension
    • monstermoose78
      Stopped in this morning and it looks like it should be good to go. 
    • ZachD
      http://www.health.state.mn.us/divs/eh/fish/eating/kidmom/index.html
    • ZachD
      The Rum has been dropping in flow and water level since the 20th from St Francis down if that helps    
    • ZachD
    • CaptJohnWis
      Fishing awful this morning. Launched at 8:30. By 12:30 had exactly two small pike. Finally found some walleyes and ended up with 22 fish - NO BASS! bummer. Water cold - only 61-64 degrees. Weather man blew it again. Walleyes in about 20 FOW, best lure Lunker City Fin-S-Fish.   New camera worked well but had troubles with new sonar/GPS. Think I got it fixed once home.   Here is today's video: June 26 video  
    • Gerkmaster
      We took our annual trip to Rainy Lake in mid-June staying at Taylor's Cove along Bear's Pass.  My dad started going there back in the late 70's and now brings me and my son.  Every year provides great memories, new and old.  Anyhow, fishing was very good, particularly for bass and walleye. Water temps ranged from 62-72 degrees.  Most days were about 75 degrees, overcast and breezy.  Our biggest smallmouth was 22" and biggest walleye was 30".  In fact we boated ten walleye 25" or larger.  We caught pike up to 37".  We found walleyes in weedy bays around 6-10' deep.  Bass were in 5-8'.  Most of the fish were spitting up perch so I imagine that's what kept them shallow.  A few pics to share.  Wish I could be there more often...