greg foley sharon soj

[gregfoley]

From: "Hans Up" <[email protected]>
Subject: Re: criteria = null
Date: Tuesday, August 04, 2009 8:17 AM

SELECT [PRODUCT TABLE].[Customer ID], [PRODUCT TABLE].ProductName, [RADIO
STATIONS TABLE].[Station Call Letters], [PRODUCT TABLE].[Target Audience
ID],
[RADIO STATIONS TABLE].[Market ID], [RADIO STATIONS TABLE].Rank, [Rank
Table].Rank, [PRODUCT TABLE].Rank
FROM (([MARKETS TABLE] INNER JOIN ([FORMATS TABLE] INNER JOIN [RADIO
STATIONS TABLE] ON [FORMATS TABLE].Format = [RADIO STATIONS TABLE].[Format
ID]) ON [MARKETS TABLE].[Market ID] = [RADIO STATIONS TABLE].[Market ID])
INNER JOIN ([Rank Table] INNER JOIN [PRODUCT TABLE] ON [Rank Table].ID =
[PRODUCT TABLE].Rank) ON [MARKETS TABLE].[Market ID] = [PRODUCT
TABLE].[Market ID].Value) INNER JOIN [TARGET AUDIENCE TABLE] ON ([FORMATS
TABLE].Format = [TARGET AUDIENCE TABLE].Format.Value) AND ([PRODUCT
TABLE].[Target Audience ID].Value = [TARGET AUDIENCE TABLE].[Target
Audience
ID])
WHERE ((([PRODUCT TABLE].ProductName) Like "*" & [Enter Product] & "*")
AND
(([RADIO STATIONS TABLE].Rank)<=[RANK TABLE].[Rank]) AND (([PRODUCT
TABLE].Rank)=[Forms]![Maintain Products Form]![Rank])) OR ((([PRODUCT
TABLE].ProductName) Like "*" & [Enter Product] & "*") AND (([RADIO
STATIONS
TABLE].Rank)<=[RANK TABLE].[Rank]) AND (([Forms]![Maintain Products
Form]![Rank]) Is Null))
ORDER BY [PRODUCT TABLE].[Customer ID], [PRODUCT TABLE].ProductName,
[RADIO
STATIONS TABLE].[Station Call Letters];


thanks but it still brings back no results if i leave the drop down blank.

Your SQL is difficult for my brain to parse. But I think you may have
other problems besides the blank drop down issue. Consider this
fragment in your "FROM" clause:

[FORMATS TABLE].Format = [TARGET AUDIENCE TABLE].Format.Value

You have a field named "Format" in both tables. But "Format" is a
reserved word.

Please see http://allenbrowne.com/AppIssueBadWord.html#F

Perhaps Access doesn't choke with "Format" as a field name, but I have
no idea how "Format.Value" will be evaluated in this context. That just
blows my mind, Nancy!

You're using ".Value" in three places:

SELECT
p.[Customer ID],
p.ProductName,
s.[Station Call Letters],
p.[Target Audience ID],
s.[Market ID],
s.Rank,
r.Rank,
p.Rank
FROM
(([MARKETS TABLE] AS m
INNER JOIN ([FORMATS TABLE] AS f
INNER JOIN [RADIO STATIONS TABLE] AS s
ON f.Format = s.[Format ID])
ON m.[Market ID] = s.[Market ID])
INNER JOIN ([Rank Table] AS r
INNER JOIN [PRODUCT TABLE] AS p
ON r.ID = p.Rank)
ON m.[Market ID] = p.[Market ID].Value) <--*
INNER JOIN [TARGET AUDIENCE TABLE] AS a
ON (f.Format = a.Format.Value) <--*
AND (p.[Target Audience ID].Value = a.[Target Audience ID]) <--*
WHERE
(((p.ProductName) Like "*" & [Enter Product] & "*")
AND ((s.Rank)<=r.[Rank])
AND ((p.Rank)=[Forms]![Maintain Products Form]![Rank]))
OR (((p.ProductName) Like "*" & [Enter Product] & "*")
AND ((s.Rank)<=r.[Rank])
AND (([Forms]![Maintain Products Form]![Rank]) Is Null))
ORDER BY
p.[Customer ID],
p.ProductName,
s.[Station Call Letters];

Can i send you the db by any chance, or is that asking way too much?

Did you intend that question for me? Or Ken?

If you really want to send me a copy of your db, I will look at it. But
no guarantee I can fix anything! I may conclude the amount of effort is
unreasonable.

With that caveat in mind, you're welcome to send me a zip of your
database. My first name is Hans. Last name is Updyke. You can get it
to me at (e-mail address removed). (I wrote it that way as a spam
deflector; you can make the substitutions for first and last.) Discard
any sensitive/confidential data from your db, but leave me at least a
few records of real or fake data in each table to help me figure out how
the data is used. And please perform a "Compact and Repair" on your db
before creating the zip archive file.

Hans