General discussion thread for Access Zone (19-Jun-2014)

Alright,

I still don't see the UI to follow this question without posting an inane post.
Or the UI to stop following a question -- not that I ever do that

Nick67

Neither did I. But it's the tiny Monitor icon on the toolbar which moves around the edges.

/gustav

@Nick:


Jim.

@Jim

Can Articles be written jointly?

Only one account can submit and only one will get the points.   The article will only be shown as belonging to that account.  So the answer really is no.

But if you want to write an article with someone else, there's nothing stopping you from doing that.

Jim.

Go to your browser settings and change the font size to medium.  You should then be able to see all the links in the footer, including Monitor/Unmonitor

I ask because I figure that a goto Article about how Access handles dates would be a good  thing, and a good thing for us to be able to point newbies at.  I could write it, but \gustav taught me much of what I know--except for the Hiriji date stuff, which I looked into for an Asker.  To be able to joint author and review before publishing would be good.

@Nick

Stuff like end of month calculation, quarter assignment and grouping, age-at-date calculation, month-of-fiscal-year calculation, crossing midnight for hourly calculation?  Or something different, like what DateDiff() does and does not do, converting to/from Unix dates, grouping by date on a field with time component?

@aikimark

No, the underpinnings.  

 

Those type of things.
Maybe DateAdd, DateDiff, DateSerial, Day, Month, Year, CDate, and other fun things

Nick67

@gustav has said he's never had the time.
Who has interest in this?
And if I wrote it, would it be useful, in terms of pointing Askers to it, to more than me?

I bring a thoroughly English North American slant to the topic, which @gustav and @harfang have pointed out doesn't work well for machines in, say Switzerland, where it's entirely possible to have machines running English, French, German and Italian and therefore my favorite date format of ''dd-mmm-yy" may not hold up well.  So a broader viewpoint that just my own would be useful.

I am thinking of an Article that covers the topic as well as the normalization tutorial at phlonx.com covers that subject.
Thoughts, anyone?

<<And if I wrote it, would it be useful, in terms of pointing Askers to it, to more than me?>>

 I believe that with changes coming in the future, there will be more leverage of articles and videos, so doing these will become more worth doing.

 One of the things we've never done a great job with is finding related content and getting that in front of someone.  In general, it's usually only  the author and one or two others that know about it existing and then remembering to use it when commenting.

  That will change with the changes coming down the pike.

 and BTW, Todd is the one that wants multiple authors....don't think that's something I've ever asked for<g>, but then I'm getting a lot grayer on top ;)

Jim.

@JimD

LOL
So you went ahead and had that Calendar ocx question re-opened, and then the asker still chose one of your responses as the answer.  Oh well!

Very strange
I have some filename records
xxxxxxx_cert.pdf
yyyyyyyy Cert.pdf

A SQL statement with a Where
filename like " & Chr(34) &  "*_Cert*" & Chr(34)
Will snag both records despite the fact one lacks the underscore
It does this regardless of
Option Compare Database, Text or Binary.

What am I missing?

You are probably a victim of 8.3 file names.  I've encountered this with VB Dir() function execution.  Please check the 8.3 name and report your findings.

The table happens to store the filenames, so no 8.3 issues.  The full names are stored  as text and Dir() and FileSystemObject are not involved, just straight tables
The table stores an autonumber primary, a foreign key, the path and the filename of a pdf file
The SQL is supposed to filter by WHERE the Primary key = something and the filename field value like "*_cert*"
Instead the filter is equivalent to WHERE the Primary key = something and the filename field value like "*cert*"

Oddly enough, I make-table exported the table to a new db to post a demo -- and in that one, the sorting works as expected.  So what have I done in the This Database | Options to break the filtering of the underscore?

Oh.  I thought you might have been invoking Dir() from within your query.

the sorting works as expected

I'm a bit confused, Nick.  Are we concerned about the Where clause or the Order By clause behavior?

