Date Range in Query returns Error in Report

[bethabernathy]

Hi There: I have a very strange problem. I am running a query that prompts you for a date range (i.e. - Between [Enter Start Date] And [Enter End Date]). Then I have designed a report that is based on this query. One of the calculations in the report is counting the number of ski accidents that involved Equipment that is Owned by the individual (rather than rented from the ski area).

Anyway, I was very very lucky to get some great help on the calculation of the value for the equipment owned text field on the report which is this:

=DCount("[EquipstatID]","qryincident","[EquipstatID] = 1&quot

Where [EquipstatID] is a lookup field with "Owned" = 1.

When I run the report without the date range in the query, I get the results of the Equipstat ID to display properly. When I add the date range into the query the value of the field gets an error (ie. #error).

Does this make any sense? Perhaps it is something to do with the syntax in the "=DCount" expression that is doing something.

Thanks again for all the AWESOME help on this forum. I have referred it to lots of great tech people.

Beth

 

[CosmoKramer]

Beth,

Could you post your SQL here so we could take a look at it??

 

[bethabernathy]

Hi Cosmo, I don't have it written in SQL just a plain query using the between statement. I have gotten somewhere with this. When I use the syntax:

=Sum([EquipstatID]=1)

In the unbound text box, I do get a result of -1. The look-up field value for "Owned Equipment" is 1. Now I need to set it up so that the value is not negative. This negative value thing is also strange, but have a work around (probably something you helped me with???) for my other calculations i.e.

=Sum([Photos])*-1

Which is returning the correct result (the above "Photos" field is a "Yes/No" field).

Do you know how I can get the value to be positive for the "EquipstatID" calculation?

Thank you for everything. Beth

 

[bethabernathy]

Hi There: I figured it out:

=Sum([EquipstatID]=1)*-1

Thank you for your help!!!

Beth

 

[CosmoKramer]

Beth,

Check out the Abs function. It returns the absolute value of a number:

=Sum(abs([EquipstatID]=1))

 

[bethabernathy]

Hi Cosmo: So, I tried this for another Yes/No field:

=Sum(Abs([Photos]=1))

And I am getting an incorrect sum? This seems like a better route than tricking the field -

=Sum([Photos])*-1

To make it work - What do you think?

Beth

 

[CosmoKramer]

Beth, sorry, my bad....If you are looking to count the number of Yes/No boxes that are checked:

=Sum(Abs([Photos]))

 

[bethabernathy]

Cosmo - Perfect!!! Thank you so much. Beth