Web Development

Our forums are currently in maintenance mode and the ability to post is disabled. We will be back up and running as soon as possible. Thanks for your patience!



SQL in MS Access

By kms_cryswater ·
I am looking for help on a specific SQL query in MS Access. I keep running into syntax errors. I have 4 fields in the query. When the data I'm querying is LIKE the data in field 1 AND the data in field 3 and/or 4 IS NULL or BLANK, then I want the null or blank records in 3 or 4 to display the text result, "ANY"

Is there anyone out there that knows Access query?

I am used to straight SQL query via a back-end connection like TOAD, so the restrictions of MS Access have me at a road block.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

lots of folks here can help - post your sql?

by sgt_shultz In reply to SQL in MS Access

can you post the code giving the error.

Collapse -


by rkuhn In reply to SQL in MS Access

I think what you want is something similar to:

IIf ( Field 3 = null , "Any", Field 3) In Access


SELECT IIf( Field 3 = Null, "Any", Field 3) AS [Field 3]
FROM dbo_Table 1; In SQL

Collapse -


by rkuhn In reply to SQL in MS Access

There is a huge difference between a null and a blank. Make sure you accommodate for that.

Collapse -

SQL code I am using in query

by kms_cryswater In reply to SQL in MS Access

This is the body of my original query. It works fine, except I would like to customize it with the parameters in the bottom of this reply:

SELECT [Paste SYNETICS PNs Here].SyneticsPN, dbo_resource.description, dbo_resource.user1 AS Extended_Description, dbo_manufacturer.name_of AS MFG_Name, dbo_source_manuf_items.source_manuf_catalog AS MFG_Number
FROM ([Paste SYNETICS PNs Here] LEFT JOIN (dbo_resource LEFT JOIN dbo_source_manuf_items ON dbo_resource.resource_no=dbo_source_manuf_items.resource_no) ON [Paste SYNETICS PNs Here].SyneticsPN=dbo_resource.resource_no) LEFT JOIN dbo_manufacturer ON dbo_source_manuf_items.source_manuf=dbo_manufacturer.id;

What I want is when [Paste SYNETICS PNs Here].SyneticsPN is LIKE "%__AB____%" AND dbo_manufacturer.name_of AND/OR dbo_source_manuf_items.source_manuf_catalog IS BLANK (meaning the table has no data to display), then the fields MFG_Name & MFG_Number, in the query [Get SYNETICS PNs Here] (IF EITHER ARE BLANK), need to display the text "ANY"

Collapse -


by darron.michael In reply to SQL in MS Access

Select Field1, Field2, iif(len(field3)<1,"ANY",Field3) as F3, iif(len(field4)<1,"Any",Field4) as F4 from TABLENAME
where Field1 like "*illi*";

Best I can guess without knowing exactly what the goal is.

Collapse -

The "literal" code

by kms_cryswater In reply to Maybe

Thank you for your reply. I have actually put the literal code in about the 5th response to the original question. The title is something like "The Code I am Using." I can't see it now or I'd give you the exact title name.

It has exactly what it is and what I want the output to produce.


Related Discussions

Related Forums