I did notice that there are a different number of leading characters in your example, 7 "x" and 8 "y".

The where clause.
By rights SELECT * from sometable where somefield like "*_cert*"
should only snag rows where sometable.somefield  has an underscore in it.
In the sample I created, that's so
In my production app, records lacking an underscore are also returned

MS added underscore as a wildcard character in 2010
http://office.microsoft.com/en-us/access-help/using-the-like-operator-and-wildcard-characters-in-string-comparisons-HA010342099.aspx

I think you'll have to do something like this to match the underscore:

Where filename like " & Chr(34) &  "*[_]Cert*" & Chr(34)

Select allOpen in new window

That get's it done!
It's funny though, the app is A2003 and the backend is SSEE 2005
But that get's the correct result, so thanks!

I think if you use ADO, the query engine always plays by the big-boy database syntax conventions.  I've been caught a few times using the asterisk wildcard character after splitting the Access database and placing the BE on a SQL Server.

Another one to try from SQL:

SELECT * from sometable where somefield like "*/_cert*" escape "/"

Select allOpen in new window

Basically tells the escape character is the forward slash. The next character after the / is to be taken litterally and not treated as a wildcard or something special.

Am I the only one receiving weird promotional mails from Jackie Gragnola:

Did you know you can earn a free Premium Service Membership with Experts Exchange? And did you know that this will give you full access to all site features and functionality – worth up to $29.95 per month?
It’s the barter system at its finest:
•      Share your technology expertise by answering member questions, or submitting how-to articles or video tutorials
•      Receive complete access to verified tech solutions, industry-leading educational content, and unlimited professional connections
Sign up today for a Free Membership (no credit card required) and begin earning points to qualify as an “Expert” with unlimited Premium Service access.
Jackie

I think that both you and I are very well aware of this option. Don't EE track the members' records?

/gustav

PS: You can't even "opt out" of these mails without loosing all news mails from EE (Jackie is investigation this issue at the moment).

I would think they would do a better job of that.   I know for sure there is a flag on an account that free premium membership has been earned.  Somebody messed up.

Don't think it will be the last time either :(

Jim.

Has anyone used Access to pull data from AD?

If you know any about it olease jump into this question:
https://www.experts-exchange.com/questions/28462994/VBA-Get-list-of-computers-from-Active-Directory-for-a-particular-OU.html

#a40160112

I found out that somehow my other email address has reached EE and this was used for all the promotional mail.
So I opted out with that address and guess it will stop now.

/gustav

Anybody know how to programmatically collapse all of the groups in the navigation pane?

https://www.experts-exchange.com/questions/28463210/Access-2007-Navigation-Pane-All-Objects-Collapse-ALL.html?anchorAnswerId=40162434#a40162434

I looked at that question, but my primary machine is A2003
Look here
http://social.msdn.microsoft.com/Forums/office/en-US/95fcf5a5-8611-4b92-b82d-bd905d6af64c/vba-code-to-set-focus-to-specific-navigation-pane-group?forum=accessdev

It looks like it would have to sussed out of DoCmd options.
Almost 7 years on and the documentation is still so poor!

It looks like you could do it in Macros, and then set a macro off with VBA code
You may be able to do it all with docmd.NavigateTo and docmd.SetDisplayedCategories

You'd be looking at a NavigateTo(Category, Group) with Group empty and the correct string are constant for All Access Objects
http://msdn.microsoft.com/en-us/library/office/ff191916(v=office.15).aspx

There are DoCmd.RunCommands as well
acCmdViewDiagrams
acCmdViewForms
acCmdViewMacros
acCmdViewModules
acCmdViewQueries
acCmdViewReports
acCmdViewTables

After Jim pointed out that it is available in a popup menu, I was able to use my commandbar tool to find the specific commandbar.  The single line command to collapse all of those is:

commandbars("Navigation Pane Group Header Pop-up").controls("&Collapse All").Execute

