Type mismatch between report control and table field (1 Viewer)

GoodyGoody

Registered User.
Local time
Today, 17:42
Joined
Aug 31, 2019
Messages
120
Hi, I have a report in which I want to save the data in a field on the report detail line which is numeric to a table field which is also numeric. Access is producing error 3164 which I assume is a type mismatch. Can someone tell me the function I need to use to convert the data in the report control to a numeric field that the access table recognises?

Thanks
Stephen
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:42
Joined
May 7, 2009
Messages
19,169
how are you saving it?
you can't save on same table while the report is displayed.
Code:
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset)
        If Me.FilterOn = True Then
            rs.Filter = Me.Filter
            Set rs = rs.OpenRecordset
        End If
        With rs
            If Not (.BOF And .EOF) Then .MoveFirst
            While Not .EOF
                currentdb.execute "insert into table (field1) select " & ![fieldOfReport] & ";"
                .MoveNext
            Wend
        End With
        rs.Close
        Set rs = Nothing
 

Micron

AWF VIP
Local time
Today, 13:42
Joined
Oct 20, 2018
Messages
3,476
Not enough info. Is the report field data calculated? You are then trying to save that calculation somewhere? If so, let's start with the premise that saving calculated data is a fundamental no-no for 99% of cases, so this could possibly be a non-starter.

If this is one of those 1% cases, then is the field in the target table a calculated field itself?
 

GoodyGoody

Registered User.
Local time
Today, 17:42
Joined
Aug 31, 2019
Messages
120
I took out the offending null field and it saved the rest of the data to the 'current' record including fields I was printing on the line as well as the hidden fields I created that were just marked as 'Not Visible'. Thanks again for all your help getting me back up to speed on Access after 15 years out of the game.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:42
Joined
Aug 30, 2003
Messages
36,118
you can't save on same table while the report is displayed.

Under what circumstances? I've got an app with a report open in report view that has code updating the underlying data and requerying the report. Reports are read-only so I wouldn't think having one open would lock the table.
 

GoodyGoody

Registered User.
Local time
Today, 17:42
Joined
Aug 31, 2019
Messages
120
Yep, PBoldy that is my experience. In the following code I am getting the error 'invalid use of Null'. Can anyone explain why? The field does have Null in it:

With rs1
.Edit
![RacePosition] = CInt(Me.Position.Value)
![RaceCategory] = Me.Category
![RaceAge] = Me.Age
![RaceGender] = Me.Gender
![RaceGenderPosition] = CInt(Me.GenderPos)
![RacePercWR] = Me.PercWorldRec / 100
![RacePBTime] = Me.RunnerPB
![RacePB] = boolPBYesOrNo
![RaceCR] = boolCRYesOrNo
![RaceTotalRuns] = Me.TotalRuns + Me.TotalRunsCalc
![RaceClub] = Me.Club
![SeriesID] = IIf(IsNull(Me.SeriesID), 0, CInt(Me.SeriesID))
.Update
End With
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:42
Joined
Aug 30, 2003
Messages
36,118
On what line? Either a table field doesn't allow Null or one of the CInt() functions is hitting a Null value. If so, you can use the Nz() function.
 

Micron

AWF VIP
Local time
Today, 13:42
Joined
Oct 20, 2018
Messages
3,476
That's a new error. Probably you are trying to convert nulls: CInt
You're still holding out on info when you ask a question
 

GoodyGoody

Registered User.
Local time
Today, 17:42
Joined
Aug 31, 2019
Messages
120
Sorry, the ![SeriesID] = IIf(IsNull(Me.SeriesID), 0, CInt(Me.SeriesID)) line gives the error
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:42
Joined
Aug 30, 2003
Messages
36,118
I've seen times when the false part of the IIf() was evaluated even when the true argument was met. Try:

Nz(Me.SeriesID, 0)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:42
Joined
Feb 19, 2002
Messages
42,971
The IIF() works differently in SQL than it does in VBA. pbaldy identified the problem but didn't explain it correctly. It is not a "sometime" condition, it is a known condition.

In VBA, all clauses of the IIF() are evaluated whereas in SQL, only the true path is evaluated.

The solution in VBA is to use If Then Else rather than the IIf() function. Only the true path of the If Then Else is evaluated. The Nz() happens to solve this particular problem but it will not solve all problems.

Please test this and confirm for yourself.

It is particularly important to understand this anomaly in order to avoid the dreaded divide-by-zero error in VBA.
 

Users who are viewing this thread

Top Bottom