Types don't match

[Jean-Paul De Winter]

On my report I have a field that calculates an age:

=IIf([datum1]<[Geboortedatum];Format([datum1];"yyyy")-Format([Geboortedatum];"yyyy");(Format([datum1];"yyyy")-Format([Geboortedatum];"yyyy"))-1)

This works perfect

I have this other field that should display a value depending on this age
Ex: if the age is 13 the the value should be 244
age=12 value should be 255

First question....
The value changes on every new page... so I wrote:

Private Sub Report_Page()
If Me!leeftijd = 13 Then
Me!normaal = "244"
End If
End Sub

I get an errormessage saying the types don't match...

What am I donig wrong?

 

[Duane Hookom]

I suggest you use Year([datum1]) rather than Format([datum1];"yyyy") since
you are using this in numeric expressions.

Don't you have a small lookup table that relates 13 to 244 and 12 to 255 and
.....?

Is Me.Normaal a bound control? If it is numeric, you must remove the quotes
around 244.

 

[Jean-Paul De Winter]

I still get the same error
The code I wrote is:

Private Sub Paginakoptekstsectie_Format(Cancel As Integer, FormatCount
As Integer)
If Me!leeftijd = 12 Then
Me!normaal = 255
End If
If Me!leeftijd = 13 Then
Me!normaal = 244
End If
End Sub

the textfield now is created with following command:
=IIf([datum1]<[Geboortedatum];Year([datum1])-Format([Geboortedatum];"yyyy");Year([datum1]-Year([Geboortedatum]))-1)

Any help is welcome... thanks
JP


Duane Hookom schreef:

 

[Duane Hookom]

You still have Format([Geboortedatum];"yyyy") in your calculation....

You didn't say whether normaal is bound to a field and if it is numeric.

Which line causes the error?


--
Duane Hookom
MS Access MVP

I still get the same error
The code I wrote is:

Private Sub Paginakoptekstsectie_Format(Cancel As Integer, FormatCount As
Integer)
If Me!leeftijd = 12 Then
Me!normaal = 255
End If
If Me!leeftijd = 13 Then
Me!normaal = 244
End If
End Sub

the textfield now is created with following command:
=IIf([datum1]<[Geboortedatum];Year([datum1])-Format([Geboortedatum];"yyyy");Year([datum1]-Year([Geboortedatum]))-1)

Any help is welcome... thanks
JP


Duane Hookom schreef:

I suggest you use Year([datum1]) rather than Format([datum1];"yyyy")
since you are using this in numeric expressions.

Don't you have a small lookup table that relates 13 to 244 and 12 to 255
and ....?

Is Me.Normaal a bound control? If it is numeric, you must remove the
quotes around 244.

 

[Jean-Paul De Winter]

OK... this is the final calculation:
=IIf([datum1]<[Geboortedatum];Year([datum1])-Year([Geboortedatum]);Year([datum1]-Year([Geboortedatum]))-1)

Normal isn't bound to a field, it's a "stand alone" textfield

The error is in the line:
If Me!leeftijd = 12 Then

of following code:
Private Sub Paginakoptekstsectie_Format(Cancel As Integer, FormatCount
As Integer)
If Me!leeftijd = 12 Then
Me!normaal = 255
End If
If Me!leeftijd = 13 Then
Me!normaal = 244
End If
End Sub

Thanks again...
JP

Duane Hookom schreef:

 

[Duane Hookom]

Is this code running in a page event? It should be in the On Format event of
a report section.
Also, I think you have a ")" out of place on the third Year() function.


V
=IIf([datum1]<[Geboortedatum];Year([datum1])-Year([Geboortedatum]);Year([datum1]-Year([Geboortedatum]))-1)

You may need to wrap the entire expression above in Val() like:
=Val( IIf([datum1]<[Geboortedatum];....)

--
Duane Hookom
MS Access MVP

OK... this is the final calculation:
=IIf([datum1]<[Geboortedatum];Year([datum1])-Year([Geboortedatum]);Year([datum1]-Year([Geboortedatum]))-1)

Normal isn't bound to a field, it's a "stand alone" textfield

The error is in the line:
If Me!leeftijd = 12 Then

of following code:
Private Sub Paginakoptekstsectie_Format(Cancel As Integer, FormatCount As
Integer)
If Me!leeftijd = 12 Then
Me!normaal = 255
End If
If Me!leeftijd = 13 Then
Me!normaal = 244
End If
End Sub

Thanks again...
JP

Duane Hookom schreef:

You still have Format([Geboortedatum];"yyyy") in your calculation....

You didn't say whether normaal is bound to a field and if it is numeric.

Which line causes the error?

 

[Jean-Paul De Winter]

I changed it to Val.... nothing

Where to find the On Format event??
All I see is:
open
close
activate
deactivate
No Data
Page()
Error

(I have the dutch version of access)

Thanks again for your kind help!
JP

Duane Hookom schreef:

 

[Duane Hookom]

Those are the Report properties. You should click on the report section
containing the controls to find the appropriate place to enter your code.

--
Duane Hookom
MS Access MVP

I changed it to Val.... nothing

Where to find the On Format event??
All I see is:
open
close
activate
deactivate
No Data
Page()
Error

(I have the dutch version of access)

Thanks again for your kind help!
JP

Duane Hookom schreef:

Is this code running in a page event? It should be in the On Format event
of a report section.
Also, I think you have a ")" out of place on the third Year() function.


V
=IIf([datum1]<[Geboortedatum];Year([datum1])-Year([Geboortedatum]);Year([datum1]-Year([Geboortedatum]))-1)

You may need to wrap the entire expression above in Val() like:
=Val( IIf([datum1]<[Geboortedatum];....)

 

[Jean-Paul De Winter]

Sorry but I don't get that....
(damn... stupid me!!)
JP

Duane Hookom schreef:

 

[Duane Hookom]

In design view, double-click on the background of the section of the report
that contains you controls. This should display the section properties.

 

[Jean-Paul De Winter]

When I do so I get the "header"properties...
There 2 of which I can choose:
"format" and "print"
If I write my code in format I get the same errormessage as always....
This dumb, simple thing just drives me crazy!!
JP

Duane Hookom schreef:

 

[Duane Hookom]

Is it possible the leeftijd control is Null?

Can you do this without code by setting the control source of normaal to:

=IIf(leeftijd = 12, 255, IIf(leeftijd=13,244,Null))

 

[Jean-Paul De Winter]

this seems to work....
but...
leeftijd(=age) can be 12 13 or 14
and there is also a second condition that must be checked: sex!

example:
age 12 sex M = 281
age 13 sex M = 293
age 14 sex M = 292
age 12 sex F = 255
age 13 sex F = 244
age 14 sex F = 236

How to put this all into the codeline you send

Have the feeling the solution is close now
Thank you so much!!
JP, belgium

Duane Hookom schreef:

 

[Duane Hookom]

Since you are adding conditions and fields, I would move this whole issue to
a lookup table. This might get rid of all your code and complex expressions
and allow you to maintain this with data.