Complicated query

[bibbyd01]

Hi all

I’m struggling on building a working report that filters out based on a calculated field. Access believes this to be because the calculation is too complicated. I will try and explain why (apologies for the length of this).

The data is populated based on a customer table which includes the fields customer number (numeric), agency number (numeric) and owner (text). Both customer number and owner are required fields, but agency number is not. The agency number and customer number could be the same (the agency could also be a customer). I have used Dlookup to populate a calculated field called agencyowner into the query.

Agencyowner: DLookUp("Owner","tbl_Customers","Customernumber = " & [Agency Number])

This is because each customer under the agency can have a different owner, but I need them to group under the agency owner. When I run the query I can see that the AgencyOwner field is giving the correct results.

When I use a the ‘stLinkCriteria’ from the event, it comes up with a message ‘this expression is typed incorrectly, or is too complex to be evaluated’.

The expression is

stDocName = "Rpt_Agency_Summ"
stLinkCriteria = "[AgencyOwner]=" & "'" & [Forms]![Menu]![CbxCC] & "'"
'DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

CbxCC is a combo box with the list of Owner names.

The next thing I tried was to link the criteria of the query to the form control, but this resulted in a similar problem. I don’t want to create a new table or extra fields if I can help it as there’s a lot of records to update and it would confuse the people having to maintain the database. It there another option I haven’t considered.

 

[CJ_London]

This error message often comes up when there are nulls in the data - in a criteria all records will be looked at and if a null is encountered it will fail.

Do you have any records in tbl_customers where agency number is null?

Alternatively it may be as simple that you have not selected someone in your CbxCC combo.

Also it looks like you are 'converting' agency numbers to onwners names in the combo and also converting it in your query. If so, there is no need to do this.

Have your combo box rowsource return agency number and owner name, set the column count to 2 and enter 0 for column widths

Then your criteria would simple be
"[Agency Number]=" & CbxCC

and you don't need the dlookup in your report recordsource (unless you are using it in the report).

One other thought - if you are building the critera in vba, you don't need to reference the form since you are already there so all you need is

stLinkCriteria = "[AgencyOwner]='" & CbxCC & "'"

Haven't tested it, but this may also be causing the problem

 

[bibbyd01]

Hi

Thanks for your reply.

I do have rows where the agency number is null, but these are excluded from the query. There’s no way around that as not all customer are or have an agency.

The cbxCC is selected so not the problem (unfortunately).

An owner could have multiple agencies (in this case, owner is the person responsible for a set of customers/agencies). Therefore in the report, I need to select all agencies, with their customers, where the agency owner matches what’s in CbxCC.

I have also tried stLinkCriteria = "[AgencyOwner]='" & CbxCC & "'" but this doesn’t work either. I know this is causing the problem because when I remove the criteria the report runs correctly.

 

[CJ_London]

How are you excluding them from the query? You may need to do this first before using Dlookup - which will return null if it doesn't find a match.

Might be an ide to post your whole SQL rather than just bits

have also tried stLinkCriteria = "[AgencyOwner]='" & CbxCC & "'" but this doesn’t work either. I know this is causing the problem because when I remove the criteria the report runs correctly.

OK, so either [AgencyOwner] is null or CbxCC is null

Can you try
"[AgencyOwner]='SomeValue'
where somevalue is a known value which will return results

then try "nz([AgencyOwner])='" & CbxCC & "'"

If this works then [AgencyOwner] has null values