Form based query wont return all values

  • Thread starter Thread starter DMag
  • Start date Start date
D

DMag

I have a simple query based on a form with 5 dropdown options.
I want the query to return all values for 3 boxes left blank...
I've tried the (Like XXX is null) on all 3 fields but that doesn't work...

Any suggestions??? Thanks
 
OK...Thanks

SELECT [Ad Master with Calculated Billed Amount].Advertiser, [Edition] & " "
& [Section] & " " & [Invoice Notes] & " " & IIf([Ad Size Column]>0,[Ad Size
Column] & " X ") & [Ad Size Depth] AS Expr1, [Ad Master with Calculated
Billed Amount].[Ad Rep],
[Ad Master with Calculated Billed Amount].Color,
[Ad Master with Calculated Billed Amount].[Trade Amount],
[Ad Master with Calculated Billed Amount].[Agency Discount],
[Ad Master with Calculated Billed Amount].[Color Chrg],
[Ad Master with Calculated Billed Amount].[Total Billed Amount],
[Ad Master with Calculated Billed Amount].[Issue Date],
[Ad Master with Calculated Billed Amount].Edition,
[Ad Master with Calculated Billed Amount].Section

FROM [Ad Master with Calculated Billed Amount]

WHERE ((([Ad Master with Calculated Billed
Amount].Advertiser)=[Forms]![Account Inquiry]![Advertiser Dropdown])
AND (([Ad Master with Calculated Billed Amount].[Issue Date]) Between
[Forms]![Account Inquiry]![Beg Issue Date Dropdown] And [Forms]![Account
Inquiry]![End Issue Date Dropdown])
AND (([Ad Master with Calculated Billed Amount].Edition)=[Forms]![Account
Inquiry]![Edition Dropdown])
AND (([Ad Master with Calculated Billed Amount].Section)=[Forms]![Account
Inquiry]![Section Dropdown])) OR ((([Ad Master with Calculated Billed
Amount].[Issue Date]) Between [Forms]![Account Inquiry]![Beg Issue Date
Dropdown] And [Forms]![Account Inquiry]![End Issue Date Dropdown])
AND ((([Ad Master with Calculated Billed Amount].[Edition]) Like
[Forms]![Account Inquiry]![Edition Dropdown]) Is Null)
AND ((([Ad Master with Calculated Billed Amount].[Section]) Like
[Forms]![Account Inquiry]![Section Dropdown]) Is Null)
AND ((([Ad Master with Calculated Billed Amount].[Advertiser]) Like
[Forms]![Account Inquiry]![Advertiser Dropdown]) Is Null))

ORDER BY [Ad Master with Calculated Billed Amount].Advertiser,
[Ad Master with Calculated Billed Amount].[Issue Date],
[Ad Master with Calculated Billed Amount].Edition,
[Ad Master with Calculated Billed Amount].Section;

I want the query and report to show all advertisers, editions or sections if
either of these dropdown boxes are left blank....
 
OK....

SELECT [Ad Master with Calculated Billed Amount].Advertiser, [Edition] & " "
& [Section] & " " & [Invoice Notes] & " " & IIf([Ad Size Column]>0,[Ad Size
Column] & " X ") & [Ad Size Depth] AS Expr1,
[Ad Master with Calculated Billed Amount].[Ad Rep],
[Ad Master with Calculated Billed Amount].Color,
[Ad Master with Calculated Billed Amount].[Trade Amount],
[Ad Master with Calculated Billed Amount].[Agency Discount],
[Ad Master with Calculated Billed Amount].[Color Chrg],
[Ad Master with Calculated Billed Amount].[Total Billed Amount],
[Ad Master with Calculated Billed Amount].[Issue Date],
[Ad Master with Calculated Billed Amount].[Edition],
[Ad Master with Calculated Billed Amount].[Section]

FROM [Ad Master with Calculated Billed Amount]

