Expert Notify Thread - For those answering questions in the MS Access Zones (09-Sept-2013)

Hi Everyone,

  This "question" is for those contributing in the MS Access Zone.  If your are not contributing as an Expert, please do not post comments here.  This question provides the ability for an Expert to ask for assistance from other Experts in getting member questions answered.

For the Experts:

  The idea behind this "question" is to allow an easy means of getting other Experts and the Zone Advisor or Page Editor to look at something.

   It is a "notify* question in the sense that you initially only *subscribe* to the question (by using the "monitor" button), but *don't* post any comments.

  When you want to notify other Experts, you post a new comment in this question that might look something like this:

Subject: Expert Topic - Member needs help with a tough one

"Member xyz needs help.  Please see

<link>

AccessExpert"

  All of us will then receive an e-mail notification (because we are subscribed to this thread) and can click on the link to find out what needs to be done or talked about.  Please do *not* post general comments or hold a discussion in this notify question.  Keep in mind that everyone receives an e-mail each time a comment is added and it takes longer for the page to load as it grows larger (some of us are still on dial-up).

 Keep in mind that anyone will be able to see these comments (both members and Experts).

 If you have general discussion comments, they should be made here:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28234413.html 

 Previous notify thread is here:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26838771.html

Jim Dettman
MS Access Topic Advisor
LVL 60
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
Don't know how many of you are monitoring this new thread.  But DevelopingProgrammer has is having an issue deleting from a local table and then appending to it.  Thought some of you might take a look:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28235344.html

On of the OP's last posts indicates a number of things that work and don't work.  Several of these would resolve the problem, but like so many of the OPs other posts, it is about understanding, and I honestly cannot figure out why he is getting the error, given what works and doesn't.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
So then, it's an ongoing Development :-)
0
aikimarkCommented:
Here is a helpful hint for queries and VBA code when dealing with null-able columns.
When you want to check that the column has a non-zero (or zero) length, use the following syntax.
SQL:
When Len([mycolname] & "") <> 0    'non-zero length
When Len([mycolname] & "") = 0      'zero length

Open in new window

VBA:
If Len([mycolname] & vbNullString) <> 0 Then    'non-zero length
End If
If Len([mycolname] & vbNullString) = 0 Then      'zero length
End If

Open in new window

It makes for simpler query syntax and code than checking for both Null values and the Length of the text in the column.

In a VB6 environment with unbound controls, you might avoid the problem of assigning a null value to a textbox by concatenating the vbNullString constant during your assignments.

============
I keep forgetting this and hope that posting it would help me remember.  Also, it isn't sufficient to constitute an article.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

IrogSintaCommented:
@aikimark,
An alternative I've been using since I've seen it used by others here is:
If [mycolname] & "" = "" Then        'zero length

Open in new window

0
Jim P.Commented:
What about

If IsNull([mycolname]) = True      'zero length

Open in new window

for VBA
and
WHERE Nz([mycolname],"") = ""  

Open in new window

for queries?
0
aikimarkCommented:
@Jim

* NZ() isn't available in all VB environments.

* In many cases, you need to check for both a null/not-null condition in addition to the length of a text column/field.

===========
@IrogSinta & Jim

* The Len() function is more efficient than a string comparison.
0
Jim P.Commented:
* NZ() isn't available in all VB environments.

Let me requote that:
VBA:
If IsNull([mycolname]) = True      'zero length

Open in new window

Queries:
WHERE Nz([mycolname],"") = ""

Open in new window

The Nz is to be used in an Access query, the IsNull in the VBA.
0
IrogSintaCommented:
The Len() function is more efficient than a string comparison.
Sounds interesting. Can you expound on this?
0
Gustav BrockCIOCommented:
Too little coffee or too much beer?
This is a mess and adds much confusion.

> SQL:
> When Len([mycolname] & "") <> 0    'non-zero length

This is not SQL syntax.

> If [mycolname] & "" = "" Then        'zero length

No, it indicates Null or zero length.
Besides, it skips an index and forces a table scan on that field.

