parenthesis change into bracket and my combo get lost!

R

rocco

Hello,
I wrote a SQL statement as recordsource for a combobox, here it is:
SELECT PAZ.ID, PAZ.PAZIENTE, TBLVISITS.TYPE, TBLVISITS.DATA
FROM (SELECT ID, (COGNOME & ", " & NOME) AS PAZIENTE FROM TBLANAGRAFICA) AS
PAZ INNER JOIN TBLVISITS ON TBLVISITS.ID=PAZ.ID
ORDER BY TBLVISITS.DATA;
I have fully tested and it works fine!!
But as soon as I close the form which hold the combobox, the statement
changes into:
SELECT PAZ.ID, PAZ.PAZIENTE, TBLVISITS.TYPE, TBLVISITS.DATA FROM [SELECT ID,
(COGNOME & ", " & NOME) AS PAZIENTE FROM TBLANAGRAFICA] AS PAZ INNER JOIN
TBLVISITS ON TBLVISITS.ID=PAZ.ID ORDER BY TBLVISITS.DATA;
It changes two parenthesis into brackets and it wont work anymore.
As soon as I click on the combpbox to choose from the list get an error
message.
Why is this happening?

Thanks,
Rocco
 
K

Ken Snell \(MVP\)

What you're seeing is how Jet handles subqueries being used as data tables.
Try adding a period (.) just after the ] character.

SELECT PAZ.ID, PAZ.PAZIENTE, TBLVISITS.TYPE, TBLVISITS.DATA FROM [SELECT ID,
(COGNOME & ", " & NOME) AS PAZIENTE FROM TBLANAGRAFICA]. AS PAZ INNER JOIN
TBLVISITS ON TBLVISITS.ID=PAZ.ID ORDER BY TBLVISITS.DATA;
 
R

rocco

If a try to add the period (.), once I shift to form view to test it it wont
work either, and I can see it deleted the period leaving the statement as:
SELECT PAZ.ID, PAZ.PAZIENTE, TBLVISITS.TYPE, TBLVISITS.DATA FROM [SELECT ID,
(COGNOME & ", " & NOME) AS PAZIENTE FROM TBLANAGRAFICA] AS PAZ INNER JOIN
TBLVISITS ON TBLVISITS.ID=PAZ.ID ORDER BY TBLVISITS.DATA;

I'm going crazy!!!!

Ken Snell (MVP) said:
What you're seeing is how Jet handles subqueries being used as data tables.
Try adding a period (.) just after the ] character.

SELECT PAZ.ID, PAZ.PAZIENTE, TBLVISITS.TYPE, TBLVISITS.DATA FROM [SELECT ID,
(COGNOME & ", " & NOME) AS PAZIENTE FROM TBLANAGRAFICA]. AS PAZ INNER JOIN
TBLVISITS ON TBLVISITS.ID=PAZ.ID ORDER BY TBLVISITS.DATA;

--

Ken Snell
<MS ACCESS MVP>


rocco said:
Hello,
I wrote a SQL statement as recordsource for a combobox, here it is:
SELECT PAZ.ID, PAZ.PAZIENTE, TBLVISITS.TYPE, TBLVISITS.DATA
FROM (SELECT ID, (COGNOME & ", " & NOME) AS PAZIENTE FROM TBLANAGRAFICA)
AS
PAZ INNER JOIN TBLVISITS ON TBLVISITS.ID=PAZ.ID
ORDER BY TBLVISITS.DATA;
I have fully tested and it works fine!!
But as soon as I close the form which hold the combobox, the statement
changes into:
SELECT PAZ.ID, PAZ.PAZIENTE, TBLVISITS.TYPE, TBLVISITS.DATA FROM [SELECT
ID,
(COGNOME & ", " & NOME) AS PAZIENTE FROM TBLANAGRAFICA] AS PAZ INNER JOIN
TBLVISITS ON TBLVISITS.ID=PAZ.ID ORDER BY TBLVISITS.DATA;
It changes two parenthesis into brackets and it wont work anymore.
As soon as I click on the combpbox to choose from the list get an error
message.
Why is this happening?

Thanks,
Rocco
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

And if that doesn't work, rewrite your query this way (this would be my
preferred "fix" in this situation):

SELECT PAZ.ID, (COGNOME & ", " & NOME) AS PAZIENTE,
TBLVISITS.TYPE, TBLVISITS.DATA
FROM TBLANAGRAFICA AS PAZ INNER JOIN TBLVISITS
ON TBLVISITS.ID=PAZ.ID
ORDER BY TBLVISITS.DATA;