WHERE ((([Ad Master with Calculated Billed
Amount].Advertiser)=[Forms]![Account Inquiry]![Advertiser Dropdown])
AND (([Ad Master with Calculated Billed Amount].[Issue Date]) Between
[Forms]![Account Inquiry]![Beg Issue Date Dropdown] And [Forms]![Account
Inquiry]![End Issue Date Dropdown])
AND (([Ad Master with Calculated Billed Amount].[Edition])=[Forms]![Account
Inquiry]![Edition Dropdown])
AND (([Ad Master with Calculated Billed Amount].[Section])=[Forms]![Account
Inquiry]![Section Dropdown])) OR ((([Ad Master with Calculated Billed
Amount].[Issue Date]) Between [Forms]![Account Inquiry]![Beg Issue Date
Dropdown] And [Forms]![Account Inquiry]![End Issue Date Dropdown])
AND ((([Ad Master with Calculated Billed Amount].[Edition]) Like
[Forms]![Account Inquiry]![Edition Dropdown]) Is Null)
AND ((([Ad Master with Calculated Billed Amount].[Section]) Like
[Forms]![Account Inquiry]![Section Dropdown]) Is Null)
AND ((([Ad Master with Calculated Billed Amount].[Advertiser]) Like
[Forms]![Account Inquiry]![Advertiser Dropdown]) Is Null))

ORDER BY [Ad Master with Calculated Billed Amount].[Advertiser],
[Ad Master with Calculated Billed Amount].[Issue Date],
[Ad Master with Calculated Billed Amount].[Edition],
[Ad Master with Calculated Billed Amount].[Section];

I want the query/report to return all value if the user leaves the
advertiser, section, or edition dropdown box blank.....Thanks again
 
Here is an example of how you can modify your query to return all values for
a combo left blank:

[Ad Master with Calculated Billed Amount].Advertiser Like
IIf([Forms]![Account Inquiry]![Advertiser Dropdown] Is Null, "*",
[Forms]![Account Inquiry]![Advertiser Dropdown])

DMag said:
OK...Thanks

SELECT [Ad Master with Calculated Billed Amount].Advertiser, [Edition] & "
"
& [Section] & " " & [Invoice Notes] & " " & IIf([Ad Size Column]>0,[Ad
Size
Column] & " X ") & [Ad Size Depth] AS Expr1, [Ad Master with Calculated
Billed Amount].[Ad Rep],
[Ad Master with Calculated Billed Amount].Color,
[Ad Master with Calculated Billed Amount].[Trade Amount],
[Ad Master with Calculated Billed Amount].[Agency Discount],
[Ad Master with Calculated Billed Amount].[Color Chrg],
[Ad Master with Calculated Billed Amount].[Total Billed Amount],
[Ad Master with Calculated Billed Amount].[Issue Date],
[Ad Master with Calculated Billed Amount].Edition,
[Ad Master with Calculated Billed Amount].Section

FROM [Ad Master with Calculated Billed Amount]

WHERE ((([Ad Master with Calculated Billed
Amount].Advertiser)=[Forms]![Account Inquiry]![Advertiser Dropdown])
AND (([Ad Master with Calculated Billed Amount].[Issue Date]) Between
[Forms]![Account Inquiry]![Beg Issue Date Dropdown] And [Forms]![Account
Inquiry]![End Issue Date Dropdown])
AND (([Ad Master with Calculated Billed Amount].Edition)=[Forms]![Account
Inquiry]![Edition Dropdown])
AND (([Ad Master with Calculated Billed Amount].Section)=[Forms]![Account
Inquiry]![Section Dropdown])) OR ((([Ad Master with Calculated Billed
Amount].[Issue Date]) Between [Forms]![Account Inquiry]![Beg Issue Date
Dropdown] And [Forms]![Account Inquiry]![End Issue Date Dropdown])
AND ((([Ad Master with Calculated Billed Amount].[Edition]) Like
[Forms]![Account Inquiry]![Edition Dropdown]) Is Null)
AND ((([Ad Master with Calculated Billed Amount].[Section]) Like
[Forms]![Account Inquiry]![Section Dropdown]) Is Null)
AND ((([Ad Master with Calculated Billed Amount].[Advertiser]) Like
[Forms]![Account Inquiry]![Advertiser Dropdown]) Is Null))

ORDER BY [Ad Master with Calculated Billed Amount].Advertiser,
[Ad Master with Calculated Billed Amount].[Issue Date],
[Ad Master with Calculated Billed Amount].Edition,
[Ad Master with Calculated Billed Amount].Section;