It isn't often I have to ask a question, but there's one here
https://www.experts-exchange.com/questions/28468537/Re-creating-the-primary-keys-of-SQL-Server-Linked-Views-using-VBA-code-in-MS-Access.html

It's been fun.
It's not everyday that the primary file/database/remote access server bites the dust.
It's easy enough to relink tables -- but when linked views lose their PK's, that's a pain!

Nick67

Ever wanted to transpose a field of some records into fields of just one row?
I was surprised it is possible with a simple crosstab:

https://www.experts-exchange.com/questions/28467368/How-to-Transpose-2-of-3-columns.html?anchorAnswerId=40172656#a40172656

Then this:
will turn into:
/gustav

@gustav

I don't suppose you have the code for relinking passthroughs handy?

I do. I've posted it here:

https://www.experts-exchange.com/questions/28468537/Re-creating-the-primary-keys-of-SQL-Server-Linked-Views-using-VBA-code-in-MS-Access.html?anchorAnswerId=40176247#a40176247

/gustav

Posted an article showing how to create and implement a relatively simple procedure to allow your users to retain their datasheet column sequencing and sizing (including hidden and frozen columns), even after you deploy a new version.

I thought under the new business model, EE was allowing others to read articles and threads without actually joining.  So I posted a link to the article mentioned above on a couple of Google and LinkedIn groups pointing to the article, only to hear back that they could not get to page 2 of the article (which contained the attached database) without creating a free user account.

This is not the way to get people, who were turned off by EE in the past, to join.  You need to let them find content and have access to the entire article or thread and realize the value associated with EE, not turn them off by providing a teaser and then requiring them to open an account.

Did you use the link button or copy and paste the URL?

I was under the impression that a full article view was possible as well, but you must use the link button to get that.   That may have changed however with all the recent changes.

 Many sites use the model of registering in order to see full content.   The biggest grip with EE was that the "free" account required a CC in the past in order to complete it.   It does no longer.

Jim.

@Dale,

   BTW, my MVP was renewed this year and I am planning on hitting the conference in Nov (finally)

Jim.

@JIm

Link button?

Mine was too.  Don't know whether I'll be able to swing the conference but sure plan on trying.

<<Link button?>>


 I believe that the current setup is that unless you use the share link, you won't have full access unless you come from Google.   Not sure though exactly how much of the pay wall was disabled and their might be additional changes coming to open it even more, so don't let it stop you from posting links.

  Right now, it just means they need to register. ....even www.sqlservercentral.com does that, so I don't think anyone can complain too loudly.   All they want is a name and a e-mail like most of the sites out there.

Jim.

Thanks, Jim.  I see how they have that configured now.  I'll have to take a look and see whether sharing that way makes a difference.

wow ... Jim at the Summit .... wow shocking, lol !

Any change in the obscure rule that if you don't provide answers for 30 days, you are no longer an expert even after 10+ years of contribution and dozens of grades?

30 days is no more than a vacation (in Europe) or a busy around-the-clock contract.

/gustav

@ Netminder <ot>  Jeff Conrad is back 'in town' (at Microsoft). so if you want to contact him as we discussed, now might be a good time </ot>

Joe

@gustav,

<<30 days is no more than a vacation (in Europe) or a busy around-the-clock contract.>>

 There is stuff in the works and I think you'll be quite happy.

Jim.

Interesting quirk to be noted.
If I open this question from the email link and DO NOT login, @Netminder's posts are redacted from what is shown.
If I am logged in, they become visible.

"There's NO difference in the business model."

ummmm ... I would say there is.  We answer questions for .... *free*. Time is money.  You don't get cable and the newspaper for free.  You pay for the service. EE does not pay us for the 'service'.

So, not reeeeeally the exact same biz model.

mx

> "There's NO difference in the business model."

Interesting. I see a huge difference. And there is; else there wouldn't be "stuff in the works".

