parenthesis change into bracket and my combo get lost!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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;
 
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...
 
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...
 
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...
 
Back
Top