Web Development



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