I want the query and report to show all advertisers, editions or sections
if
either of these dropdown boxes are left blank....

Klatuu said:
It would be better if you post the SQL of the query so we can have a
look.
Click to expand...
Click to expand...
 
I've rewrote the query as follows

SELECT [Ad Master with Calculated Billed Amount].Advertiser, [Edition] & " "
& [Section] & " " & [Invoice Notes] & " " & IIf([Ad Size Column]>0,[Ad Size
Column] & " X ") & [Ad Size Depth] AS Expr1, [Ad Master with Calculated
Billed Amount].[Ad Rep], [Ad Master with Calculated Billed Amount].Color, [Ad
Master with Calculated Billed Amount].[Trade Amount], [Ad Master with
Calculated Billed Amount].[Agency Discount], [Ad Master with Calculated
Billed Amount].[Color Chrg], [Ad Master with Calculated Billed Amount].[Total
Billed Amount], [Ad Master with Calculated Billed Amount].[Issue Date], [Ad
Master with Calculated Billed Amount].[Edition], [Ad Master with Calculated
Billed Amount].[Section]
FROM [Ad Master with Calculated Billed Amount]
WHERE (([Ad Master with Calculated Billed
Amount].Advertiser)=[Forms]![Account Inquiry]![Advertiser Dropdown])
AND (([Ad Master with Calculated Billed Amount].[Issue Date]) Between
[Forms]![Account Inquiry]![Beg Issue Date Dropdown] And [Forms]![Account
Inquiry]![End Issue Date Dropdown])
AND (([Ad Master with Calculated Billed Amount].[Edition])=[Forms]![Account
Inquiry]![Edition Dropdown])
AND (([Ad Master with Calculated Billed Amount].[Section])=[Forms]![Account
Inquiry]![Section Dropdown])
OR ((([Ad Master with Calculated Billed Amount].[Issue Date]) Between
[Forms]![Account Inquiry]![Beg Issue Date Dropdown] And [Forms]![Account
Inquiry]![End Issue Date Dropdown])

AND [Ad Master with Calculated Billed Amount].Advertiser Like
IIf([Forms]![Account Inquiry]![Advertiser Dropdown] Is Null, "*",
[Forms]![Account Inquiry]![Advertiser Dropdown])
AND [Ad Master with Calculated Billed Amount].Section Like
IIf([Forms]![Account Inquiry]![Section Dropdown] Is Null, "*",
[Forms]![Account Inquiry]![Section Dropdown])
AND [Ad Master with Calculated Billed Amount].Edition Like
IIf([Forms]![Account Inquiry]![Edition Dropdown] Is Null, "*",
[Forms]![Account Inquiry]![Edition Dropdown])

ORDER BY ([Ad Master with Calculated Billed Amount].[Advertiser], [Ad Master
with Calculated Billed Amount].[Issue Date], [Ad Master with Calculated
Billed Amount].[Edition], [Ad Master with Calculated Billed Amount].[Section];

When I try to close and save, I get a 'missing syntax' error message with
the 'ORDER' in ORDER BY highlighted. Any suggestions??

Thanks in advance

Klatuu said:
Here is an example of how you can modify your query to return all values for
a combo left blank:

[Ad Master with Calculated Billed Amount].Advertiser Like
IIf([Forms]![Account Inquiry]![Advertiser Dropdown] Is Null, "*",
[Forms]![Account Inquiry]![Advertiser Dropdown])

DMag said:
OK...Thanks

SELECT [Ad Master with Calculated Billed Amount].Advertiser, [Edition] & "
"
& [Section] & " " & [Invoice Notes] & " " & IIf([Ad Size Column]>0,[Ad
Size
Column] & " X ") & [Ad Size Depth] AS Expr1, [Ad Master with Calculated
Billed Amount].[Ad Rep],
[Ad Master with Calculated Billed Amount].Color,
[Ad Master with Calculated Billed Amount].[Trade Amount],
[Ad Master with Calculated Billed Amount].[Agency Discount],
[Ad Master with Calculated Billed Amount].[Color Chrg],
[Ad Master with Calculated Billed Amount].[Total Billed Amount],
[Ad Master with Calculated Billed Amount].[Issue Date],
[Ad Master with Calculated Billed Amount].Edition,
[Ad Master with Calculated Billed Amount].Section

FROM [Ad Master with Calculated Billed Amount]

WHERE ((([Ad Master with Calculated Billed
Amount].Advertiser)=[Forms]![Account Inquiry]![Advertiser Dropdown])
AND (([Ad Master with Calculated Billed Amount].[Issue Date]) Between
[Forms]![Account Inquiry]![Beg Issue Date Dropdown] And [Forms]![Account
Inquiry]![End Issue Date Dropdown])
AND (([Ad Master with Calculated Billed Amount].Edition)=[Forms]![Account
Inquiry]![Edition Dropdown])
AND (([Ad Master with Calculated Billed Amount].Section)=[Forms]![Account
Inquiry]![Section Dropdown])) OR ((([Ad Master with Calculated Billed
Amount].[Issue Date]) Between [Forms]![Account Inquiry]![Beg Issue Date
Dropdown] And [Forms]![Account Inquiry]![End Issue Date Dropdown])
AND ((([Ad Master with Calculated Billed Amount].[Edition]) Like
[Forms]![Account Inquiry]![Edition Dropdown]) Is Null)
AND ((([Ad Master with Calculated Billed Amount].[Section]) Like
[Forms]![Account Inquiry]![Section Dropdown]) Is Null)
AND ((([Ad Master with Calculated Billed Amount].[Advertiser]) Like
[Forms]![Account Inquiry]![Advertiser Dropdown]) Is Null))

ORDER BY [Ad Master with Calculated Billed Amount].Advertiser,
[Ad Master with Calculated Billed Amount].[Issue Date],
[Ad Master with Calculated Billed Amount].Edition,
[Ad Master with Calculated Billed Amount].Section;

I want the query and report to show all advertisers, editions or sections
if
either of these dropdown boxes are left blank....

Klatuu said:
It would be better if you post the SQL of the query so we can have a
look.

I have a simple query based on a form with 5 dropdown options.
I want the query to return all values for 3 boxes left blank...
I've tried the (Like XXX is null) on all 3 fields but that doesn't
work...

Any suggestions??? Thanks
Click to expand...
Click to expand...
Click to expand...
 
I have read it a few times, but I can't see the problem. Try taking out the
Order By and see if that is really where the problem is.

DMag said:
I've rewrote the query as follows

SELECT [Ad Master with Calculated Billed Amount].Advertiser, [Edition] & "
"
& [Section] & " " & [Invoice Notes] & " " & IIf([Ad Size Column]>0,[Ad
Size
Column] & " X ") & [Ad Size Depth] AS Expr1, [Ad Master with Calculated
Billed Amount].[Ad Rep], [Ad Master with Calculated Billed Amount].Color,
[Ad
Master with Calculated Billed Amount].[Trade Amount], [Ad Master with
Calculated Billed Amount].[Agency Discount], [Ad Master with Calculated
Billed Amount].[Color Chrg], [Ad Master with Calculated Billed
Amount].[Total
Billed Amount], [Ad Master with Calculated Billed Amount].[Issue Date],
[Ad
Master with Calculated Billed Amount].[Edition], [Ad Master with
Calculated
Billed Amount].[Section]
FROM [Ad Master with Calculated Billed Amount]
WHERE (([Ad Master with Calculated Billed
Amount].Advertiser)=[Forms]![Account Inquiry]![Advertiser Dropdown])
AND (([Ad Master with Calculated Billed Amount].[Issue Date]) Between
[Forms]![Account Inquiry]![Beg Issue Date Dropdown] And [Forms]![Account
Inquiry]![End Issue Date Dropdown])
AND (([Ad Master with Calculated Billed
Amount].[Edition])=[Forms]![Account
Inquiry]![Edition Dropdown])
AND (([Ad Master with Calculated Billed
Amount].[Section])=[Forms]![Account
Inquiry]![Section Dropdown])
OR ((([Ad Master with Calculated Billed Amount].[Issue Date]) Between
[Forms]![Account Inquiry]![Beg Issue Date Dropdown] And [Forms]![Account
Inquiry]![End Issue Date Dropdown])

AND [Ad Master with Calculated Billed Amount].Advertiser Like
IIf([Forms]![Account Inquiry]![Advertiser Dropdown] Is Null, "*",
[Forms]![Account Inquiry]![Advertiser Dropdown])
AND [Ad Master with Calculated Billed Amount].Section Like
IIf([Forms]![Account Inquiry]![Section Dropdown] Is Null, "*",
[Forms]![Account Inquiry]![Section Dropdown])
AND [Ad Master with Calculated Billed Amount].Edition Like
IIf([Forms]![Account Inquiry]![Edition Dropdown] Is Null, "*",
[Forms]![Account Inquiry]![Edition Dropdown])

ORDER BY ([Ad Master with Calculated Billed Amount].[Advertiser], [Ad
Master
with Calculated Billed Amount].[Issue Date], [Ad Master with Calculated
Billed Amount].[Edition], [Ad Master with Calculated Billed
Amount].[Section];

When I try to close and save, I get a 'missing syntax' error message with
the 'ORDER' in ORDER BY highlighted. Any suggestions??

Thanks in advance

Klatuu said:
Here is an example of how you can modify your query to return all values
for
a combo left blank:

[Ad Master with Calculated Billed Amount].Advertiser Like
IIf([Forms]![Account Inquiry]![Advertiser Dropdown] Is Null, "*",
[Forms]![Account Inquiry]![Advertiser Dropdown])

DMag said:
OK...Thanks

SELECT [Ad Master with Calculated Billed Amount].Advertiser, [Edition]
& "
"
& [Section] & " " & [Invoice Notes] & " " & IIf([Ad Size Column]>0,[Ad
Size
Column] & " X ") & [Ad Size Depth] AS Expr1, [Ad Master with Calculated
Billed Amount].[Ad Rep],
[Ad Master with Calculated Billed Amount].Color,
[Ad Master with Calculated Billed Amount].[Trade Amount],
[Ad Master with Calculated Billed Amount].[Agency Discount],
[Ad Master with Calculated Billed Amount].[Color Chrg],
[Ad Master with Calculated Billed Amount].[Total Billed Amount],
[Ad Master with Calculated Billed Amount].[Issue Date],
[Ad Master with Calculated Billed Amount].Edition,
[Ad Master with Calculated Billed Amount].Section

FROM [Ad Master with Calculated Billed Amount]

WHERE ((([Ad Master with Calculated Billed
Amount].Advertiser)=[Forms]![Account Inquiry]![Advertiser Dropdown])
AND (([Ad Master with Calculated Billed Amount].[Issue Date]) Between
[Forms]![Account Inquiry]![Beg Issue Date Dropdown] And
[Forms]![Account
Inquiry]![End Issue Date Dropdown])
AND (([Ad Master with Calculated Billed
Amount].Edition)=[Forms]![Account
Inquiry]![Edition Dropdown])
AND (([Ad Master with Calculated Billed
Amount].Section)=[Forms]![Account
Inquiry]![Section Dropdown])) OR ((([Ad Master with Calculated Billed
Amount].[Issue Date]) Between [Forms]![Account Inquiry]![Beg Issue Date
Dropdown] And [Forms]![Account Inquiry]![End Issue Date Dropdown])
AND ((([Ad Master with Calculated Billed Amount].[Edition]) Like
[Forms]![Account Inquiry]![Edition Dropdown]) Is Null)
AND ((([Ad Master with Calculated Billed Amount].[Section]) Like
[Forms]![Account Inquiry]![Section Dropdown]) Is Null)
AND ((([Ad Master with Calculated Billed Amount].[Advertiser]) Like
[Forms]![Account Inquiry]![Advertiser Dropdown]) Is Null))

ORDER BY [Ad Master with Calculated Billed Amount].Advertiser,
[Ad Master with Calculated Billed Amount].[Issue Date],
[Ad Master with Calculated Billed Amount].Edition,
[Ad Master with Calculated Billed Amount].Section;

I want the query and report to show all advertisers, editions or
sections
if
either of these dropdown boxes are left blank....

:

It would be better if you post the SQL of the query so we can have a
look.

I have a simple query based on a form with 5 dropdown options.
I want the query to return all values for 3 boxes left blank...
I've tried the (Like XXX is null) on all 3 fields but that doesn't
work...

Any suggestions??? Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top