Query Doesn't Return All Results (Base)

English
,
#1

Using 7.6.2.1 in Windows

Been working on getting some queries and forms setup and I’ve run into a problem.

Example: I want to see every record in which “Hanks” appears in the Owner field. So I setup the query to do just that. It returns some of the fields, but not all.

If I run the query, I get 112 different records. If I run it within a form, I get 42.

Comparing the results from each, it appears the form is only returning where “Hanks” is the first part of the Owner field and not the second.

Example: Hanks, Larry appears but Williams, Roger Hanks does not. Or if there are multiple owners.

Example: Hanks, Larry appears but Hanks, Larry and Deloris; Roberts, John and Beth does not.

Any ideas? The form was made using the wizard and it is tied to the same query.

#2

You have set the same query as data source for the form? If it shows ‘42’: is this really the last row? Isn’t it ‘42*’, which shows: There are more rows, but form has only be loaded 42 rows in cache?

Post the SQL-code for the query here.

#3
#4

When I create the form there is a dialog box that wants me to select the fields in the form. There’s a drop down for tables or queries, I am selecting the query I mentioned above.

And yes, it is the last row. I cannot scroll down, sideways, anything. It’s only loading the 42 records that have the owner field as I mentioned above.

The code for the query is as follows.

SELECT * FROM “records” WHERE “Owner” LIKE ‘%a%’ AND “Owner” LIKE ‘%’ || :Enter_Name || ‘%’

#5

What do I do with that? Directions are a key part of offering assistance.

#6

My form “Filter Form” gets the resulting records from query “Query_Filter” which is a parameter query.

FROM "STRASSEN" 
WHERE UPPER ( "Name" ) LIKE UPPER ( '%' || :Contains ) || '%' OR :Contains IS NULL 
ORDER BY "Name" ASC```

It selects all columns ( * ) from the table “STRASSEN” where the upper cased street name is like the parameter or all records when the parameter is not given.
WIthout the form you can test the query by double-clicking on it. You will be prompted for the parameter.
In the form, the parameter is substituted by the parent form’s value of “CHARS” where the search expression has been entered. See data properties of the subform.

You don’t tell us anything about your approach, let alone any document which would tell us exactly why you fail to do what you want.

#7

This didn’t solve the problem. I still get all 112 results when I run the query as a query, but only the 42 when I run the quest via the forum.

I told you what I was doing, and what was happening. I run a query and get one set of results, I use a form to run the same query and I get a different set of results. I told you the results I expected to get, the results I actually get, and the records that are being left out of said results when ran via the form and not as a direct query.

#8

Please upload a file. Shouldn’t contain personal data. But should show different behavior of query and form, depending on the query. You didn’t set a filter to the form?

#9

I’ll have to go host it somewhere. My 1.4 mb seems to be too large for the 4 mb file max.

Query I’m working with is “OwnerLookup”
Form is, currently, “2”

The form is running the same script that the query is running. Open the query, it prompts for a name. Enter name, get results.

Open the form, prompts for a name. Enter the same name as above, get different results.

#10

This is because you did a spreadsheet dump with default settings, highly inefficient and bloated.

#12

1.4 mb is less than 4 mb no matter what I did.

#14

@jparton : Couldn’t get the uploaded file. Only an error on the page…

#15

Took it down. As per usual, I’m not getting anywhere here. I appreciate the attempt though.

#16

Maybe check roberts question above:

Some time ago I had 2 tables with identical setup, but the “archive”-table did not show newer data. Turned out I set a filter, wich I seldom do. I only realized the next time I wished to set a filter there. Since then I clear filters before saving.

#17

By any chance, does the form have a filter set on any of the fields that might limit the displayed result set?

#11

You need to change from “NameLookUp” to “OwnerLookup” query in the form.

#13

The form was built off the query. I didn’t make the form and then direct it to the query so it’s not looking for “NameLookUp” anyway.

Even if that were not the case, it’s still not operating as it’s supposed to be because there are other records that would appear in the results.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.