TYPE MISMATCHES USING ACCESS 2000 DATABASE 2

[TNN]

Not sure if this is the right forum to ask this but here goes.
Getting errors "TYPE MISMATCH" with following code:

With objSource.adoRsPyrlCalc4Ded
.AddNew
!EMPNO = strEmployee_Number
!DedNo = Me.txt1(Index - 4).Text
!DEPT = "1"
!AMOUNT = Me.txt1(Index).Text
!LIMIT = Me.txt1(Index - 1).Text
.Update
End With

In the Access 2000 database I have assigned datatype NUMBER to all fields. As you can see I am using text boxes to fill many of the database fields. When I have no numbers in the text box but a "" I get a type mismatch. How do I handle that situation?

I also have the same situation with text boxes bound as part of a binding collection. When I try to update the binding collection I get the same type mismatch error.

Any help is appreciated.

TNN, Tom
TNPAYROLL@AOL.COM



TOM

http://WWW.TNPAYROLL@AOL.COM

 

[MLK]

Have you tried putting zero in the textboxes with no values, instead of "". MLK

P.S. Make the Default value in those fields 0 in the database. MLK

 

[dpaulson]

With objSource.adoRsPyrlCalc4Ded
.AddNew
!EMPNO = strEmployee_Number
!DedNo = val(Me.txt1(Index - 4).Text)
!DEPT = 1
!AMOUNT = val(Me.txt1(Index).Text)
!LIMIT = val(Me.txt1(Index - 1).Text)
.Update
End With

Textboxes are strings values. Allways convert these to numbers when applying to a database. Anything surrounded with the quotaion marks are strings. You cannot assign a string to a number field in a database.

David Paulson

 

[WHM]

One further refinement should make all those nasties go away:

With objSource.adoRsPyrlCalc4Ded
.AddNew
!EMPNO = Val(strEmployee_Number & " &quot
!DedNo = val(Me.txt1(Index - 4).Text & " &quot
!DEPT = 1
!AMOUNT = val(Me.txt1(Index).Text & " &quot
!LIMIT = val(Me.txt1(Index - 1).Text & " &quot
.Update
End With

Val(String) does not always handle nulls correctly. By adding the space (" &quot, you ensure that you are not asking for the value of a null.

Hunter

 

[TNN]

Hello MLK,
I was wanting to leave the text box blank, nothing in it as I was also wanting the database field blank, nothing in it. Looks like this isn't possible with a NUMBER data type.
Thank you for your input.
TNN, Tom
TNPAYROLL@AOL.COM



TOM

http://WWW.TNPAYROLL@AOL.COM

 

[TNN]

Hello David,
I was wanting to leave a blank nothing in the text box but doesn't look like I can do that.
Thank You for your input.
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[TNN]

Hello WHM Hunter,
Your suggestion works good but I still end up with a zero in my database field and the zero appears in the text box when I load from the database. I would like to keep the field and text box blank nothing in it.
Thank You for your input.
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[TNN]

Hello All,
The following If, Else works but is awful cumbersome(lot of code):

With objSource.adoRsPyrlCalc4Ded
.AddNew
!EMPNO = strEmployee_Number
!DedNo = Me.txt1(Index - 4).Text
!DEPT = "1"
!AMOUNT = Me.txt1(Index).Text
If Me.txt1(Index - 1).Text = "" Then
!LIMIT = Null
Else
!LIMIT = Me.txt1(Index - 1).Text
End If
.Update
.MovePrevious
'MovePrevious needed to save record and MoveNext to make record current in order
'to Bkmrk per below.
.MoveNext
End With

TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[dpaulson]

You can shorten it up a bit.

!LIMIT = iif(Me.txt1(Index - 1).Text = "", Null, val(Me.txt1(Index - 1).Text)




David Paulson

 

[TNN]

Thank You David,
I had never used the "IIf" before but sure will from now on.
Thank you for the tip.
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[BigDaddy]

One thing to be careful of fella's. When using the val(NumberAsText) function is that it will convert the numeric representation of the text until it hits a non-numeric character. For most values this should be okay but I believe that the character "," is non numeric. The conversion would stop there. You would be better off using the set of explicit conversion functions cdbl, csng, cdate, etc...

So if you had a text box Text1 with the text 1,115.23 the following would occur

'Command1_Click would return a message box with the value of 1
Private Sub Command1_Click()
MsgBox Val(Text1.Text)
End Sub

'Command1_Click would return a message box with the value of 1115.23
Private Sub Command2_Click()
MsgBox CDbl(Text1.Text)
End Sub

 

[TNN]

Thanks BigDaddy,
Advise taken. Thank you for pointing that out.
TNN, Tom
TNPAYROLL@AOL.COM


TOM

http://WWW.TNPAYROLL@AOL.COM