> (how it can be easier than answering a couple of questions in a month, I don't know,

That's because some of us have a life outside of EE, be it familiy or busines or - most likely - both.

/gustav

We answer questions for .... *free*

That's like saying you do the crossword puzzle or write a letter to the editor for free. You still pay for the paper. If you get caught robbing a bank, you still contribute the content ... for "free". And if you don't want to contribute content, you don't have to -- and there's nothing EE can do to make you.

It's an Exchange. You "pay" for the Premium Services by answering a couple of questions a month. Someone else "pays" by handing over a credit card number -- frequently their boss's.

And there is; else there wouldn't be "stuff in the works".

Nonsense. Subscription media are constantly coming up with new reasons for you to keep subscribing and to get new subscribers. That EE is doing the same thing should be expected; it would be astonishing if they didn't.

That's because some of us have a life outside of EE, be it familiy or busines or - most likely - both.

And that's your choice, and more power to you. But you also shouldn't complain that when you stop keeping up your end of the exchange that the other side would stop keeping up theirs. They owe none of us a damn thing; that we kept the site alive when the company was bankrupt is a debt repaid -- at least in their eyes -- long ago. That we contribute the content that keeps people coming back is appreciated (there are shirts, after all), but that EE even gives away t-shirts shouldn't be expected.

Nick,

I'll post this one as a regular comment; I'll bet it shows normally.

N

Well, it appears that you may have forgotten that we are only here for the fun. Not the points, not the T-shirts.
At the moment we should be taken for granted, the fun will vaporise, and we are out.

The subscription comparison is not valid. Even though I put a question at EE now and then, I wouldn't consider paying. There are too many free alternatives out there. Not that I think the fee is too high (I don't even know the current rate), I just feel being outside the target group.

The single fact that bothers me is, that answering thousands of questions during a decade counts for nothing. After 30 days you are locked out and ranked in line with he/she who opens the EE page for the first time.

My recommendation is to fabricate a sign to put on the table the next time you discuss with the powers that be how to nurse and encourage the experts to maintain motivation and loyalty:

    Without EE, the experts are still experts
    Without the experts, EE is nil

Read this out loud as a good starting point.

Looking forward to the new stuff!

/gustav

I think I see what you mean, and you provide a lot of inside details I didn't know about. Or didn't care about.

However, you have to look at it outside in. Then you'll see for an expert like me:

1. Participation in EE activities is a kind of membership, not a subscription
2. The business model of EE doesn't matter

ad. 1: EE is more like a forum. You can read and contribute as time and skills allow meeting a positive attitude
ad. 2: I don't care if EE has 10K or 10M members, wether they pay or not, or if the owners have sold their family or make a fortune. What counts is if the site works well (it does, indeed after the latest redesign)

ad. 1: The attitude is important. This is where the fun starts and, indeed, where it ends if it is negative. That's why I left UtterAccess from one day to the other. It is run like a sect, and if you think different than the priests (not to say the God), you are banned. Your writings are silently deleted and complains remain unanswered. Then you realise that your time is wasted.

Thinking about it, you mentioned the T-shirts. It's a non-rational thing. I mean, I can buy all the T-shirts I want but it is more fun to receive a special one of good quality.

/gustav

@gustav,

 Since it's now been made more or less public (Brian had a public webinar yesterday); it will be easier to earn free membership (bar has been lowered) and as long as you have 50,000 points in the past rolling 12 months, you will have free membership.   You can now take off for months at a time without issue.

Jim.

A system like that was exactly my wish.
Thanks to Brian and whoever made this come true.

/gustav

More details will be forth coming shortly.  Points have been revamped on articles and videos as well.

On the flip side, something else that some will cheer is that the bar has been raised for getting classed as an "Expert" vs just been a "member".

Quite a few changes  coming, many of which I think the majority of you will like (member to member messaging, member groups, content feeds, changes in the points system, etc).

Jim.

will the webinar be available for viewing any time soon?

I only caught about the last 10 minutes.

