Report based on query - no fields (1 Viewer)

[gazsharpe101]

Hi everyone,

I have a report that is based on a query.

Some of the fields in the queries are dates (i.e. 'date1' and 'date2') , and on the report I want to do a datediff to show how many days there are between 'date1' and 'date2' and for this value to be placed into a text box on the report.

Please note that not there won't be entries in every single 'date1' and 'date2' field. If there are dates missing, then I want the text box on the report to equal zero.

In VBA on the 'detail_format' section, I have dimmed the following:

Dim daysdiffbetweendates as Integer

I then use the code:
daysdiffbetweendates = DateDiff("d", date1, date2) obviously where 'date1' and 'date2' are being pulled through from the query.

txtdaysdiff = daysdiffbetweendates

But as soon as it comes across a date in either of the fields that hasn't been entered, it throws up an error and when I put my cursor over 'date1' for example, it says cannot find field or something like that.

Is there anyway that I can make it so that if either of the two fields hasn't got a date in then i can make the textbox = 0?

I have tried using the datediff as an expression in the query but it didn't work so that is why I tried to use it in VBA, which is working more than before but I now get the 'Run-time error 2465'.

Does anyone know a way around this? I was thinking similar to the Nz function on a null field, but obviously this is being unable to trace a field so I assume it is different.

Thanks for any help.
Gareth.

 

[RuralGuy]

If IsDate([Date1]) AND IsDate([Date2]) Then
...do the calc
Else
...YourControl = 0
End If

 

[gazsharpe101]

Thanks for your reply.

I have done a similar thing but using IsNull will that work or should I change it to IsDate?

 

[RuralGuy]

IsDate() is more specific for your situation and will catch Null fields as well.