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  
Whoaru99

MS Access / Excel experts?

Recommended Posts

Whoaru99

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
huntingislife

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
Whoaru99

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
huntingislife

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
wayne123

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  



  • Posts

    • leech~~
      I remember that video, pretty darn cool! 
    • leech~~
      Man you can really see the standards of this site of have dropped.  A few years ago we were talking over $80-$100 Thorne Brothers Custom made Ice Rods.  Now a $9.99 dollar rod is the talk for the forum!  We need to bring back the Rich folks!
    • slammer
      I think we should start a Go fund me for Cropduster to take his plane up and give us some pics. lol  He did it a few years ago but not lately.  Its pretty cool veiws from above when ice is forming.  I wonder if he is still lurking around here?
    • EyeChsr
      was out tonight, 6-8 in out from the dnr landing. wheelers and a couple perms out. still open water out in front of Peters resort on the south shore. It starts out a couple hundred yards from shore and runs to the north- northwest for a long ways. some of the spots that were open earlier this week have skimmed, be careful out there if you see different looking ice stop and check!!
    • JIvers
      Thanks for the report--about what I figured, which is why I stayed down here in Iowa this weekend. I am fairly optimistic about getting on the ice in southeast or south central Minnesota next weekend; whether I am on a lake around Faribault or Waseca, or one of my old backwater haunts on the Mississippi remains to be seen. Maybe Clear Lake, Iowa. Whatever I do I will post my results.
    • JerkinLips
      9-11am was best time both days.
    • littlebear
      If you are willing to go a little further into Canada, I would recommend Hollinshead Resort, a few hours north of Thunder Bay.  The resort is the only one on the lake, and in June offers excellent Walleye Fishing, as well as some dandy Northern Pike.  It's somewhere you can drive to as well. 
    • monstermoose78
      Caught 20-23 gills and 6 crappies. Fun day on the 10 inches of ice. The whole lake was close to the same where I cut all the holes out and around
    • Wanderer
      I’m still seeing very random thicknesses all over.  In the past few days I’ve been from St. Cloud to Winnie and have seen vehicles on the ice and open water on the same lake. I’m in Little Falls.  Tonight I checked 2 lakes NW of town.  Both had people fishing.  One looked to be safe for wheeler travel and the other, very near it, still had an open lead and you could see the difference between the first sheet of ice and the second. I hope it doesn’t get covered with snow anytime soon so we can still tell where the changes are. Be safe. Bring floatation and picks.  And don’t drive yet! Ruh ro....
    • Bigfatbert
        Nice fish , great pics ,, what times of the day were best for you ?