I posted a question and was surprised that there was not a huge question queue.  At any rate, I asked if they were going to implement some form of point system so that users that find the answer to their problem by simply searching the knowledge-base could points to the solution that met their needs.  Brian indicated that they didn't currently have plans for that functionality, but that he liked the idea.

I thought it was kind of dorky that the CEO of an IT company on a webcast, was talking on a phone rather than a headset or a mike connected to his computer, but that may have had to do with signal latency.  As a minimum, I would have put him on a wireless headset or speaker phone.

The reason I think so is that your comments are labelled "Administrative Comment" and mostly express a view as seen from the inside - executives or board, whatever. Also, you seem to be familiar with happenings like a "serious jeopardy" of EE which I never have heard about.
However, nothing is Black and White and I'm happy that you have the other set of glasses as well.

/gustav

@ NetMinder:

(re Gustav's comment)
"That's why I left UtterAccess from one day to the other. It is run like a sect, and if you think "

Remember my Dark Side comment in that LinkedIn thread regarding UA ... :-)

Just sayin' ...

====

Thanks for the insight. Your wording "serious jeopardy" seems to label the situation then correctly.

/gustav

@Dale,

<<I thought it was kind of dorky that the CEO of an IT company on a webcast, was talking on a phone rather than a headset or a mike connected to his computer, but that may have had to do with signal latency.  As a minimum, I would have put him on a wireless headset or speaker phone. >>

  One of the things you'll discover quickly is that in any type of web based video conference, you'll always get far better audio quality over POTS rather than VOIP.   Speaker phones kind of suck too because the phone is always trying to  figure out if your speaking or not and have a tendency to cut you in and out.  Good quality conferencing is tough unless you have dedicated conference equipment.

 A wireless headset though would have been the ticket or better yet, a wired headset (no batteries that can die, no interference, etc. )

Jim.

Here is the link to last weeks webcast with CEO Brian Clausen

Hey,

Confirm for me something is very wrong with how EE is serving up the attachments here.
Attempt to pull down that attachment in the last post.
https://www.experts-exchange.com/questions/28421862/Moving-subform-listbox-items-to-table.html?anchorAnswerId=40203143#a40203143
On multiple machines, the browser is opening the accdb as a textfile and not prompting for download.
The Asker has that problem, and I've got it at work and at home, too, with IE 11.
Chrome seems to be working correctly.

The mdb attachments prompt differently than they used to, and differently than the accdb ones.  The accdb ones try to open as .htm files

Nick67

Nick,

Are you tring to open the attachment directly.

don't know whether it is just me or not, but I've always right clicked and selected Save Target As,  but interestingly even when you try to do that it wants to save the file as a .htm document.  But I generally use FireFox.

But keep in mind that they did something (I think it was over the weekend) to switch to some new host, so that may have something to do with it.  Have you posted a "bug report" on the community bugs topic?

I have IE 8 here and have the same issue.  Firefox works OK.

Jim.

I haven't posted a bug report.
I wanted corroboration first.
Do you want to post it, @Jim?

I posted this as a bug several days ago.

/gustav

I'd pile on to your bug report ... but I can't find it.

Good Luck finding that!
I guess if I'd've lurked /gustav and looked at his activity I'd've gotten there.
Reported here now, too
https://www.experts-exchange.com/questions/28478573/Accdb-downloading-is-busted-in-IE.html.

The accdb's are badly broken because it decides they are html, but the mdb's dialog has changed, too.
The smooth, 'this is a download link and I know it' behavior is broken.
The only reason mdb's are less broken is because the browser has no idea what to do and looks for user input.

It's been almost a week, and it's not fixed -- and it's a bit of a pain -- much like accdb in general -- in the butt!

I have four new articles in the VBA or Access area:
Iterating Office Enumeration Constants: http:A_14015.html
RichText to Text conversion -- fast and free: http:A_14035.html
Range Iteration: http:A_14076.html
Big Choices -- Dynamic columns with A Better Choose function: http:A_14179.html

