3421 Data Type Conversion Error

S

Supe

I have an Access database for tracking my golf scores. Set up a form for
entering my scores where I hit a button to add the score after entering the
information. It works fine when I enter one score, but if I try to enter
another score right after I get a 3421 Data Type Conversion error. Code is
below. When I hit the debug error it brings me to the "temptable![SCRAMBLE
SCORE] = Nz(SCRAMBLESCORE)" line.


Private Sub AddRound_Click()
Dim temptable As DAO.Recordset
Set temptable = CurrentDb.OpenRecordset("tblData", dbOpenDynaset)
temptable.AddNew
temptable![GOLFMONTH] = SCOREMONTH
temptable![DAY] = DAY
temptable![YEAR] = YEAR
temptable![COURSE] = cbocourse
temptable![City] = City
temptable![State] = State
temptable![Par] = Par
temptable![HOLES] = HOLES
temptable![SELF SCORE] = Nz(SELFSCORE)
temptable![SCRAMBLE SCORE] = Nz(SCRAMBLESCORE)
temptable![SCRAMBLE PARTNER(S)] = SCRAMBLEPARTNER
temptable![TOURNAMENT] = TOURNAMENT
temptable![TOURNAMENT SCORE] = Nz(TOURNAMENTSCORE)
temptable.Update
temptable.Close

SCOREMONTH = ""
DAY = ""
YEAR = ""
cbocourse = ""
City = ""
State = ""
Par = ""
HOLES = ""
SELFSCORE = ""
SCRAMBLESCORE = ""
SCRAMBLEPARTNER = ""
TOURNAMENT = ""
TOURNAMENTSCORE = ""

End Sub
 
D

Douglas J. Steele

Assuming Scramble Score is a numeric field, try using the following instead:

temptable![SCRAMBLE SCORE] = Nz(SCRAMBLESCORE, 0)

If it's a text field, try

temptable![SCRAMBLE SCORE] = Nz(SCRAMBLESCORE, "0")

or

temptable![SCRAMBLE SCORE] = Nz(SCRAMBLESCORE, "")
 
O

Ofer Cohen

If you want to convert Null to zero, then specify

temptable![SCRAMBLE SCORE] = Nz(SCRAMBLESCORE,0)

If SCRAMBLESCORE field is a text type variant, and value in it is empty and
not null the Nz wont work and you'll get type conversion error
Try

temptable![SCRAMBLE SCORE] = IIf(Len(SCRAMBLESCORE & "")=0 ,0,SCRAMBLESCORE)
 
S

Supe

Believe that did it. Thanks.

Ofer Cohen said:
If you want to convert Null to zero, then specify

temptable![SCRAMBLE SCORE] = Nz(SCRAMBLESCORE,0)

If SCRAMBLESCORE field is a text type variant, and value in it is empty and
not null the Nz wont work and you'll get type conversion error
Try

temptable![SCRAMBLE SCORE] = IIf(Len(SCRAMBLESCORE & "")=0 ,0,SCRAMBLESCORE)

--
Good Luck
BS"D


Supe said:
I have an Access database for tracking my golf scores. Set up a form for
entering my scores where I hit a button to add the score after entering the
information. It works fine when I enter one score, but if I try to enter
another score right after I get a 3421 Data Type Conversion error. Code is
below. When I hit the debug error it brings me to the "temptable![SCRAMBLE
SCORE] = Nz(SCRAMBLESCORE)" line.


Private Sub AddRound_Click()
Dim temptable As DAO.Recordset
Set temptable = CurrentDb.OpenRecordset("tblData", dbOpenDynaset)
temptable.AddNew
temptable![GOLFMONTH] = SCOREMONTH
temptable![DAY] = DAY
temptable![YEAR] = YEAR
temptable![COURSE] = cbocourse
temptable![City] = City
temptable![State] = State
temptable![Par] = Par
temptable![HOLES] = HOLES
temptable![SELF SCORE] = Nz(SELFSCORE)
temptable![SCRAMBLE SCORE] = Nz(SCRAMBLESCORE)
temptable![SCRAMBLE PARTNER(S)] = SCRAMBLEPARTNER
temptable![TOURNAMENT] = TOURNAMENT
temptable![TOURNAMENT SCORE] = Nz(TOURNAMENTSCORE)
temptable.Update
temptable.Close

SCOREMONTH = ""
DAY = ""
YEAR = ""
cbocourse = ""
City = ""
State = ""
Par = ""
HOLES = ""
SELFSCORE = ""
SCRAMBLESCORE = ""
SCRAMBLEPARTNER = ""
TOURNAMENT = ""
TOURNAMENTSCORE = ""

End Sub
Click to expand...
Click to expand...
 
S

Supe

Spoke to soon. When I do this way, if I enter a score in one field the other
two fields appear as 0 in the table and report. Need those to appear as
blanks.



Ofer Cohen said:
If you want to convert Null to zero, then specify

temptable![SCRAMBLE SCORE] = Nz(SCRAMBLESCORE,0)

If SCRAMBLESCORE field is a text type variant, and value in it is empty and
not null the Nz wont work and you'll get type conversion error
Try

temptable![SCRAMBLE SCORE] = IIf(Len(SCRAMBLESCORE & "")=0 ,0,SCRAMBLESCORE)

--
Good Luck
BS"D


Supe said:
I have an Access database for tracking my golf scores. Set up a form for
entering my scores where I hit a button to add the score after entering the
information. It works fine when I enter one score, but if I try to enter
another score right after I get a 3421 Data Type Conversion error. Code is
below. When I hit the debug error it brings me to the "temptable![SCRAMBLE
SCORE] = Nz(SCRAMBLESCORE)" line.


Private Sub AddRound_Click()
Dim temptable As DAO.Recordset
Set temptable = CurrentDb.OpenRecordset("tblData", dbOpenDynaset)
temptable.AddNew
temptable![GOLFMONTH] = SCOREMONTH
temptable![DAY] = DAY
temptable![YEAR] = YEAR
temptable![COURSE] = cbocourse
temptable![City] = City
temptable![State] = State
temptable![Par] = Par
temptable![HOLES] = HOLES
temptable![SELF SCORE] = Nz(SELFSCORE)
temptable![SCRAMBLE SCORE] = Nz(SCRAMBLESCORE)
temptable![SCRAMBLE PARTNER(S)] = SCRAMBLEPARTNER
temptable![TOURNAMENT] = TOURNAMENT
temptable![TOURNAMENT SCORE] = Nz(TOURNAMENTSCORE)
temptable.Update
temptable.Close

SCOREMONTH = ""
DAY = ""
YEAR = ""
cbocourse = ""
City = ""
State = ""
Par = ""
HOLES = ""
SELFSCORE = ""
SCRAMBLESCORE = ""
SCRAMBLEPARTNER = ""
TOURNAMENT = ""
TOURNAMENTSCORE = ""

End Sub
Click to expand...
Click to expand...
 
Top