• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

Problem Updating MDB Database

I'm having issues inserting information into a database (MDB). I believe everything in the database is set up. The field in question is a Date/Time format. Here's my code inserting into the database.

Set MyRs = Myconn.Execute("UPDATE all_users Set Name= '" & xName & "', Age= '" & Age & "', Sex= '" & _
 Sex & "' , City= '" & IIf(Len(city), city, Null) & "', State= '" & IIf(Len(state), state, Null) & _
 "', Country= '" & Country & "' , Music='" & IIf(Len(xMusic), xMusic, Null) & "', Last Login='" & Last_Login & "' Where ID ='" & List1.Text & "'")



To get Last_Login, I do this:
Last_Login = CDate(Split(strTemp, ":")(1))

Last_Login should be the correct format, is something else wrong?
0
Takamine334
Asked:
Takamine334
  • 9
  • 7
  • 6
  • +3
2 Solutions
 
jefftwilleyCommented:
Set MyRs = Myconn.Execute("UPDATE all_users Set Name= '" & xName & "', Age= '" & Age & "', Sex= '" & _
 Sex & "' , City= '" & IIf(Len(city), city, Null) & "', State= '" & IIf(Len(state), state, Null) & _
 "', Country= '" & Country & "' , Music='" & IIf(Len(xMusic), xMusic, Null) & "', Last Login=#" & Last_Login & "# Where ID ='" & List1.Text & "'")

you aren't using len() right, by the way.
0
 
jkaiosCommented:
In addition to Jeff's comments where you have to enclose the date value in pound signs (#), you should also enclose the field name [Last Login] in square brackets since it contains spaces.
0
 
Takamine334Author Commented:
what would be the correct use of Len?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
jefftwilleyCommented:
iif(len([xyz])=4,123,789)

its comprarive...what were you trying with it?
0
 
Takamine334Author Commented:
I think the way I was doing it too was correct.

if len equaled 0, then it'd be false, else it's true. I don't have to set it to equal a number.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
IIf(Len(xMusic), xMusic, Null) is incorrect and will fail.

Len() returns a Long integer ... and cannot be negative because the length of a string cannot be less than zero.  Hence, Len() will never be -1 would is True (boolean), so ...

"if len equaled 0, then it'd be false, else it's true."

In fact it will not be True

IIF(len(""),"jammin","joe")  returns Joe    ' but the length is zero

IF(len("")=0,"jammin","joe") returns jammin

So, it is a Jeff stated.

Correct use:

If Len("Takamine334") = 11 the MsgBox "MX"    

mx :-)

0
 
Takamine334Author Commented:
Well, I don't know what to tell you. It works for me and inserts it properly. I don't want to state a length value because the length always changes.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"I don't want to state a length value because the length always changes."

You don't need to ... just do this:

 IIf(Len(state)>0, state, Null)

mx
0
 
Takamine334Author Commented:
ok
0
 
PaulHewsCommented:
Guys, I thought I would weigh in on the secondary issue mentioned here:

>if len equaled 0, then it'd be false, else it's true. I don't have to set it to equal a number.

This is true, because of VB's behind the scenes conversion.  It converts a non-zero integer to boolean True, and zero to boolean False.  It is easily testable.

Private Sub Command1_Click()
    Dim str1 As String, str2 As String
    str1 = ""
    str2 = "hello"
   
    MsgBox IIf(Len(str1), "True", "False")  'displays "False"
    MsgBox IIf(Len(str2), "True", "False")  'displays "True"

End Sub

VBA can do some rather heroic, and often misguided implicit conversions:

