Inconsistent query results

Question

I have a query that builds a cursor to create invoices. Part of it is the expression "IIF(cuPR.curren="EUR",NULL,rate) AS Taux". My problem is that my query works fine for January through April and for June, but not for May. I checked the query to identify the problem, I checked and rechecked my data, everything looks fine. The data being the only thing that changes, what else should I check, please??

Answer 1

Upon further research I noticed that in May the first record in the cursor cuPR had "EUR" as "curren". I tried sorting my cursor by "curren DESC", making sure EUR would not be in the first record (USD and GBP are other possible values) and my query went through. DRapp had given the explanation in response to a previous question of mine: "Bernard (and others new to VFP). VFP queries actually run the query twice, once for the first record just to confirm the final column types and sizes, then for the actual query of ALL records." In my case one of the columns was NULL...

Answer 2

Usually if there exists a possibility that I will have data that may throw things off like you have here I will use a cast to ensure my field is what I expect.

Something like...

SELECT CAST(CAST(IIF(cuPR.curren="EUR", NULL, rate) AS Numeric(10,5)) AS Taux ...