Re: Partial Name Prompt Question

From: bdehning (bdehning_at_discussions.microsoft.com)
Date: 07/31/04 

Date: Sat, 31 Jul 2004 08:27:02 -0700

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

Relevant Pages