No current Record error message - help!!

[mikeTTI]

I have a query:

SELECT tblContacts.Name, tblContacts.[Mussel Processor], tblContacts.[Salmon Processor], tblContacts.[Oyster Processor], tblLevyReceiptsHeader.AutoID, tblLevyReceiptsHeader.Paid, tblLevyReceiptsHeader.DateAdded
FROM tblContacts LEFT JOIN tblLevyReceiptsHeader ON tblContacts.ID = tblLevyReceiptsHeader.Processor
GROUP BY tblContacts.Name, tblContacts.[Mussel Processor], tblContacts.[Salmon Processor], tblContacts.[Oyster Processor], tblLevyReceiptsHeader.AutoID, tblLevyReceiptsHeader.Paid, tblLevyReceiptsHeader.DateAdded
HAVING (((IIf([Forms]![LevyReportDialog]![spp]="SAL",[Salmon Processor]=True,True))=True) AND ((IIf([Forms]![LevyReportDialog]![spp]="GSM",[Mussel Processor]=True,True))=True) AND ((IIf([Forms]![LevyReportDialog]![spp]="OYS",[Oyster Processor]=True,True))=True));

(Sorry, the query is too wide in design grid to capture a screen shot of)

It is sopposed to select all records for the user determines one of three fields = true. The field that must = true is determined by the value of the [Spp] list box on a dialog form.

For example if [Spp] = SAL, the query selects all records for which [Salmon Processor] = True, regardless of whether [Mussel Processor] and/or [Oyster Processor] are true or false for those records.

Basically it compiles a list of all Salmon Processors, to feed into another report.

The selection process is controlled by three IIF functions that appear after HAVING in the SQL above.

This query has been working fine for several months. Today (report day) it has stopped working and returns the error message.

"No Current Record" (Error 3021).

It does this only if OYS or SAL are selected in [Spp] on the dialog form. It works fine if GSM is selected. There are records it should be selecting in each case. Oddly these two selections return the smallest recordsets

I can't figure this out. Any help form someone with more experience would be greatly appreciated. I need to run these monthly reports today!!

 

[raskew]

Hi -

Here's a formatted version of your code.

What happens if you run the query, minus the three Iif() statements?

SELECT
    tblContacts.Name
  , tblContacts.[Mussel Processor]
  , tblContacts.[Salmon Processor]
  , tblContacts.[Oyster Processor]
  , tblLevyReceiptsHeader.AutoID
  , tblLevyReceiptsHeader.Paid
  , tblLevyReceiptsHeader.DateAdded
FROM
   tblContacts 
LEFT JOIN
   tblLevyReceiptsHeader 
ON
   tblContacts.ID = tblLevyReceiptsHeader.Processor
GROUP BY
   tblContacts.Name
  , tblContacts.[Mussel Processor]
  , tblContacts.[Salmon Processor]
  , tblContacts.[Oyster Processor]
  , tblLevyReceiptsHeader.AutoID
  , tblLevyReceiptsHeader.Paid
  , tblLevyReceiptsHeader.DateAdded
HAVING
   (((IIf([Forms]![LevyReportDialog]![spp]="SAL",[Salmon Processor]=True,True))=True) 
AND
   ((IIf([Forms]![LevyReportDialog]![spp]="GSM",[Mussel Processor]=True,True))=True) 
AND
   ((IIf([Forms]![LevyReportDialog]![spp]="OYS",[Oyster Processor]=True,True))=True));

Bob

 

[mikeTTI]

Hi Bob,

Thanks for your help.

Interestingly I tried this. It still brings up the error, even when those IIF expressions are removed from the query.

This suggests it is not the conditions that are problematic, yet the query works for one of the three values that relate to those conditions.

Since my post I have managed to avoid the error message by changing the join type.

From "Type 2 "
All records from tblContacts and only those records from tblLevyReceiptsHeader where the joined fields are equal

To "Type 3 "
All records from tblLevyReceiptsHeader and only those records from tblContacts where the joined fields are equal

This is odd because it has been a type 2 join since I built the database, and has worked fine until today and even then, only failed for two of three possible values in the dialog box field.

It seems to be working now, but I don't know if I will get the same dataset with a a different join type. I have someone manually checking this against last months report and subsequent paper records.

 

[WayneRyan]

Bob,

It's been a long day here. Haven't read the whole thread, but I'd venture to guess
that --> In your tblContacts

One of your [Salmon Processor], Mussel Processor] or Oyster Processor] Is Null.

Otherwise, I'd have to see some data.

Wayne

 

[mikeTTI]

Hi Wayne,

Just had a look at this. Can a Yes/No filed be null and if so how do I tell it is null?

Cheers

Mike

 

[raskew]

Hi Wayne -

Think you 'hit the nail on the head'.

Are you, in scenic Camarillo, getting hit by the winds? My sister, in Santa Barbara, says 'the sky is falling'.

Best wishes -

Bob

 

[WayneRyan]

Mike,

If a boolean has no default value, and nothing is entered, it'll be Null.
You can set a defalut value in the table design.

Bob,
Actually, it's a great looking day outside. I'm sick on this holiday so
I won't see any of it, but it looks nice outside. Tomorrow I'll get to
see it from work.

See ya,
Wayne

 

[mikeTTI]

I have realised I do in fact need to use a type 2 join to get the data I want. (All records from tblContacts and only those records from tblLevyReceiptsHeader where the joined fields are equal)



Thanks to all the help I have received from this forum by trial and error and removing fields from the query, I have realised it is tblLevyReceiptsHeader.[Paid] that is causing the problem.

If it is in the query, I get the no current record error. If I leave it out I don't.

[Paid] is a yes/no field. It does have a default value which is set to False. It may still be possible that there are Null values in this field. If so how would I find them?

 

[raskew]

Hi-

Please show the code that sets the default value of .Paid.

Bob

 

[mikeTTI]

No code. It is set in the Default Value property of the Table tblLevyReceiptsHeader.