> The Len() function is more efficient than a string comparison.

That's what I thought for years. But this is fastest:

> If [mycolname] = "" Then     ' zero length

In general, you have to distinguish between:

 - check for Null
 - check for zero length string
 - check for Null or zero length string

SQL:

  Where [field] Is Null
  Where [field] = ""
  Where [field] Is Null Or [field] = ""

VBA:

  If IsNull([field]) Then
  If [field] = ""
  If Nz([field]) = ""

/gustav
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
:-)
0
aikimarkCommented:
"When" should be "Where"

Good catch.  Thanks.
0
aikimarkCommented:
Sounds interesting. Can you expound on this?

Strings are complex data structures that include both the text and the length of the text.  If you compare strings (<, <=, =, <> , >=, >) code has to first find the string with the lesser length and set up a comparison iteration for the characters in the strings.

If you use the Len() function, it gets the readily available datum out of the data structure.  Also, numeric comparisons, especially integers, are usually handled in registers and do not involve any floating point operations/circuitry.  This is why they are faster.

Way back in the 90s, I did some testing of common VB statements and demonstrated the string versus number performance differences to the local VB/Access user group.  This difference extends into If...Then and Select Case statements.  Although your code might be easier to understand if string comparisons are made, you will pay a performance penalty in high volume and critical situations.
0
omgangIT ManagerCommented:
I don't believe I am subscribed to both threads but would like to be.  Please post a link to the discussion thread.
OM Gang
0
omgangIT ManagerCommented:
Thank you!
OM Gang
0
omgangIT ManagerCommented:
Access application with ODBC connection to Oracle db via DSN.  Access app has been migrated from Win XP to Win 7 but now doesn't connect to Oracle db.  Oracle client tests successfully on Win 7 machine.  Here's the Q.  Perhaps I'm missing something?
OM Gang
0
Gustav BrockCIOCommented:
0
IrogSintaCommented:
I'm stumped with how to proceed with this question.  The OP wants to create a table that holds a record each for 20 departments for each day of the year to be used in scheduling employee vacations.  Can anyone else provide some insight please.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28252303.html#a39530638
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerAuthor Commented:
Would someone mind jumping in and answering this:

Using ADO to pass values to a SQL Server Stored Procedure and return a RecordSet
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28263624.html

I had to go orange and delete some comments because of the link policy.

I know the answer, but really would like to avoid posting as it might appear as a conflict of interest to the Expert that first responded and it doesn't look like he's coming back to the table.

Thanks,
Jim.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
JimD:

I don't think anyone would have an issue with you posting a solution there.

Edit - JimD

 Probably no one except maybe the Expert that I deleted the comments on.   But even so, I've always kept it as a general rule of thumb that any thread I go orange on for any reason, I make sure I don't get any points.

 There have been a few exceptions, but it's always very clear that posting orange is totally un-related to getting the points.

 In this case, to delete two comments and then turn around and be the first to answer would be a definite no-no in my book.

JimD.
0
Dale FyeCommented:
Anybody experience any problems with Access 2010 not being configured to Output to PDF?

This question was asked, and my response was to use the OutputTo method assuming that since the OP indicated they were using A2010, that option would be available.  OP indicates it is not, and that when he attempts to use acFormatPDF he gets an error message
0
Jim P.Commented:
Can I get some additional expert comments on this one:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28319780.html

Thanks!
0
Dale FyeCommented:
Anybody with lots of SQL Server experience want to chime in on this question?  I'm at a loss, and not nearly as experiences with SQL Server as some of y'all.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28326283.html
0
Dale FyeCommented:
Could someone please take a look at this question and bail me out!

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28339403.html

I generally try not to get too deeply involved with this OP, but got suckered in.  I honestly believe he has a lookup field in his table, and doesn't realize it, or has misspelled the field name and doesn't realize that.

Thanks,

Dale

================ edit
on it

aikimark -- zone advisor
0
Dale FyeCommented:
Could someone, please take a look at this question.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28346314.html

User is trying to create a query that joins two other queries and then uses a WHERE clause on a date field, but keeps getting a "datatype mismatch" error.