--

Ken Snell
<MS ACCESS MVP>


Ken Snell (MVP) said:
What you're seeing is how Jet handles subqueries being used as data
tables. Try adding a period (.) just after the ] character.

SELECT PAZ.ID, PAZ.PAZIENTE, TBLVISITS.TYPE, TBLVISITS.DATA FROM [SELECT
ID,
(COGNOME & ", " & NOME) AS PAZIENTE FROM TBLANAGRAFICA]. AS PAZ INNER JOIN
TBLVISITS ON TBLVISITS.ID=PAZ.ID ORDER BY TBLVISITS.DATA;

--

Ken Snell
<MS ACCESS MVP>


rocco said:
Hello,
I wrote a SQL statement as recordsource for a combobox, here it is:
SELECT PAZ.ID, PAZ.PAZIENTE, TBLVISITS.TYPE, TBLVISITS.DATA
FROM (SELECT ID, (COGNOME & ", " & NOME) AS PAZIENTE FROM TBLANAGRAFICA)
AS
PAZ INNER JOIN TBLVISITS ON TBLVISITS.ID=PAZ.ID
ORDER BY TBLVISITS.DATA;
I have fully tested and it works fine!!
But as soon as I close the form which hold the combobox, the statement
changes into:
SELECT PAZ.ID, PAZ.PAZIENTE, TBLVISITS.TYPE, TBLVISITS.DATA FROM [SELECT
ID,
(COGNOME & ", " & NOME) AS PAZIENTE FROM TBLANAGRAFICA] AS PAZ INNER JOIN
TBLVISITS ON TBLVISITS.ID=PAZ.ID ORDER BY TBLVISITS.DATA;
It changes two parenthesis into brackets and it wont work anymore.
As soon as I click on the combpbox to choose from the list get an error
message.
Why is this happening?

Thanks,
Rocco
Click to expand...
Click to expand...
 
R

rocco

And now it works!
Thank you very much.

Ken Snell (MVP) said:
And if that doesn't work, rewrite your query this way (this would be my
preferred "fix" in this situation):

SELECT PAZ.ID, (COGNOME & ", " & NOME) AS PAZIENTE,
TBLVISITS.TYPE, TBLVISITS.DATA
FROM TBLANAGRAFICA AS PAZ INNER JOIN TBLVISITS
ON TBLVISITS.ID=PAZ.ID
ORDER BY TBLVISITS.DATA;

--

Ken Snell
<MS ACCESS MVP>


Ken Snell (MVP) said:
What you're seeing is how Jet handles subqueries being used as data
tables. Try adding a period (.) just after the ] character.

SELECT PAZ.ID, PAZ.PAZIENTE, TBLVISITS.TYPE, TBLVISITS.DATA FROM [SELECT
ID,
(COGNOME & ", " & NOME) AS PAZIENTE FROM TBLANAGRAFICA]. AS PAZ INNER JOIN
TBLVISITS ON TBLVISITS.ID=PAZ.ID ORDER BY TBLVISITS.DATA;

--

Ken Snell
<MS ACCESS MVP>


rocco said:
Hello,
I wrote a SQL statement as recordsource for a combobox, here it is:
SELECT PAZ.ID, PAZ.PAZIENTE, TBLVISITS.TYPE, TBLVISITS.DATA
FROM (SELECT ID, (COGNOME & ", " & NOME) AS PAZIENTE FROM TBLANAGRAFICA)
AS
PAZ INNER JOIN TBLVISITS ON TBLVISITS.ID=PAZ.ID
ORDER BY TBLVISITS.DATA;
I have fully tested and it works fine!!
But as soon as I close the form which hold the combobox, the statement
changes into:
SELECT PAZ.ID, PAZ.PAZIENTE, TBLVISITS.TYPE, TBLVISITS.DATA FROM [SELECT
ID,
(COGNOME & ", " & NOME) AS PAZIENTE FROM TBLANAGRAFICA] AS PAZ INNER JOIN
TBLVISITS ON TBLVISITS.ID=PAZ.ID ORDER BY TBLVISITS.DATA;
It changes two parenthesis into brackets and it wont work anymore.
As soon as I click on the combpbox to choose from the list get an error
message.
Why is this happening?

Thanks,
Rocco
Click to expand...
Click to expand...
Click to expand...
 
Top