Solved

vba if statement returns false

Posted on 2012-03-13
9
262 Views
Last Modified: 2012-03-16
I'm using the if statement below to modify the string strMySql.
' initialize
   strMySQL = "Update Part_Serial_Number Set "
   
    'build sql statement from text boxes that have data
    If Me.txtPCBoard_Lot_No1 <> "" Then
        strMySQL = strMySQL & "[PCBoard_Lot_No1] = " & str_txtPCBoard_Lot_No1
    End If

 DoCmd.RunSQL strMySql
 
When Me.txtPCBoard_Lot_No1 is empty, strMySql is being set to "False".  I would expect it to just leave strMySql as it was.  

I don't recall seeing this before.  Any ideas?

Thanks,
Brooks
0
Comment
Question by:gbnorton
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37717030
case sensitive issue?
strMySQL  <> strMySsql....
please check/confirm, also by using OPTION EXPLICIT to ensure you declare all your variables.
0
 

Author Comment

by:gbnorton
ID: 37717065
In the code I posted I see I used both upper and lower case.  In the project code I verified the variable name is correct and consistant.  Option Explicit set.
0
 

Author Comment

by:gbnorton
ID: 37717142
This code is illogical to me but works:
    If IsNull(Me.txtPCBoard_Lot_No1) Then
        strDoNothing = "Doing Nothing"
    Else
        strMySQL = strMySQL & "[PCBoard_Lot_No1] = " & str_txtPCBoard_Lot_No1
    End If

    If IsNull(Me.txtPCBoard_Lot_No2) Then
        strDoNothing = "Doing Nothing"
    Else
        strMySQL = strMySQL & [PCBoard_Lot_No2] = " & str_txtPCBoard_Lot_No2"
    End If
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 400 total points
ID: 37717932
1. Is what you posted originally the *entire* code...?
I don't see where you are declaring: strMySQL

2. To be sure, I always check for null as well as an empty string:
If Me.txtPCBoard_Lot_No1 <> "" Or not isnull(Me.txtPCBoard_Lot_No1) Then
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37718702
do you have something a variable or string with the & directly attached, aka with no space?

"string"&  vs "string" &
or
variable& vs variable &
0
 
LVL 30

Expert Comment

by:hnasr
ID: 37719437
If Nz(Me.txtPCBoard_Lot_No1, "")  <> "" Then
        strMySQL = strMySQL & "[PCBoard_Lot_No1] = " & str_txtPCBoard_Lot_No1
End If
0
 
LVL 16

Assisted Solution

by:Walter Ritzel
Walter Ritzel earned 100 total points
ID: 37720909
The code you posted is very logical, and you should do what boag2000 mentioned... Test the variable for NULL and <> "" to make sure you caught all the situations where you dont want to issue your update.
0
 

Author Comment

by:gbnorton
ID: 37721004
I'll update you tomorrow.  Out today.  Thanks, Brooks
0
 

Author Closing Comment

by:gbnorton
ID: 37729746
thank you,
Brooks
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Populating datasheet subform from VBA 6 16
Need return values from a stored procedure 8 23
Search feature 14 29
MS SQL GROUP BY 5 11
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question