Partial Name Prompt Question

Gary Thank You for the help. I took out the "=" in the expression and it worked.

Thanks again.

Now I can adjust all the queries I want partial prompts for since I know the issue.
--
Brian


"Gary Walter" wrote:

Hi Brian,

Please post the 2 *separate* queries that work.

Thank you,

Gary Walter

"bdehning" wrote
Gary, I got syntax error in expression.

WHERE ((([Total Outstanding Service Calls By Consultant].[Rescheduled Service
Month])[SCHEDULED SERVICE MONTH] Or ([Total Outstanding Service Calls By
Consultant].[Rescheduled Service Month]) Is Null) AND (([Total Outstanding Service
Calls By Consultant].[Location Servicing Division]) Like [Enter Servicing Division] &
"*" ))

The above is what was created by what you told me to do. Query 1 worked but when I
placed the UNION All , I got syntax error in query expression. The computer created
the expression. Do you see anything wrong with it?
--

Brian


"Gary Walter" wrote:

Hi Brian,

The SQL below would have a syntax error
because there is no space between FROM
and [TOTAL OUTSTANDING SERVICE CALLS BY CONSULTANT]
in both sections of UNION query.

Often, when creating a UNION query, it helps
to start with designing a single SELECT query.

Click on "Create Query in Design View"

In the Show Table dialog box,
click on your table(query?),
click Add,
and then click Close.

Double-click on the "header" of your table
(which selects all the fields).

Drag-and-drop your selected fields down
to a Field row in the grid.

Select any column(s) you don't want in query
and click on "Cut" to remove that field from
the query.

Under column for [Location Servicing Division],
type the following in the Criteria row:

Like [Enter Servicing Division] & "*"

Under column for [RESCHEDULED SERVICE MONTH],
type the following in the Criteria row:

[SCHEDULED SERVICE MONTH]

Save this query and verify it works.

You now have the SQL for the second part of
your UNION query and you know it works.

Make a copy of this query and save it.

Go into Design mode of this copy and change
the criteria to what you want for the "first section"
of your UNION query.

Save and verify that it works properly.

Go into SQL view, delete the semicolon,
type in

space UNION ALL space

then copy-and-paste SQL from first query.

Save and verify it works.

Pretty easy, huh?

Gary Walter

"bdehning" wrote:
Here is my SQL. Can Someone help place Like[Enter Servicing Division] & "*" in
place of [Enter Servicing Division] in the SQL below? I still get syntax errors
if
using spacing between Like and [

SELECT [SERVICE CALL NUMBER],[ACCOUNT NAME],[ASSIGNED
CONSULTANT],[COUNTOFASSIGNED
CONSULTANT],[LOCATION SERVICING DIVISION],[POLICY NUMBER],[SCHEDULED SERVICE
MONTH],[LOCATION ADDRESS],[LOCATION CITY],[LOCATION STATE],[ORIGINAL SERVICE CALL
DATE],[EXPIRATION DATE],[COMMENTS],[SERVICE CALL TYPE],[EAP],[COUNTOFSCHEDULED
SERVICE MONTH]
FROM[TOTAL OUTSTANDING SERVICE CALLS BY CONSULTANT]
WHERE(([SCHEDULED SERVICE MONTH] [RESCHEDULED SERVICE MONTH] OR [RESCHEDULED
SERVICE MONTH] IS NULL) and [Location Servicing Division] =[Enter Servicing
Division])

UNION ALL SELECT [SERVICE CALL NUMBER],[ACCOUNT NAME],[ASSIGNED
CONSULTANT],[COUNTOFASSIGNED CONSULTANT],[LOCATION SERVICING DIVISION],[POLICY
NUMBER],[RESCHEDULED SERVICE MONTH],[LOCATION ADDRESS],[LOCATION CITY],[LOCATION
STATE],[ORIGINAL SERVICE CALL DATE],[EXPIRATION DATE],[COMMENTS],[SERVICE CALL
TYPE],[EAP],[COUNTOFSCHEDULED SERVICE MONTH]
FROM[TOTAL OUTSTANDING SERVICE CALLS BY CONSULTANT]
WHERE([RESCHEDULED SERVICE MONTH] [SCHEDULED SERVICE MONTH] and [Location
Servicing Division] =[Enter Servicing Division]);

--
Brian

Sorry...I completely missed the "=" in
what you provided. 8-)

