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
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
Last Comment
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:
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.
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
If Len([mycolname] & vbNullString) <> 0 Then 'non-zero length
End If
If Len([mycolname] & vbNullString) = 0 Then 'zero length
End If
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:
An alternative I've been using since I've seen it used by others here is:
If [mycolname] & "" = "" Then 'zero length
What about
and
If IsNull([mycolname]) = True 'zero length
and
WHERE Nz([mycolname],"") = ""
@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.
* 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:
Let me requote that:
VBA:
If IsNull([mycolname]) = True 'zero length
WHERE Nz([mycolname],"") = ""
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
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.
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
OM Gang
Thank you!
OM Gang
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
OM Gang
Remove menu File from Runtime?
https://www.experts-exchange.com/questions/28246855/Compiling-Access2010-eliminating-bars.html?anchorAnswerId=39515471#a39515471
/gustav
https://www.experts-exchange.com/questions/28246855/Compiling-Access2010-eliminating-bars.html?anchorAnswerId=39515471#a39515471
/gustav
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-exchang
http://www.experts-exchang
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.
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.
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
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!
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
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
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.
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
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
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.
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.
need a moderator to pull the attachment from this message.
https://www.experts-exchange.com/questions/28444479/ion-and-Query-Issue.html?anchorAnswerId=40115843#a40115843
https://www.experts-exchange.com/questions/28444479/ion-and-Query-Issue.html?anchorAnswerId=40115843#a40115843
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?
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
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
<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.
JDettman:
because of the way points are totaled, it would have been far more complex to leave points on older articles and videos alone.
Where is the new thread ?
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.
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-exchang
Ron
http://www.experts-exchang
Ron
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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.