Link to home
Start Free TrialLog in
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)Flag for United States of America

asked on

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:

https://www.experts-exchange.com/questions/28234413/Access-Experts-General-discussion-thread-for-Access-Zone-9-Sept-2013.html 

 Previous notify thread is here:

https://www.experts-exchange.com/questions/26838771/Expert-Notify-Thread-For-those-answering-questions-in-the-MS-Access-Zones.html

Jim Dettman
MS Access Topic Advisor
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

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:

https://www.experts-exchange.com/questions/28235344/error-Could-not-update-currently-locked.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.
So then, it's an ongoing Development :-)
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.
@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

What about

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

Open in new window

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

Open in new window

for queries?
@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.
* 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.
The Len() function is more efficient than a string comparison.
Sounds interesting. Can you expound on this?
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
"When" should be "Where"

Good catch.  Thanks.
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.
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
Thank you!
OM Gang
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
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
Avatar of Jim Dettman (EE MVE)

ASKER

Would someone mind jumping in and answering this:

Using ADO to pass values to a SQL Server Stored Procedure and return a RecordSet
https://www.experts-exchange.com/questions/28263624/Using-ADO-to-pass-values-to-a-SQL-Server-Stored-Procedure-and-return-a-RecordSet.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.
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.
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
Can I get some additional expert comments on this one:

https://www.experts-exchange.com/questions/28319780/How-to-store-file-attachments.html

Thanks!
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.

https://www.experts-exchange.com/questions/28326283/Different-results-sets-when-pulling-SQL-table-to-Access.html
Could someone please take a look at this question and bail me out!

https://www.experts-exchange.com/questions/28339403/How-write-values-to-table-if-record-does-not-exist-but-over-write-record-if-it-does-exist.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
Could someone, please take a look at this question.

https://www.experts-exchange.com/questions/28346314/Where-clause-in-query.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.
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
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
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.
Thanks, Jim.  Wasn't sure of the quickest way to achieve that result, but you were all over it.
Jim,

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

/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
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.
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.
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
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial