Form based query wont return all values

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
 
D

DMag

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....
 
D

DMag

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
 
K

Klatuu

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...
 
D

DMag

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...
 
K

Klatuu

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Parameter using multiple options 0
Update Subform Query 3
Using Between criteria to search query 1
Fill in form using query 2
Problem with filling form using query? 2
append query if form item is null 0
Check box to control editablility of text box and return all recor 1
Null entry to extract all records 10

Top