Says query is too complex.

[soorags]

SELECT [UK Table].[Business Name], [UK Table].[Business Type], [UK Table].Address, [UK Table].City, [UK Table].Country, [UK Table].[Telephone Number], [UK Table].[Website Address], [UK Table].[Email Address]
FROM [UK Table]
WHERE ((([UK Table].[Business Name]) Like [Forms]![frmNz]![txtBusinessName] & '*' Or [Forms]![frmNz]![txtBusinessName] Is Null) AND (([UK Table].[Business Type]) Like [Forms]![frmNz]![txtBusinessType] & '*' Or [Forms]![frmNz]![txtBusinessType] Is Null) AND (([UK Table].Address) Like [Forms]![frmNz]![txtAddress] & '*' Or [Forms]![frmNz]![txtAddress] Is Null) AND (([UK Table].City) Like [Forms]![frmNz]![txtCity] & '*' Or [Forms]![frmNz]![txtCity] Is Null) AND (([UK Table].Country) Like [Forms]![frmNz]![txtCountry] & '*' Or [Forms]![frmNz]![txtCountry] Is Null) AND (([UK Table].[Telephone Number]) Like [Forms]![frmNz]![txtTelephoneNumber] & '*' Or [Forms]![frmNz]![txtTelephoneNumber] Is Null) AND (([UK Table].[Website Address]) Like [Forms]![frmNz]![txtWebsiteAddress] & '*' Or [Forms]![frmNz]![txtWebsiteAddress] Is Null) AND (([UK Table].[Email Address]) Like [Forms]![frmNz]![txtEmailAddress] & '*' Or [Forms]![frmNz]![txtEmailAddress] Is Null));

What is wrong? I have attached the database. The form frmNz is what I want to work. I should be able to enter company information into at least one field, allowing me to retrieve the specific company data from the UK Table in the subform.

Gurdip.

 

[phatnq2002]

Here is my work. (see file attached)

Hope this helpful for you.

 

[The_Doc_Man]

I don't think "Is Null" is the correct syntax.

Isn't it more like IsNull([{some-object-name}]) ?

I'll be honest, I'm not familiar with the Is {space} Null syntax. Doesn't prove anything, of course... I'm noted for being behind the times on a lot of things.

In any case, the parentheses look a little funky to me. I'm not sure how they balance out but at first blush, the balance of parentheses doesn't SEEM to match the items with similar names. That might be the cause of your complexity.

 

[dcobau]

Is Null works in queries, IsNull works in VBA. When I tried IsNull in queries the results were erratic, sometimes correct other times not so. However, after many years and version of Access I have had to change some queries to

If ([FieldName] Is Null or [FieldName] = "", etc

Dave

 

[soorags]

phatnq2002, your work is good. It does the job but I cannot edit it and save changes, etc. It is READ-ONLY unfortunately. Do you know a way around this?

 

[soorags]

phatnq2002,

I copied your SearchResults form onto my database. How do I integrate it onto my frmNz form? Do I just set the subform's source object to that form?

 

[soorags]

When I tried this first and then used the form it wasn't working properly. The form was not searching correctly.

 

[phatnq2002]

When I tried this first and then used the form it wasn't working properly. The form was not searching correctly.

Check to be sured the fieldnames are correct.
The SearchResults form and the FormNz form are yours. I only edit something.

Can you send me your newest file?

 

[soorags]

This is my latest file. I have copied the database to a new one. From another forum I was told that I should use filter coding and have done so for the 'Submit Information for Search' command button (On Click event). I have also changed the subforms source to 'UK Table'. However the coding that I have used for the command button is not correct and needs altering. This is the new problem.

 

[phatnq2002]

Dont get a table directly to use as a Source Object of a subform.
You make a form (named frmSearchResults or else) that has a RecordSource - query2 (Default View: Datasheet, Allow Additions: No, ...)

In formNz, change the Source Object to frmSearchResults.
In code, remove Me.SearchResults.RowSource = sCustSearch
and add
Me.SearchResults.Form.RecordSource = sCustSearch
Me.SearchResults.Requery

 

[soorags]

Replicas of a form

On form frmNz I have changed the source object to frmSearchResults, and the source for this form is qryNew, not Query 2. The coding is for Query 2, but the frmNz form still works the way that I want. If there is anything wrong with this please let me know.

Now that frmNz does work, I would like to make 2 replicas of the form that I can alter slightly for different uses. How can I make 2 exact replicas of frmNz? I have tried pasting 2 copies but this method results in 2 copies that aren't exact. There is an attachment.

Gurdip.

 

[neileg]

I can't see how the copies are different.

 

[soorags]

The two copies of the frmNz form do not work. That is the problem.