Any reason why this is wrong?

[pr2-eugin]

I am trying to take data from a memo field and split it into strings and place them in the combo box.. it is runnign fine.. except for a very small problem.. the coding works to extract the data from the memo box and place it neatly in the combobox; except that it is not complete.. i.e. the string is chopped off.. If i had entered data as "The customer changed the value and has now requested a new contract. the request was made on 21/12/2011 12:56:35." the result displayed is ""The customer changed the value and has now" the rest of the data is missing.. following is the code.

dumStr = DLookup("[FAILED PAYMENTS]", "Customers", "CUSTOMER_ID = " & id & "")
If Len(dumStr & "") > 0 Then
iArray = Split(dumStr, "#")
For iCtr = 0 To UBound(iArray)
'inserted a Msgbox to display the values they are fine. i mean the iArray(iCtr) has the whole data.
FailCmbBox.AddItem iArray(iCtr)
Next
End If

 

[harmankardon]

Can I ask what the & "" is all about ?

I think

"CUSTOMER_ID = " & id & ""

Should be

"CUSTOMER_ID = " & id

But maybe I'm missing something?

Also, can you explain the use of & "" here as well?

If Len(dumStr & "") > 0

 

[vbaInet]

"The customer changed the value and has now requested a new contract. the request was made on 21/12/2011 12:56:35." the result displayed is ""The customer changed the value and has now" the rest of the data is missing.. following is the code.

iArray = Split(dumStr, "#")

I don't see any hash (#) in your string.

 

[vbaInet]

Also, can you explain the use of & "" here as well?

If Len(dumStr & "") > 0

One of us on here would have advised on its use.

Len(Null) will return Null and it will error when it's time to compare if it's greater than 0.
Len(Null & "") will implicitly cast the Null to string and hence, the result will be 0 for Null.

Another variation is Len(Nz(dumStr, vbNullString))

 

[harmankardon]

One of us on here would have advised on its use.

Len(Null) will return Null and it will error when it's time to compare if it's greater than 0.
Len(Null & "") will implicitly cast the Null to string and hence, the result will be 0 for Null.

Another variation is Len(Nz(dumStr, vbNullString))

Ah, clever, thanks for the explanation.

For the DLookup, using that same trick doesn't really help you though because the DLookup is still going to return a null value right?

 

[pr2-eugin]

@ harmankardon > Well i believe vbaInet has clarified the issues..

@vbaInet > Yes i have not included the # in the example.. but in my table.. the memo field has a lot of strings such as mentioned above and are seperated by #.. the following is the example what the memo field (FAILED PAYMETS in this case) holds..

------#20/12/2011 17:36:42The customer has changed the pay date, which means they owe a payment of 1.23 for the difference of 5 days. #20/12/2011 18:30:44The customer has changed the pay date, which means they owe a payment of 2.1 for the difference of 4 days.#20/12/2011 18:49:15The customer has changed the pay date, which means they owe a payment of £2.63 for the difference of 5 days.

so that is the reason i have used Split function to split it into strings..

 

[vbaInet]

For the DLookup, using that same trick doesn't really help you though because the DLookup is still going to return a null value right?

If you're assigning it to a variable or performing some sort of comparison then it's worthwhile:

someVariable = Nz(DLookup(...), vbNullString)

 

[vbaInet]

Replace vbNewline characters with vbNullString in the string before splitting.

 

[pr2-eugin]

i beg your pardon..

 

[vbaInet]

I mentioned all the necessary keywords. Replace() is a function, look it up.

 

[pr2-eugin]

so do you suggest that there might be problem because, the memo field might be broken because of NewLine.. so i replace the NewLine with NullString which makes it continous for it to looked up by the split method.. am i getting it right?

 

[pr2-eugin]

well i tried it; now it is throwing up an Invalid use of Null exception.

 

[vbaInet]

Correct!

We discussed how to handle Null throughout this thread. I would have expected you to pick up on this already.

 

[pr2-eugin]

am sorry for my ignorance.. lol.. yes.. i did it.. but still i am not able to view the entier message.. the result is the same as before..

maybe i am doing it wrong.. i used the replace immediately after the if condition.. is that right?

 

[vbaInet]

Nope!

iArray = Split(Replace(dumStr, ...), "#")

 

[pr2-eugin]

hmmm... i did that actually... it gives the same result.. i however did.. dumStr=Replace(dumStr,vbNewLine,vbNullString).. but then again its the same.. is it not?

i am getting the same result.. does it have anything to do with the combobox? because when i used a MsgBox to see that different strings.. i can see it completely.. but then it changes when it gets placed on the combobox..

 

[harmankardon]

Using the debugger can you verify that the combo box is not getting the full string? Is it possible that it's getting the full string but its not being displayed due to some formatting issue?

Also, is it possible there is a character limit on the control source for the combo box?

 

[pr2-eugin]

i have checked whatever you have mentioned.. there is no character limit, maybe there are some formatting issues thats the reason i made the AfterUpdate property of the combobox value to be placed on a text area where i can view the details.. where the error actually is.. because i am not getting the full string to view on the text area..(when i say text are it is nothing but another memo field on the form)

 

[vbaInet]

As long as you did it before the Replace() is applied it should be fine.

Try replacing Chr(13) & Chr(10) for vbNullString

 

[pr2-eugin]

you mean both in place of vbNullString.?? because Chr(10) is new line and Chr(10) is return.. so the combination of the two results in a null value?