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

    • ANYFISH2
      7/19 Day 5   Day 5 was spent celebrating my Isaacs 11th birthday!  Lots of cake and catering to him.  The exciting typical swimmimg and paddling ruled the day.  Grandma and grandpa DeZurik came up to celebrate his birthday as well.  Always nice to get both sets of grandparents together at the same time.   Fishing continued slow even more.  Still a successful night though. The birthday boy conceded his seat in the boat so grandma and grandpa cpuld join me for the evening fish trip.  My folks in all truth haven't been fishing for 2 years or more.  It was just nice to get them in the boat.   I struggled as the acting to put them on a consistent bite.  Mom as able to scratch out the biggest walleye at 17".  Dad brought home the honors of most fish caught landing a 14" and 15" walleye, 8.5" bluegill, and 2 perch as big aa his leech! I mustered up 1 small pumpkinseed.
    • ANYFISH2
      no doubt aboit the hooks, they are lottle stickers.
    • DLD24
      I like drifting with them and snap jigging them with a controlled fall...Almost every time you'll feel that tick just as it's hitting bottom... Last time I was on mille lacs that's all I could get them to go on. As far as colors I'd just match the forage Tullies in the lake use blue,purples,silvers....Perch use perch colors.. I think the jiggin rap is my new favorite way to fish, but it gets scary with them little hooks when you got a big eye on lol.
    • DLD24
      Fished Big Sandy from 8-2 today and got 10 eyes (no keepers) keeper crappie and perch.... Marked tons of fish, but it was tough to get them to go, Lindy rig with a half crawler was the best by far. I tried leeches,jigging rap, jig n plastic. Points and reefs were the spots, later in the day a lot of fish were off the edges of the reefs... Just one day this year I'd like a happy medium weather wise, either I'm in 4ft rollers or 90 degrees with zero wind haha.
    • Garmandu
      According to Al Linder you can do it all with them...on his video that I watched a while back he was in deeper water throwing into 15 feet and working it back to the boat.  I have not tried it yet but will have to sometime this year.  Sand or gravel bottom would be the best.
    • ANYFISH2
      Just started playing with these this week as a friend has been have goos luck all summer with them on the Cass lake chain. I have not any success yet but not real sure on the best way to use them with my set this week.  My friend searches pods of fish out with electronics then spot locks and vertical jigs. I have no electronics or spot lock so I have been control drifting and jigging.   My questions...   Is there a depth they work better in, shallow vs. deeper?   Better vertically jigged vs trolled vs casted and jigged?   prime colors? of course my be lake dependent.   typically, aggressive jigging vs subtle jigging?   Thanks for tips
    • Perchy
      Yes, insured. I will ask the adjuster, thanks.
    • Captain Acorn
      Thanks cliff and lb I have actually had better luck with the puppet minnows from northland but I have mainly jigged them vertically definently is a fun way to fish 
    • LBerquist
      I've been trolling at about .5mph while using a jigging rap. This way one guy can still drag a lindy. I keep the front hook intact but my boat has contributed about half a dozen to the lake so far this summer. Im still working on getting the hang of it. If I know I'm in a rough area I will attempt to keep the jig from hitting bottom which still seems to be effective. I did pick up a couple off brands that don't have a front hook that I want to try. This is just what I have been toying around with, I'm definitely not an expert at it.
    • fisherjmb
      Hi Everyone, a couple of questions, I know there is free public boat ramp in Stillwater just above the lift bridge. Is there another public boat ramp further down river? I thought I read somewhere that Beanies is or was becoming a free public boat launch. Is that the case? Also, I am thinking of heading there on Monday to try my luck. Any tactics/depths/areas that have been producing for anyone?