MsgBox IIf(Now, "True", "False")
MsgBox IIf(#12:00:00 AM#, "True", "False")

http://www.brainbell.com/tutors/Visual_Basic/Evil_Type_Coercion.htm
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Then how do you explain this:

IIF(len(""),"jammin","joe")  returns Joe    ' but the length is zero

IF(len("")=0,"jammin","joe") returns jammin

mx
0
 
PaulHewsCommented:
Len("") will return zero, which converts to false (Joe) (which is how he was using it.)
Len("") = 0 is a boolean expression which returns true (Jammin)




0
 
jefftwilleyCommented:
Invigorating arguement...
and depending on the environment used to store and execute the expressions can be written this way as well

nz(city,"") & "', State= '" & nz(state,""), etc...

iif(isnull([city]),null,[city])

iif([city] is null, null,[city])

as long as the table receiving the values accepts nulls...he's good to go.
J
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
IIf(Len(state), state, Null)

ok ... how is this supposed to evaluate, ie ... what is the intent?

mx
0
 
Takamine334Author Commented:
Data - If len of state then state, else value = null

It works just fine.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
If len of state ... what?  

Well, that's not what I meant.  Say it in words ...

0
 
PaulHewsCommented:
If it's an empty string return Null, otherwise return the string...
0
 
Takamine334Author Commented:
state is either a value or it will equal ""

So, if the length of state is present, then I want to insert the value of state into the database (MDB), else I want to insert the value of NULL into the database.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Or Null ...

0
 
PaulHewsCommented:
Yes... Null.  Because that isn't the same as an empty string.  Since a string can never have a value of Null.  Right?




0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Because that isn't the same as an empty string. "

Definitely not the same.  
Correct, a String cannot be Null ...
But ... a field value in a Table *can* be Null ... and is Null until a value is entered, string or otherwise ... eg a Number data type or Date Time.  When a field in a table is defined ... the initial value is Null.  Later ... after data is entered ... it can still be set to Null.

Rarely is the Value of a Field = "" ... nor ... expect for possibly some very special case ... should it be.

SADLY ... there is a Table Field property called  Allow Zero Length ... and the damm default is Yes.  ALWAYS set that to NO ... unless you have a very specific reason to set it to Yes.

mx
0
 
PaulHewsCommented:
>Rarely is the Value of a Field = "" ... nor ... expect for possibly some very special case ... should it be.

Right.  Which is the real reason why that part of the original query is wrong... Not because of the use of Len()  

If xMusic is an empty string, then this:
"Music='" & IIf(Len(xMusic), xMusic, Null) & "'"

because of type conversion actually evaluates to:
Music=''

Which could be expressed more simply as:
"Music='" & xMusic & "'"

Whereas what I think he really wants in his SQL string output is:
Music=Null

Which should actually be expressed as:
"Music=" & IIf(Len(xMusic), "'" & xMusic & "'", "Null")

Leaving it out of the update isn't really right, as it's an update query, and there may be a value in there that he wants to Null out.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
The use of Len() in that manner is at best **confusing** ( as evidenced by the Len gth of this thread)... and may be taking advantage of some VBA anomaly.  

When you really think about it, none of those tests are necessary .... the value of City (or State, etc) is what it is ... Null or otherwise ... and can just be 'Set' to that value. IF ... I suspected there were any field values with zero length strings, that is what I would be looking for ... can convert them to Null.

mx
0
 
PaulHewsCommented:
>The use of Len() in that manner is at best **confusing** ( as evidenced by the Len gth of this thread)... <

It's actually a very common usage, and certainly not any kind of rocket science.  Substituting a numeric for a boolean.  You'll commonly see it as a shortcut in conditional expressions:

If Len(Dir(Filename)) Then  ' simple test for file existence

If Instr(strString, strSubstring) Then 'test if string contains substring.

hWnd = FindWindow(vbNullString, "Untitled - Notepad")
If hWnd Then  'Test if the find window API returned a valid window handle.

>and may be taking advantage of some VBA anomaly. <
As mentioned before, it uses the implicit conversion of datatypes that is a common feature of VB and VBA.

Sorry if you find this so perplexing.  The points were awarded, so I was really just posting for the quality of the PAQ and so that you might learn something that you didn't know about VB.  I learn new things every day here.
0
 
harfangCommented:
mx asked me to read through this and give my opinion.

Takamine334:

Your expression is meant to convert empty strings to null, but as PaulHews {http:#19541736} explained, it will not work as expected:

    "..., City= '" & IIf(Len(city), city, Null) & "', State=..."

If city is "Geneva", you get:

    "..., City= '" & IIf(6, "Geneva", Null) & "', State=..."
    "..., City= '" & "Geneva" & "', State=..."
    "..., City= 'Geneva', State=..."

If city is Null (suppose city is a variant, not a string), you get:

    "..., City= '" & IIf(Null, Null, Null) & "', State=..."
    "..., City= '" & Null & "', State=..."
    "..., City= '', State=..."

If city is '', you get:

    "..., City= '" & IIf(0, '', Null) & "', State=..."
    "..., City= '" & Null & "', State=..."
    "..., City= '', State=..."

Or exactly what you were trying to avoid: an zero-length string. I suppose however that Access will convert it to Null in most cases, i.e. when "allow zero-length string" is set to "no". In other words, your code probably did work in the end.

VB's Null will not convert to an SQL Null, unless you state it explicitly. I believe you really wanted to write this:

    "..., City= " & IIf(Len(city), "'" & city & "'", "Null") & ", State=..."

Now, if city is '' (or Null), you would get the correct SQL of:

    "..., City= Null, State=..."

You could really use a function for this:


Function QuoteSQL(pvarText) As String

    If Len(Trim(pvarText)) Then
        QuoteSQL = "'" & Replace(pvarText, "'", "''") & "'"
    Else
        QuoteSQL = "Null"
    End If

End Function


And then write:

    "..., City= " & QuoteSQL(city) & ", State=..."

Note that this also takes care of quotes within the city name, just in case.


mx:

I suspect that you asked me to read this more for the use of Len() than for the exact syntax used here.

The "computer definition" of true and false is, in all languages I know:

    0 = false
    not 0 = true

The "coding" for true is often 1; it's -1 in VB (for reasons I'm not going into right now). But in a test, <>false means "true".

How about Null? Null isn't "false" but is interpreted as "failure" in a test:

    IIf(Len(city), <true part>, <false part>)

Length of city = 0: <false part>
city is Null (length is Null): <false part>
length of city>0: <true part>

Quite simple, really. You can of course be purist and write:

    IIf(CBool(Len(city)), <true part>, <false part>)

But that (sadly) throws an error on Null...


Cheers!
(°v°)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
thank you Marcus... appreciate this.

actually, it was Jeff who pointed out the incorrect use of Len ... which got me started.

Paul ... thank you for your input also.  And yes, I too learn something everyday on EE ... it's a beautiful thing.

Crazy huh ... such a seemingly function ... can cause so much discussion.

mx
0
 
PaulHewsCommented:
>Crazy huh ... such a seemingly function ... can cause so much discussion.

Yep, but an interesting discussion.  harfang's comments about creating a function to test the string value and those about Null at the end are very much on point.
 
Thanks folks.

Cheers.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 9
  • 7
  • 6
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now