Q. Searching for one field for "0"s and another for spaces

[JimJones]

Hi.

I have created a query, which draws from several tables.

One of these tables is a pop up form, where details are entered (the pop up form draws from a query also, not directly from a table, as I've been advised against).

The results in the pop up form link to a subform (on which I have the cmd button to open the pop up form).

The data I can see in my pop up form is plagued by something that is not a problem in the query. That is, the standard next record button acts also as a new record button, and I'd like to prevent that from happening, because it's easy to enter new records, with no data. (the standard next record button also has the word (filtered) next to it, and I'm sure that's part of the problem.
But, I don't know how to get rid of it, and to make the recordset stop, when go to the end of the record set, and dim the next record arrow.

This is partly because there is code to enter the primary key into the table, as it is linked to the subform.

Now, one way I could deal with it is to create a delete query to delete the "extra" records created in the table, via the pop up form's query, if the user continually/inadvertently presses the next record key, which doesn't stop at the end of the record set.

The problem I'm having is that in the delete query, I can't seem to specify if a certain field is blank. I can specify if a certain field has a "0", but not spaces.
The extra records that are created contain a field with spaces and a field with "0"s, but I don't know how to make it show the certain records when I specify: Like" ", along with ="0"

Please explain how I can do this.

Thanks,
Jim

 

[a.sinatra]

...The data I can see in my pop up form is plagued by something that is not a problem in the query. That is, the standard next record button acts also as a new record button, and I'd like to prevent that from happening, because it's easy to enter new records, with no data. (the standard next record button also has the word (filtered) next to it, and I'm sure that's part of the problem...

Form property sheet->
Allow Additions = No
Data Entry = No

...but I don't know how to make it show the certain records when I specify: Like" ", along with ="0"...

Like "" And "0"
________
DODGE SRT-4

 

[JimJones]

Form property sheet->
Allow Additions = No
Data Entry = No



Like "" And "0"

Hi, thanks, but I do need to be able to add a new record when needed.
What you instructed works for the next record button, but dims the new record button.

Please help,
Thanks,
Jim

 

[Pat Hartman]

NEVER use Like for a numeric field. According to help you can get erroneous results since numeric fields are not text strings.
NEVER use Like when you have a complete value for a text field. The relational operator Like is intended to be used in conjunction with one or more wildcard characters. The use of "Like" prevents Jet from using an index to satisfy your search even when a complete value is entered and/or no wildcard characters are specified. This makes the use of Like against large datasets very slow.
NEVER surround numeric values with quotes.

Your "blank" field is probably null and so you should use Is Null to test for it.

To test for 0 or null in the same field -

= 0 OR Is Null

AND, as a.sinatra suggested would never work since a single column could not be two different values at once.