Link to home
Start Free TrialLog in
Avatar of Takamine334
Takamine334Flag for United States of America

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of jefftwilley
jefftwilley
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
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.
Avatar of Takamine334

ASKER

what would be the correct use of Len?
iif(len([xyz])=4,123,789)

its comprarive...what were you trying with it?
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.
SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
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
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.
"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
ok
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
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
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)




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
IIf(Len(state), state, Null)

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

mx
Data - If len of state then state, else value = null

It works just fine.
If len of state ... what?  

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

If it's an empty string return Null, otherwise return the string...
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.
Yes... Null.  Because that isn't the same as an empty string.  Since a string can never have a value of Null.  Right?




"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
>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.
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
>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.
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°)
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
>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.