Report Calculations

[Clownfish1980m]

I have a database containing two fields; one is “1st Tx Date” the second is “Contacted Date”. When an individual received a product the 1st TX Date field has a date input. Once oral confirmation is made they have received the product, a date is added to the Contacted Date. On a report I have two text boxes. The first is called Notification Time (Days), field name (Text107) and in that field I have it calculate the amount of time from 1st Tx Date to Contacted Date (I set the Control source to: =[Contacted Date]-[1st Tx Date]). This field works great. No issues. The second text box called “Text109” is where I want to calculate the date difference between Now() and 1st Tx Date when there is no Contacted Date entered. This tells me how many days have lapse between the 1st Tx Date and today. The coding I am trying is:

If [Contacted Date] = Null Then
[Text109] = "NA"
Else
[Text109] = Now() - [1st Tx Date]
End If

I have tried it under On Got Focus, On enter, On Format, on Mouse Move and cannot get it to work. Is there a way to get it to work automatically when the report is opened? Thanks Ken

 

[spikepl]

Null is not a value you can compare with anything using = <>

Use
If IsNull(value_to_be_tested_for_null) THEN

 

[spikepl]

Besides: when sorting out stuff like this, then step through it using the debugger, see where the logic fails, and that would normally zero you in on the problem.

And since we are at it: do not use field or table names containing spaces. Sooner or later that is bound to create problems, or else you have the joy of dragging square brackets with you all over the place.)

 

[Clownfish1980m]

I got it backwords in the original part but this is what I tried and I get Compile error: syntax error.

Private Sub Text109_Enter()
If IsNull (Contacted Date) Then
[Text109] = Now() - [1st Tx Date]
Else
[Text109] = "NA"
End If

 

[spikepl]

IsNull is a function with an argument:

If IsNull (Contacted Date) Then

->
If IsNull([Contacted Date]) Then

and my prediction about spaces in names causing you trouble has already come true. Get rid of spaces, or replace them by underscores _

 

[Clownfish1980m]

We are getting closer. I can now open the report it is blanks. When I clidk on the text box and it changes to "NA" even if there is a contacted date there. If I click it again it changes to the Now()-1st Tx Date value. What event should I put the coding under? I have under On Enter. Ken

 

[spikepl]

The report's OnOpen event.

The textboxes fire events only if the user clicks on them

 

[Clownfish1980m]

Here is what I have

Private Sub Report_Open(Cancel As Integer)
If IsNull([Contacted_Date]) Then
[Text109] = Now() - [1st Tx Date]
End If

I figure that if there is a contact date than the field will be blank. Ken

 

[spikepl]

Errr ... .hallelujah? :-D