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/Microsoft/Development/MS_Access/Q_28234413.html 

 Previous notify thread is here:

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

Jim Dettman
MS Access Topic Advisor
Microsoft AccessVBA

Avatar of undefined
Last Comment
Jim Dettman (EE MVE)

8/22/2022 - Mon
Dale Fye

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/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.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

So then, it's an ongoing Development :-)
aikimark

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
IrogSinta

@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

Jim P.

What about

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

Open in new window

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

Open in new window

for queries?
aikimark

@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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim P.

* 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.
IrogSinta

The Len() function is more efficient than a string comparison.
Sounds interesting. Can you expound on this?
Gustav Brock

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

:-)
aikimark

"When" should be "Where"

Good catch.  Thanks.
aikimark

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
omgang

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
omgang

Thank you!
OM Gang
omgang

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Gustav Brock

IrogSinta

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
ASKER
Jim Dettman (EE MVE)

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/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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott McDaniel (EE MVE )

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.
Dale Fye

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
Jim P.

Can I get some additional expert comments on this one:

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

Thanks!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Dale Fye

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/Microsoft/Development/MS_Access/Q_28326283.html
Dale Fye

Could someone please take a look at this question and bail me out!

https://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
Dale Fye

Could someone, please take a look at this question.

https://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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gustav Brock

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
Dale Fye

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
Dale Fye

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Dale Fye

need a moderator to pull the attachment from this message.

https://www.experts-exchange.com/Database/MS_Access/Q_28444479.html#a40115843
Dale Fye

Thanks, Jim.  Wasn't sure of the quickest way to achieve that result, but you were all over it.
Dale Fye

Jim,

Any way to tell how many people looked at that thread or downloaded the file?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gustav Brock

Thanks.
I watched the video (link above) and noticed the upcoming groups. Would this thread turn into such a group?

/gustav
Gustav Brock

>  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
Dale Fye

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Where is the new thread ?
Dale Fye

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.
IrogSinta

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question