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

    • leech~~

      Posted

      2 hours ago, JBMasterAngler said:

      Drove all the way up to grindstone this morning from Forest Lake. As I'm driving along the shoreline, I can't stop admiring how beautiful of a day it is, and how great it will be to be on the water. Was totally amped up to fish for some trout! Then I pull up to the boat launch...and it's closed down! :mad: This dredging project has been pushed back for years, then they finally decide to do it on a beautiful sunny day, that I picked to come here...on the last day of the lake trout season nonetheless! Needless to say, I was very upset! 

      Could have throw a line off the south shore since you were all the way up there. :(

    • easyrider25

      Posted

      Hey guys thanks for the tips.  What you are saying really makes sense.  I have been working on these items this week, not expecting a completely changed dog out there but looking forward to continued exercises this weekend out in the blind.

    • JBMasterAngler

      Posted

      Drove all the way up to grindstone this morning from Forest Lake. As I'm driving along the shoreline, I can't stop admiring how beautiful of a day it is, and how great it will be to be on the water. Was totally amped up to fish for some trout! Then I pull up to the boat launch...and it's closed down! :mad: This dredging project has been pushed back for years, then they finally decide to do it on a beautiful sunny day, that I picked to come here...on the last day of the lake trout season nonetheless! Needless to say, I was very upset! 

    • papadarv

      Posted

      1 hour ago, delcecchi said:

      I bet the state, not the political cronies, would get a lot more money by having the auction on the internet or allowing phone bids.  Heck, put them on the bay of E. 

      Actually DMV and DNR do quite well at their auctions. You win bid, You Pay, You own. No red tape, no collection process, simple and things seem to sell for quite a bit of money. Go and one see for yourself, its quite amazing. Lots of them at the Arden Hills DMV. Hard to collect money over the phone and on the internet. I do believe they will use the same auction process for this property sale as the DMV and DNR are good at it and a lot of buyers will be there. If the internet were to be used, the "political cronies" would need to create a bill, Ear Mark several million to develop a site, create an app, hire 40 to 50 people to manage and than increase taxes to cover the cost much like Obama Care Not a good plan. If your interested, go to auction, bring lots of money, win a bid and own.

      2016 MN auction schedule if your interested in going to one.

      http://mn.gov/admin/citizen/surplus-property/auctions/schedule/

    • SpecialK

      Posted

      They were dredging there today. Not sure if they are done or if it will take longer.

    • the weather has not been good this past week either, but fishing was still do able. Pike island is a popular spot and I am sure you can find fish there. Gold is. would be worth a try as would the area around the wolf pack. We had 4 nice walleye today and a great fish fry tonight. Heading home in the morning for the season. so good luck

    • I bet we will end up going to red trying to convince the couple guys that dont like sleepers for some reason that a 10x24 sleeper is just like a cabin and that we can bring 2 portables to bounce around if we want. Dont really need a guide ill probably allready have gone there 3-4 times before this trip.... Maybe superior will freeze good this year and I can convince them to go there i guess I just get kinda bored or red sometimes

    • delcecchi

      Posted

      I bet the state, not the political cronies, would get a lot more money by having the auction on the internet or allowing phone bids.  Heck, put them on the bay of E. 

    • delcecchi

      Posted

      On 9/13/2016 at 4:37 PM, loadmaster said:

      Well, got out of the hospital and rehab a couple of weeks ago, Its been a long haul went in on Feb 15, with some massive heart attacks.   Starting to feel better and have hit Fish a couple of times.  Thought I would check out the site and see if traffic has picked up,  Sorry to say not much different, a lot of lookers as usual and not a lot of posters

      Been there and done that.... Not the site but the hospital etc

       

    • certified jumbo

      Posted

      In northwest Wisconsin 26 hunting dogs have already been killed this late summer.   Already breaking the record of 23.  Be careful in the northland.



  • Posts

    • leech~~
      Could have throw a line off the south shore since you were all the way up there.
    • easyrider25
      Hey guys thanks for the tips.  What you are saying really makes sense.  I have been working on these items this week, not expecting a completely changed dog out there but looking forward to continued exercises this weekend out in the blind.
    • JBMasterAngler
      Drove all the way up to grindstone this morning from Forest Lake. As I'm driving along the shoreline, I can't stop admiring how beautiful of a day it is, and how great it will be to be on the water. Was totally amped up to fish for some trout! Then I pull up to the boat launch...and it's closed down!  This dredging project has been pushed back for years, then they finally decide to do it on a beautiful sunny day, that I picked to come here...on the last day of the lake trout season nonetheless! Needless to say, I was very upset! 
    • papadarv
      Actually DMV and DNR do quite well at their auctions. You win bid, You Pay, You own. No red tape, no collection process, simple and things seem to sell for quite a bit of money. Go and one see for yourself, its quite amazing. Lots of them at the Arden Hills DMV. Hard to collect money over the phone and on the internet. I do believe they will use the same auction process for this property sale as the DMV and DNR are good at it and a lot of buyers will be there. If the internet were to be used, the "political cronies" would need to create a bill, Ear Mark several million to develop a site, create an app, hire 40 to 50 people to manage and than increase taxes to cover the cost much like Obama Care Not a good plan. If your interested, go to auction, bring lots of money, win a bid and own. 2016 MN auction schedule if your interested in going to one. http://mn.gov/admin/citizen/surplus-property/auctions/schedule/
    • SpecialK
      They were dredging there today. Not sure if they are done or if it will take longer.