Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

TYPE MISMATCHES USING ACCESS 2000 DATABASE 2

Status
Not open for further replies.
TNN

TNN

Programmer
Sep 13, 2000
417
0
0
US
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
One further refinement should make all those nasties go away:

With objSource.adoRsPyrlCalc4Ded
.AddNew
!EMPNO = Val(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

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

Hunter

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
You can shorten it up a bit.

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




David Paulson

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

2009luca
  • Locked
  • Question
graph with access database - im on vb6 2
Replies
10
Views
192
strongm
strongm
sal21
  • Locked
  • Question
ARRANGE this Access coe to vb6
Replies
4
Views
82
strongm
strongm
2009luca
  • Locked
  • Question
using edge instead IE
Replies
4
Views
81
sal21
sal21
R_a_i_n_e_r_8
  • Locked
  • Question
Compile error: Types of intolerance (need help)
Replies
3
Views
207
strongm
strongm
Niki_S
  • Locked
  • Question
How to read an excel file using VB 6?
Replies
17
Views
184
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top