Access where is blank

I tried Not "" in the criteria row for that field, but the results view still shows records where that field is blank. It is my understanding that this is a case of a blank string, not a Null value. Would appreciate any assistance. Have you tried a criterion. Whence is your "understanding that this is a case of a blank string" - where is the data coming from? Was this reply helpful? Yes No. Sorry this didn't help. Thanks for your feedback.

Records with that field blank still display when the query is run. My comment about "blank string" is a result of trying to figure out how to eliminate records with a specific empty field. It repairs corrupt. When trying to open an Access database form, you may see a blank screen. You can resolve the problem by ensuring that these two conditions are not met simultaneously.

Home Databases Microsoft Access How-tos. Microsoft Access. Priyal Stellar Info Tech This person is a verified professional. Verify your account to enable IT peers to see that you are a professional. Jun 04, 2 Minute Read. Reply 0. Facebook Twitter Reddit LinkedIn. QP I have added a query Q1 that lists all purchased items in categories that are insurable, so it includes types that are not insurable. PS There are non-insurable items that don't have a type as well, if that matters.

Is T1 and QP list of specific items or a list of types of items? If it is specific items then a query of QP to T1 joined by item id returns the list of purchased and insured items. If you query QP and return all items that do not require insurance you have a list all items not requiring insurance but it will include those that do have insurance anyways. The nice thing about a union query is that it will not duplicate items. So if an item is insured but not required to be insured it will be returned in your first query.

It will also be in the second query that returns all items that do not need insurance. When you union them it will no duplicate that item, but only add it once. T1 is all possible items that can be insured in that location by type and purpose. QP is all items that have been purchased at that location by type and purpose, along with additional data about the purchase i. They don't have unique identifiers, although each record is unique.

But my query is only returning the first record. Not sure of what you want for final results. There is no mention of Purpose. I think this is what you want. All records that match by name and purpose and match by type or type is null. That goes back to my original. Code: Copy to clipboard. Or without the cartesian join Code: Copy to clipboard. MajP said:. Can you tell me about the order portion? What does that do? When you use the query designer in the background it creates the SQL code that gets stored.

You can switch view from design view to sql view. Some queries cannot be shown in design view. The above query cannot be shown in design view. Then save it. The order by creates the sort order of the query. Thank you. My table names and columns have spaces in them. I'm guessing that's a problem?

I get a syntax error. You should avoid any spaces in any names of anything in Access. For fields and table names you usually will have to wrap them in square brackets [Table Name]. Will mess with it again on Monday I guess.