Glad it all worked out okay.

"bdehning" wrote:
Gary Thank You for the help. I took out the "=" in the expression and it worked.

Thanks again.

Now I can adjust all the queries I want partial prompts for since I know the issue.
--
Brian


"Gary Walter" wrote:

Hi Brian,

Please post the 2 *separate* queries that work.

Thank you,

Gary Walter

"bdehning" wrote
Gary, I got syntax error in expression.

WHERE ((([Total Outstanding Service Calls By Consultant].[Rescheduled Service
Month])[SCHEDULED SERVICE MONTH] Or ([Total Outstanding Service Calls By
Consultant].[Rescheduled Service Month]) Is Null) AND (([Total Outstanding
Service
Calls By Consultant].[Location Servicing Division]) Like [Enter Servicing
Division] &
"*" ))

The above is what was created by what you told me to do. Query 1 worked but
when I
placed the UNION All , I got syntax error in query expression. The computer
created
the expression. Do you see anything wrong with it?
--

Brian


"Gary Walter" wrote:

Hi Brian,

The SQL below would have a syntax error
because there is no space between FROM
and [TOTAL OUTSTANDING SERVICE CALLS BY CONSULTANT]
in both sections of UNION query.

Often, when creating a UNION query, it helps
to start with designing a single SELECT query.

Click on "Create Query in Design View"

In the Show Table dialog box,
click on your table(query?),
click Add,
and then click Close.

Double-click on the "header" of your table
(which selects all the fields).

Drag-and-drop your selected fields down
to a Field row in the grid.

Select any column(s) you don't want in query
and click on "Cut" to remove that field from
the query.

Under column for [Location Servicing Division],
type the following in the Criteria row:

Like [Enter Servicing Division] & "*"

Under column for [RESCHEDULED SERVICE MONTH],
type the following in the Criteria row:

[SCHEDULED SERVICE MONTH]

Save this query and verify it works.

You now have the SQL for the second part of
your UNION query and you know it works.

Make a copy of this query and save it.

Go into Design mode of this copy and change
the criteria to what you want for the "first section"
of your UNION query.

Save and verify that it works properly.

Go into SQL view, delete the semicolon,
type in

space UNION ALL space

then copy-and-paste SQL from first query.

Save and verify it works.

Pretty easy, huh?

Gary Walter

"bdehning" wrote:
Here is my SQL. Can Someone help place Like[Enter Servicing Division] & "*"
in
place of [Enter Servicing Division] in the SQL below? I still get syntax
errors
if
using spacing between Like and [

SELECT [SERVICE CALL NUMBER],[ACCOUNT NAME],[ASSIGNED
CONSULTANT],[COUNTOFASSIGNED
CONSULTANT],[LOCATION SERVICING DIVISION],[POLICY NUMBER],[SCHEDULED SERVICE
MONTH],[LOCATION ADDRESS],[LOCATION CITY],[LOCATION STATE],[ORIGINAL SERVICE
CALL
DATE],[EXPIRATION DATE],[COMMENTS],[SERVICE CALL
TYPE],[EAP],[COUNTOFSCHEDULED
SERVICE MONTH]
FROM[TOTAL OUTSTANDING SERVICE CALLS BY CONSULTANT]
WHERE(([SCHEDULED SERVICE MONTH] [RESCHEDULED SERVICE MONTH] OR
[RESCHEDULED
SERVICE MONTH] IS NULL) and [Location Servicing Division] =[Enter Servicing
Division])

UNION ALL SELECT [SERVICE CALL NUMBER],[ACCOUNT NAME],[ASSIGNED
CONSULTANT],[COUNTOFASSIGNED CONSULTANT],[LOCATION SERVICING
DIVISION],[POLICY
NUMBER],[RESCHEDULED SERVICE MONTH],[LOCATION ADDRESS],[LOCATION
CITY],[LOCATION
STATE],[ORIGINAL SERVICE CALL DATE],[EXPIRATION DATE],[COMMENTS],[SERVICE
CALL
TYPE],[EAP],[COUNTOFSCHEDULED SERVICE MONTH]
FROM[TOTAL OUTSTANDING SERVICE CALLS BY CONSULTANT]
WHERE([RESCHEDULED SERVICE MONTH] [SCHEDULED SERVICE MONTH] and [Location
Servicing Division] =[Enter Servicing Division]);

--
Brian