ACCESS VB Question

[Libre]

Maybe Stewart can help me here.
OR ANYBODY PLEASE!
Sorry if this question is in the wrong place - i just need some help.

I have a field in a table defined as a number, with 2 fixed decimal places.
When I convert it to a string, it's fine as long as the decimals are not zero. In other words, if I have the value of 1.25 in the field, it converts fine to "1.25". But when I have zeros in the decimal, it drops them, so 1.00 converts to "1", and 1.10 converts to "1.1". I've tried all kinds of ways to get it to hold on to the zeros, but so far nothing has worked.
When the number is used in a text box it displays correctly.
I'm doing something like:
MyString = rst![MyNumber]
I need MyString to correctly hold the value of MyNumber even if there are zeros after the decimal.
Actually, I am converting to a variant first and then a variant to the string.
Any suggestions?

 

[direstraits]

Gee, I've never answered a technical question on Access VB before, much less on a Book Forum! Hehe...

Here goes:

Try:
MyString = Format(rst![MyNumber], "0.00")

That should do it.

Actually, you shouldn't directly use rst![MyNumber], because if MyNumber is ever a null, you're going to throw up an error.

Let me know if you need anything else.

ds

 

[direstraits]

Ah what the heck.

I usually use a custom function to handle instances exactly like yours when I want the value of a table field, but run the risk of accessing a null value. Therefore, in the above example, I'd do this instead:

MyString = Format(GetDBField(rst![MyNumber]), "0.00")

And GetDBField() will be:

Public Function GetDBField(sDBField, Optional sDontTrim As String, _
Optional bClearCRLF As Boolean) As String
' if the retrieved db field is null, then return an empty string, else trim the result
' this is important as the retrieved db fields that are null, when being assigned to
' a string variable/control, will result in an error.

' if sTrim is not empty string, then it will skip the trim
If sDontTrim = "" Then
GetDBField = IIf(IsNull(sDBField), "", Trim(sDBField))
Else
GetDBField = IIf(IsNull(sDBField), "", (sDBField))
End If

If bClearCRLF Then
GetDBField = Replace(GetDBField, vbCrLf, " ")
GetDBField = Replace(GetDBField, vbTab, " ")
End If

End Function


ds

 

[Stewart]

Urgh, a horrible bit of relative addressing there, ds. I would prefer to use the absolute method as that way you are guaranteed to know if there is an error with your code when it's compiled rather than for it to break down at runtime.

i.e.

Rather than:

MyString = Format(GetDBField(rst![MyNumber]), "0.00")

use:

MyString = Format(GetDBField(rst.Fields([MyNumber]), "0.00"))

It may look longer, but it's better.


As for your function, I wouldn't use the IIf() function as this wastes evaluation time since both sides of the function are evaluated to determine which is true. You can remove it, along with the IsNull() function by making use of the Nz() function.

I've also altered the "" to vbNullString as this is a compiled constant within the application, where "" has to be compiled to the same thing.

Public Function GetDBField(sDBField, Optional sDontTrim As String, _
Optional bClearCRLF As Boolean) As String

    ' if the retrieved db field is null, then return an empty string, else trim the result
    ' this is important as the retrieved db fields that are null, when being assigned to
    ' a string variable/control, will result in an error.

    ' if sTrim is not empty string, then it will skip the trim
    If sDontTrim = vbNullString Then
        GetDBField = Trim(Nz(sDBField) vbNullString))
    Else
        GetDBField = Nzl(sDBField), vbNullString)
    End If

    If bClearCRLF Then
        GetDBField = Replace(GetDBField, vbCrLf, " ")
        GetDBField = Replace(GetDBField, vbTab, " ")
    End If

End Function

 

[Libre]

HEY GUYS - THANKS FOR THE HELP!!!
Stewart - your code may be better, and I thank you greatly, but for some reason it does not work.
I'm not sure why, but your code would not compile.
I was getting errors such as "External Reference Not Found" in the line:
MyString = Format(GetDBField(rst.Fields([MyNumber]), "0.00"))
Of course I'm replacing MyString and MyNumber with the correct variable names.
Also, in the function, the syntax of: GetDBField = Trim(Nz(sDBField) vbNullString)) was causing probs.

direstraits - IT WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
(the code from your second post)

This is to create an invoice for diamonds by the way. Diamond weights are always in 2 decimal places - i.e. 1.00ct, NEVER 1ct.
So I thank you, and so do all the ladies who are buying the little sparklies right now - so I have to go and sell them STAT!

 

[Stewart]

Stewart - your code may be better, and I thank you greatly, but for some reason it does not work.

I was getting errors such as "External Reference Not Found" in the line:
MyString = Format(GetDBField(rst.Fields([MyNumber]), "0.00"))

The field name should be a string literal. Sorry. I was just free typing and trying to rush out the door at the same time.

Also, in the function, the syntax of: GetDBField = Trim(Nz(sDBField) vbNullString)) was causing probs.

I missed a comma separating the arguments.

I mod here too and it's one hell of a resource for Access knowledge, if you'd be interested.

 

[Libre]

Okay - thanks -I'll check that forum out.
I've looked at it the past - I thought I created an account but it won't let me in.
If you get around to it, could you PM me my log-in?
In any event - I hope it's okay to talk about Access and VB under the GENERAL CHAT heading.
I do have some questions about your code, Stewart, but I'm getting that this is not the place to ask.
In any event - I'm grateful to both you and ds.
Thanks

 

[direstraits]

Libre, you're most welcome. It was the simplest fix for you, with minimal changes to your code - guaranteed to work, or your money back.

Urgh, a horrible bit of relative addressing there, ds.

I don't know if it's called relative or absolute, but I've always preferred accessing the name of the field using the rst!SomeField rather than rst.Fields("SomeField"). Mainly because in all my database accesses I dislike using ADO and manipulating records within a recordset, and having to issue a rst.Update.

I much prefer direct sql commands, executed throught myconn.Execute calls. For some reason, early during my development days, I found that referencing fields using 'absolute' method will generate an error with recordsets generated via an .Execute method. Since then I've always stuck with the "!".

I would prefer to use the absolute method as that way you are guaranteed to know if there is an error with your code when it's compiled rather than for it to break down at runtime.

Hmm... if you're talking about referencing table fields, then I don't think the compiler can figure out if a particular field in a table exists or not during compilation, regardless of whether you use (as you put it) the relative or absolute method. This is because the compiler is database agnostic, and will not access your tables to see if it's there or not. I'm pretty sure that

rst!MissingField

and

rst.Fields("MissingField")

will both compile just fine, even if MissingField doesn't exist in the database table.

As for your function, I wouldn't use the IIf() function as this wastes evaluation time since both sides of the function are evaluated to determine which is true.

If you're using VB in the first place, I don't think wasting negligible CPU cycles is something I'd worry about.

Btw, the nz() function is not a recognized function in VB, not VBA, so Libre will not be able to get it working with or without the comma.

But I did learn two things, vbNullString, which I didn't know of before. And I also learnt that vBulletin has a

 tag for html codes.  Thanks!

Hey this is fun.  

ds

 

[Libre]

Btw, the nz() function is not a recognized function in VB, not VBA, so Libre will not be able to get it working with or without the comma.

dire, this statement is not that clear to me.
But, in any case, I use the nz() function very frequently - pretty much anytime I use an equal sign.
And thanks again for your code. Yesterday I just pasted it into my module and it worked.

 

[direstraits]

Whooops...

I thought you were developing using Visual Basic 6... in VB6 there's no such thing as nz() in the base language.

No probs.... just remember that if you have any spare diamonds you know who to send it to!

ds