What, only four?   LOL

The editorial pipeline is a bit sluggish at the moment.  I waited until they were all in published status.

This was an unusually productive month.  The articles and question points vaulted me from 201st to 196th place over all.

Since the Iterating Office Enumeration Constants article relies on an old DLL, I'm working on a sequel article that parses the typelib with native VBA/VB code.  As with most of my articles, the inspiration comes from an answer I've given in a question.

Also, these articles are under the new CKEditor system.  I found a couple of bugs (now reported) and at least one work-around.

From /gustav

">  This is NOT a bug.

<humour>
Oh, I see you cashed in 58.000 points. Let me guess the designer of the new system ..
</humour>

/gustav
"

 Actually, I was just as surprised this morning....I didn't realize they were going to adjust for old articles and videos.  As for being the designer, I could only wish<g>

 Seriously, a lot of these changes are coming as a result of the PAC (Product Advisory Committee) and the fact that EE wanted to move forward in different ways.   The PAC is a group of about ten members, including a few that consider themselves to be more of simply a "member" rather than an expert.

 What your seeing is a result of the ideas that came out of that group and the new group of folks at EE.     The group as a whole comes up with areas that should be targeted, then a small 2-3 member group works on the feature along with some EE folks through a formalized process.

 The intent is to keeps things small and focused and moving forward.   That seems to be working well, because if you look back at the changes that have occurred in the last six months, it's nothing short of amazing.    Might not get it right all the time, but so far the overall concept seems to be working well.

 Lot's more to come too.   At the moment, were just getting a lot of fundamental building blocks in place (Messaging, Networking, News Feed, etc) and only the basics of those.  Spit and polish will come as things get shaken out.

 Hopefully I haven't said more here than I should (under NDA), but I just wanted everyone to know that things are being worked on, improving (hopefully), and there is more to look forward to.  Exciting times.

Jim.

OK folks, just a heads up that in the near future we will be setting up a discussion group to replace this thread and possibly the notify thread.

Please note that groups can be setup as wide open or require approval to join, so it will be possible to have a group specifically for the folks that contribute here on a regular basis or anything in between.  Any feedback you'd care to offer would be welcome.  e-mail me off-line if you prefer.

Note too this is going to be a trail run at it...it may take a bit to figure out what does and doesn't work.  So for a start, I would like a few folks who would like just to try it out and see how it works along with me.   After that, we'll move onto replacing this thread, and then possibly the notify one as well, and also seeing what other groups we might like to setup.

Jim.

I really enjoy the banter and expert-to-expert discussions that take place on the accessmvp googlegroup, so I'm looking forward to a similar functionality here.

I'm looking forward to that as well and getting away from this question/comment format.   Been a long time in coming....I suggested this back in 2006 or so and I know many others wanted chat type features as well.

Jim.

Your probably right, but I didn't feel like digging out my notes<g>.  I just know it was awhile ago and your the only one that would probably know any better<g>

Jim.

cool.

Some questions are top-left labelled: Private
What does that mean?

Example: https://www.experts-exchange.com/questions/28517770/Access-2010-Animated-object-that-only-requires-Access-2010.html

/gustav

If your subscription level allows it, you can mark your question as private - which means (essentially) it is not indexed (to the outside world)

read up via help : http://support.experts-exchange.com/customer/portal/articles/770046

OK, thanks. I marked the topic as helpful.

/gustav

wow ... that is truly sad :-(

All,

  I've started a new group:

https://www.experts-exchange.com/groups/Microsoft-Access-Professional-Developers-Group.html

 which is focused on folks that are developing Access apps professionally for a living outside of being an employee of a company.   Topics would be things like "Do you use contracts?", "Do you bill for bugs", "Work on flat fees or by the hour?", "Where do you find cheap health insurance?" (short answer to that; no where<g>), or anything else that has to do with running your own consulting/development business.

Jim.