Partial Name Prompt Question

G

Guest

I currently have the following SQL which works fine but requires me to enter the whole Servicing Division such as Henderson.

How do I add Like[Enter Servicing Division] & "*" without getting sytax errors so I can type only a patial name such as Hen to pull it up.

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]);
 
M

matt

try using the left function. i.e.
Left([servicing division],3)
then in the criteria type, [enter servicing division].
when HEN is typed in the prompt, Henderson will be brought
up.
-----Original Message-----
I currently have the following SQL which works fine but
Click to expand...
requires me to enter the whole Servicing Division such as
Henderson.
How do I add Like[Enter Servicing Division] & "*"
Click to expand...
without getting sytax errors so I can type only a patial
name such as Hen to pull it up.
SELECT [SERVICE CALL NUMBER],[ACCOUNT NAME],[ASSIGNED
Click to expand...
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
Click to expand...
MONTH] OR [RESCHEDULED SERVICE MONTH] IS NULL) and
[Location Servicing Division] =[Enter Servicing Division])
UNION ALL SELECT [SERVICE CALL NUMBER],[ACCOUNT NAME],
Click to expand...
[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
Click to expand...
MONTH] and [Location Servicing Division] =[Enter Servicing
Division]);
 
G

Gary Walter

Hi Brian,

It seems like twice you have typed
"Like" without a following space
between it and the parameter.

bdehning said:
I tried that but still got syntax error. I am more familiar with Like[Enter
Click to expand...
Servicing Division] & "*" which works in my other design grid queries.
Does it have to do with proper( )?
--
Brian


matt said:
try using the left function. i.e.
Left([servicing division],3)
then in the criteria type, [enter servicing division].
when HEN is typed in the prompt, Henderson will be brought
up.
-----Original Message-----
I currently have the following SQL which works fine but
Click to expand...
requires me to enter the whole Servicing Division such as
Henderson.
How do I add Like[Enter Servicing Division] & "*"
Click to expand...
without getting sytax errors so I can type only a patial
name such as Hen to pull it up.
SELECT [SERVICE CALL NUMBER],[ACCOUNT NAME],[ASSIGNED
Click to expand...
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
Click to expand...
MONTH] OR [RESCHEDULED SERVICE MONTH] IS NULL) and
[Location Servicing Division] =[Enter Servicing Division])
UNION ALL SELECT [SERVICE CALL NUMBER],[ACCOUNT NAME],
Click to expand...
[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
Click to expand...
MONTH] and [Location Servicing Division] =[Enter Servicing
Division]);
Click to expand...
Click to expand...
 
G

Guest

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


Gary Walter said:
Hi Brian,

It seems like twice you have typed
"Like" without a following space
between it and the parameter.

bdehning said:
I tried that but still got syntax error. I am more familiar with Like[Enter
Click to expand...
Servicing Division] & "*" which works in my other design grid queries.
Does it have to do with proper( )?
--
Brian


matt said:
try using the left function. i.e.
Left([servicing division],3)
then in the criteria type, [enter servicing division].
when HEN is typed in the prompt, Henderson will be brought
up.

-----Original Message-----
I currently have the following SQL which works fine but
requires me to enter the whole Servicing Division such as
Henderson.

How do I add Like[Enter Servicing Division] & "*"
without getting sytax errors so I can type only a patial
name such as Hen to pull it up.

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]);
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

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]
Click to expand...

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 said:
Here is my SQL. Can Someone help place Like[Enter Servicing Division] & "*" in
Click to expand...
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
Click to expand...
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
Click to expand...
SERVICE MONTH] IS NULL) and [Location Servicing Division] =[Enter Servicing
Division])
UNION ALL SELECT [SERVICE CALL NUMBER],[ACCOUNT NAME],[ASSIGNED
Click to expand...
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
Click to expand...
Servicing Division] =[Enter Servicing Division]);
 
G

Guest

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 said:
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]
Click to expand...

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 said:
Here is my SQL. Can Someone help place Like[Enter Servicing Division] & "*" in
Click to expand...
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
Click to expand...
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
Click to expand...
SERVICE MONTH] IS NULL) and [Location Servicing Division] =[Enter Servicing
Division])
UNION ALL SELECT [SERVICE CALL NUMBER],[ACCOUNT NAME],[ASSIGNED
Click to expand...
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
Click to expand...
Servicing Division] =[Enter Servicing Division]);
Click to expand...
 
G

Gary Walter

Hi Brian,

Please post the 2 *separate* queries that work.

Thank you,

Gary Walter

Gary, I got syntax error in expression.

WHERE ((([Total Outstanding Service Calls By Consultant].[Rescheduled Service
Click to expand...
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
Click to expand...
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 said:
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]
Click to expand...

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 said:
Here is my SQL. Can Someone help place Like[Enter Servicing Division] & "*" in
Click to expand...
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
Click to expand...
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
Click to expand...
SERVICE MONTH] IS NULL) and [Location Servicing Division] =[Enter Servicing
Division])
UNION ALL SELECT [SERVICE CALL NUMBER],[ACCOUNT NAME],[ASSIGNED
Click to expand...
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
Click to expand...
Servicing Division] =[Enter Servicing Division]);
Click to expand...
Click to expand...
 
G

Guest

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 said:
Hi Brian,

Please post the 2 *separate* queries that work.

Thank you,

Gary Walter

Gary, I got syntax error in expression.

WHERE ((([Total Outstanding Service Calls By Consultant].[Rescheduled Service
Click to expand...
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
Click to expand...
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 said:
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

:
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]);
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

Sorry...I completely missed the "=" in
what you provided. :cool:

Glad it all worked out okay.

bdehning said:
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 said:
Hi Brian,

Please post the 2 *separate* queries that work.

Thank you,

Gary Walter

Gary, I got syntax error in expression.

WHERE ((([Total Outstanding Service Calls By Consultant].[Rescheduled Service
Click to expand...
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
Click to expand...
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


:

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

:
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
Click to expand...
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]);
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

Do not Count Duplicates 23
Bring together 2 queries 5
Exclude Duplicates in Report Counts 5
Bringing Together 2 unrelated queries 3
Duplicating Main Form And Its Subform Detail Records in Access 97 3
Criteria to open form 1
Form 1
Problems as I see them with Outlook 2007 and time management 1

Top