The Linux Page

As I added more complexity to the Query Builder in MS-Access, my fields were detached!

As I was working on a new Report, I had two fields with computations.

These are easy to handle, for example, for a Total you write something like this:

=[Quantity]*[Price]

And you get a product of the quantity and the price.

That works great. At first I had a very simple query and it looked like there was a problem in computed field, but I still decided to first add the WHERE clause to allow the user to limit the data to a given set of dates:

WHERE (((Invoice.CreatedDate)>=[Enter start date:])
   AND ((Invoice.CreatedDate)<=[Enter end date:]))

Notice all the parenthesis. Also the field is on the left hand side of the operators.

Once I had that in my query, all my fields got detached from the query. Clicking on the drop down I could see no fields at all, as if the query was all wrong or something.

The problem was actually the error! (it was showing as #Error in the report output)

I had a test to know whether I should use the quantity or zero (0).

IIf([SomeFlag],[Quantity],0)

However, instead of IIf(), I put 2x F: Iff(). [I'm not top notch in Visual Basic...] Until I fixed that error, I could not get the query to work. Took me nearly 2 hours to figure it out. Grrrr! Lack of proper error reporting and breakage of A while working on B just doesn't make it easy to work with such a system!