We have run through a bunch of steps to assess whether the fields in the WHERE clause are dates or strings, convert them to dates, and I am stumped.
0
Gustav BrockCIOCommented:
Why my full answer to the questioneer had to be deleted, I don't understand. I provided the answer and the workaround and a link with an example of using that workaround. This is far from just to provide a link without any comment or explanation.

It is plain stupid to have to copy and paste when a links provides the exact example code.
Actually, this policy violates the very basic princip of the web: the hyperlink.

EE should be "large" enough to accept that from time to time valid material exists on other sites.

I want my answer back, so I can copy and paste some code.

/gustav
0
Dale FyeCommented:
Anybody have time to take a look at this post

Issue started out being about a 3 or 4 table query, but eventually when what I thought should work didn't, we took it down to a single, one table query with hard coded date parameters and the OP indicates the query is still returning dates outside of the values we entered.

This should be very straight forward, so I'm hoping someone else can take a look.  I don't think I can see the forest for all the trees.

Dale
0
Dale FyeCommented:
Anybody know if there is a Experts thread in the Visual Basic topic area?  Or maybe one of you guys/gals with recent Visual Basic (VB6) experience can take a look at this question.

OP is having difficulty looping through the selected items in a VB6 multi-select listbox.  I originally didn't pay any attention to the topic area and provided an Access solution, but apparently the syntax is a bit different in VB6.

============edited by aikimark
commented.  thanks for the heads up.
0
Dale FyeCommented:
need a moderator to pull the attachment from this message.

http://www.experts-exchange.com/Database/MS_Access/Q_28444479.html#a40115843
0
Dale FyeCommented:
Thanks, Jim.  Wasn't sure of the quickest way to achieve that result, but you were all over it.
0
Dale FyeCommented:
Jim,

Any way to tell how many people looked at that thread or downloaded the file?
0
Gustav BrockCIOCommented:
Thanks.
I watched the video (link above) and noticed the upcoming groups. Would this thread turn into such a group?

/gustav
0
Gustav BrockCIOCommented:
>  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
0
Dale FyeCommented:
I was kind of surprised they chose to retroactively award extra points for articles.  I think it would have been better just to have started that practice beginning today, or maybe have backed it up a couple of weeks, but not for articles submitted more than a month ago.

JDettman:

  because of the way points are totaled, it would have been far more complex to leave points on older articles and videos alone.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Where is the new thread ?
0
Dale FyeCommented:
a little help here.

OP is having difficulty with a form that doesn't seem to maximize properly in Access 2013.  I'm still doing most of my client work in 2010 and have not encountered this problem.
0
IrogSintaCommented:
I'm stuck on this one question where the OP is using the Web Browser control to display a PDF.  He followed a video tutorial but his PDF opens up in a separate window and not within the control.  I followed the same instruction and it worked fine.  He even sent me his db and it worked just fine on my computer.  I figured there must be some default setting on his computer that causes this but am not sure what.
http://www.experts-exchange.com/Database/MS_Access/Q_28682539.html#a40825229

 Ron
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerAuthor Commented:
All,

Two things:

1.  EE is in the process of re-tagging some questions and has discovered that they have a bug in a system function that skips the processing of a neglected e-mail alert upon edit (they edit the question to change the tags).

 If you are getting hundreds of neglected e-mail alerts, you may want to go into your profile and disable designated Expert on all the TA's for a few days at least.

2. I am closing this question thread.  All notifications will now appear here:

http://www.experts-exchange.com/discussions/209505/Expert-Notify-Thread.html

and any off-topic/general banter should be posted here:

http://www.experts-exchange.com/discussions/82/The-Water-Cooler-General-discussion.html

 Both of those are part of Microsoft Access Discussion Group:

http://www.experts-exchange.com/groups/Microsoft-Access-Discussion.html

 Please feel free to start any new discussions you wish if they are on a specific topic.  Otherwise use one of the two discussion threads above as appropriate.

Thanks,
Jim Dettman
MS Access Topic Adviser.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.