Including form controls as fields in a query

  • Thread starter Thread starter jaf
  • Start date Start date
J

jaf

I have converted an Access 97 database to an Access 2003 database. I have
queries that include the value of controls on a form (i.e. StartDate &
StopDate) and these queries are the record source for a report. The query
fields populate correctly in the 97 database but do not populate in the 2003
database.

Here is an example of my query field:
Start Date: [Forms]![frmMyForm]![StartDate]

Why can't I get this to work in 2003 when it works in 97?
 
Post the complete SQL of the query. Open in design view, click on VIEW -
SQL View, highlight all, copy, and paste in a post.
 
Here is query SQL:
SELECT HTTX_PATIENT.MRN, HTTX_PATIENT.LAST_NAME AS [Last Name],
HTTX_PATIENT.FIRST_NAME AS [First Name], HTTX_PATIENT.DATE_OF_BIRTH AS DOB,
HTTX_RECIPIENT_REGISTRATION.REGISTRATION_DATE AS [Reg Date],
HTTX_PATIENT.DATE_OF_DEATH AS DOD, HTTX_DATA_DICTIONARY_TB.DESCRIPTION AS
[UNOS Status], HTTX_PATIENT.PHTS_ID AS [PHTS ID],
[Forms]![frmPHTSDataReports]![StartDate] AS [Start Date],
[Forms]![frmPHTSDataReports]![StopDate] AS [Stop Date]
FROM (HTTX_PATIENT INNER JOIN HTTX_RECIPIENT_REGISTRATION ON
HTTX_PATIENT.MRN = HTTX_RECIPIENT_REGISTRATION.MRN) LEFT JOIN
HTTX_DATA_DICTIONARY_TB ON HTTX_RECIPIENT_REGISTRATION.UNOS_STATUS =
HTTX_DATA_DICTIONARY_TB.DICTIONARY_PK
GROUP BY HTTX_PATIENT.MRN, HTTX_PATIENT.LAST_NAME, HTTX_PATIENT.FIRST_NAME,
HTTX_PATIENT.DATE_OF_BIRTH, HTTX_RECIPIENT_REGISTRATION.REGISTRATION_DATE,
HTTX_PATIENT.DATE_OF_DEATH, HTTX_DATA_DICTIONARY_TB.DESCRIPTION,
HTTX_PATIENT.PHTS_ID, [Forms]![frmPHTSDataReports]![StartDate],
[Forms]![frmPHTSDataReports]![StopDate], HTTX_PATIENT.PHTS_STUDY_IND,
DateDiff("d",[Forms]![frmPHTSDataReports]![StartDate],[registration_date]),
DateDiff("d",[registration_date],[Forms]![frmPHTSDataReports]![StopDate])
HAVING (((HTTX_PATIENT.PHTS_STUDY_IND)="000000000331") AND
((DateDiff("d",[Forms]![frmPHTSDataReports]![StartDate],[registration_date]))>=0)
AND
((DateDiff("d",[registration_date],[Forms]![frmPHTSDataReports]![StopDate]))>=0))
ORDER BY HTTX_PATIENT.LAST_NAME, HTTX_PATIENT.FIRST_NAME;

--
James


KARL DEWEY said:
Post the complete SQL of the query. Open in design view, click on VIEW -
SQL View, highlight all, copy, and paste in a post.
--
Build a little, test a little.


jaf said:
I have converted an Access 97 database to an Access 2003 database. I have
queries that include the value of controls on a form (i.e. StartDate &
StopDate) and these queries are the record source for a report. The query
fields populate correctly in the 97 database but do not populate in the 2003
database.

Here is an example of my query field:
Start Date: [Forms]![frmMyForm]![StartDate]

Why can't I get this to work in 2003 when it works in 97?
Click to expand...
Click to expand...
 
Why do you have --
DateDiff("d",[Forms]![frmPHTSDataReports]![StartDate],[registration_date]),
DateDiff("d",[registration_date],[Forms]![frmPHTSDataReports]![StopDate])
in the Group By?

--
Build a little, test a little.


jaf said:
Here is query SQL:
SELECT HTTX_PATIENT.MRN, HTTX_PATIENT.LAST_NAME AS [Last Name],
HTTX_PATIENT.FIRST_NAME AS [First Name], HTTX_PATIENT.DATE_OF_BIRTH AS DOB,
HTTX_RECIPIENT_REGISTRATION.REGISTRATION_DATE AS [Reg Date],
HTTX_PATIENT.DATE_OF_DEATH AS DOD, HTTX_DATA_DICTIONARY_TB.DESCRIPTION AS
[UNOS Status], HTTX_PATIENT.PHTS_ID AS [PHTS ID],
[Forms]![frmPHTSDataReports]![StartDate] AS [Start Date],
[Forms]![frmPHTSDataReports]![StopDate] AS [Stop Date]
FROM (HTTX_PATIENT INNER JOIN HTTX_RECIPIENT_REGISTRATION ON
HTTX_PATIENT.MRN = HTTX_RECIPIENT_REGISTRATION.MRN) LEFT JOIN
HTTX_DATA_DICTIONARY_TB ON HTTX_RECIPIENT_REGISTRATION.UNOS_STATUS =
HTTX_DATA_DICTIONARY_TB.DICTIONARY_PK
GROUP BY HTTX_PATIENT.MRN, HTTX_PATIENT.LAST_NAME, HTTX_PATIENT.FIRST_NAME,
HTTX_PATIENT.DATE_OF_BIRTH, HTTX_RECIPIENT_REGISTRATION.REGISTRATION_DATE,
HTTX_PATIENT.DATE_OF_DEATH, HTTX_DATA_DICTIONARY_TB.DESCRIPTION,
HTTX_PATIENT.PHTS_ID, [Forms]![frmPHTSDataReports]![StartDate],
[Forms]![frmPHTSDataReports]![StopDate], HTTX_PATIENT.PHTS_STUDY_IND,
DateDiff("d",[Forms]![frmPHTSDataReports]![StartDate],[registration_date]),
DateDiff("d",[registration_date],[Forms]![frmPHTSDataReports]![StopDate])
HAVING (((HTTX_PATIENT.PHTS_STUDY_IND)="000000000331") AND
((DateDiff("d",[Forms]![frmPHTSDataReports]![StartDate],[registration_date]))>=0)
AND
((DateDiff("d",[registration_date],[Forms]![frmPHTSDataReports]![StopDate]))>=0))
ORDER BY HTTX_PATIENT.LAST_NAME, HTTX_PATIENT.FIRST_NAME;

--
James


KARL DEWEY said:
Post the complete SQL of the query. Open in design view, click on VIEW -
SQL View, highlight all, copy, and paste in a post.
--
Build a little, test a little.


jaf said:
I have converted an Access 97 database to an Access 2003 database. I have
queries that include the value of controls on a form (i.e. StartDate &
StopDate) and these queries are the record source for a report. The query
fields populate correctly in the 97 database but do not populate in the 2003
database.

Here is an example of my query field:
Start Date: [Forms]![frmMyForm]![StartDate]

Why can't I get this to work in 2003 when it works in 97?
Click to expand...
Click to expand...
Click to expand...
 
I designed the query in the Access query design grid and had clicked the
Totals button on the toolbar which includes all query fields in the Group By.
Turns out for this particular query I do not need the Group By clause at all.
Still, it does not affect the issue I am trying to resolve.

I created a test form and test query to experiment with different scenarios.
I discovered that I can populate a query field with an unformatted control
(general text) and I can get the value of a formatted control (i.e. date or
number) if I have 2nd query the input of which is the 1st query. A cumbersome
and far from ideal workaround solution! And I created the same test form and
query in Access 97 and have no problems getting the values in form controls,
formatted or not.
--
James


KARL DEWEY said:
Why do you have --
DateDiff("d",[Forms]![frmPHTSDataReports]![StartDate],[registration_date]),
DateDiff("d",[registration_date],[Forms]![frmPHTSDataReports]![StopDate])
in the Group By?

--
Build a little, test a little.


jaf said:
Here is query SQL:
SELECT HTTX_PATIENT.MRN, HTTX_PATIENT.LAST_NAME AS [Last Name],
HTTX_PATIENT.FIRST_NAME AS [First Name], HTTX_PATIENT.DATE_OF_BIRTH AS DOB,
HTTX_RECIPIENT_REGISTRATION.REGISTRATION_DATE AS [Reg Date],
HTTX_PATIENT.DATE_OF_DEATH AS DOD, HTTX_DATA_DICTIONARY_TB.DESCRIPTION AS
[UNOS Status], HTTX_PATIENT.PHTS_ID AS [PHTS ID],
[Forms]![frmPHTSDataReports]![StartDate] AS [Start Date],
[Forms]![frmPHTSDataReports]![StopDate] AS [Stop Date]
FROM (HTTX_PATIENT INNER JOIN HTTX_RECIPIENT_REGISTRATION ON
HTTX_PATIENT.MRN = HTTX_RECIPIENT_REGISTRATION.MRN) LEFT JOIN
HTTX_DATA_DICTIONARY_TB ON HTTX_RECIPIENT_REGISTRATION.UNOS_STATUS =
HTTX_DATA_DICTIONARY_TB.DICTIONARY_PK
GROUP BY HTTX_PATIENT.MRN, HTTX_PATIENT.LAST_NAME, HTTX_PATIENT.FIRST_NAME,
HTTX_PATIENT.DATE_OF_BIRTH, HTTX_RECIPIENT_REGISTRATION.REGISTRATION_DATE,
HTTX_PATIENT.DATE_OF_DEATH, HTTX_DATA_DICTIONARY_TB.DESCRIPTION,
HTTX_PATIENT.PHTS_ID, [Forms]![frmPHTSDataReports]![StartDate],
[Forms]![frmPHTSDataReports]![StopDate], HTTX_PATIENT.PHTS_STUDY_IND,
DateDiff("d",[Forms]![frmPHTSDataReports]![StartDate],[registration_date]),
DateDiff("d",[registration_date],[Forms]![frmPHTSDataReports]![StopDate])
HAVING (((HTTX_PATIENT.PHTS_STUDY_IND)="000000000331") AND
((DateDiff("d",[Forms]![frmPHTSDataReports]![StartDate],[registration_date]))>=0)
AND
((DateDiff("d",[registration_date],[Forms]![frmPHTSDataReports]![StopDate]))>=0))
ORDER BY HTTX_PATIENT.LAST_NAME, HTTX_PATIENT.FIRST_NAME;

--
James


KARL DEWEY said:
Post the complete SQL of the query. Open in design view, click on VIEW -
SQL View, highlight all, copy, and paste in a post.
--
Build a little, test a little.


:

I have converted an Access 97 database to an Access 2003 database. I have
queries that include the value of controls on a form (i.e. StartDate &
StopDate) and these queries are the record source for a report. The query
fields populate correctly in the 97 database but do not populate in the 2003
database.

Here is an example of my query field:
Start Date: [Forms]![frmMyForm]![StartDate]

Why can't I get this to work in 2003 when it works in 97?
Click to expand...
Click to expand...
Click to expand...
 
I learned that using the CDate function to coerce the form control formatted
as a date in the query field causes the field to be populated correctly.
This query is a select query. But an append query that pulls the same date
formatted form control works correctly without requiring the CDate function.
I do not know why the CDate function is needed in the select query but not in
the append query. Can anyone explain this?
--
James


jaf said:
I designed the query in the Access query design grid and had clicked the
Totals button on the toolbar which includes all query fields in the Group By.
Turns out for this particular query I do not need the Group By clause at all.
Still, it does not affect the issue I am trying to resolve.

I created a test form and test query to experiment with different scenarios.
I discovered that I can populate a query field with an unformatted control
(general text) and I can get the value of a formatted control (i.e. date or
number) if I have 2nd query the input of which is the 1st query. A cumbersome
and far from ideal workaround solution! And I created the same test form and
query in Access 97 and have no problems getting the values in form controls,
formatted or not.
--
James


KARL DEWEY said:
Why do you have --
DateDiff("d",[Forms]![frmPHTSDataReports]![StartDate],[registration_date]),
DateDiff("d",[registration_date],[Forms]![frmPHTSDataReports]![StopDate])
in the Group By?

--
Build a little, test a little.


jaf said:
Here is query SQL:
SELECT HTTX_PATIENT.MRN, HTTX_PATIENT.LAST_NAME AS [Last Name],
HTTX_PATIENT.FIRST_NAME AS [First Name], HTTX_PATIENT.DATE_OF_BIRTH AS DOB,
HTTX_RECIPIENT_REGISTRATION.REGISTRATION_DATE AS [Reg Date],
HTTX_PATIENT.DATE_OF_DEATH AS DOD, HTTX_DATA_DICTIONARY_TB.DESCRIPTION AS
[UNOS Status], HTTX_PATIENT.PHTS_ID AS [PHTS ID],
[Forms]![frmPHTSDataReports]![StartDate] AS [Start Date],
[Forms]![frmPHTSDataReports]![StopDate] AS [Stop Date]
FROM (HTTX_PATIENT INNER JOIN HTTX_RECIPIENT_REGISTRATION ON
HTTX_PATIENT.MRN = HTTX_RECIPIENT_REGISTRATION.MRN) LEFT JOIN
HTTX_DATA_DICTIONARY_TB ON HTTX_RECIPIENT_REGISTRATION.UNOS_STATUS =
HTTX_DATA_DICTIONARY_TB.DICTIONARY_PK
GROUP BY HTTX_PATIENT.MRN, HTTX_PATIENT.LAST_NAME, HTTX_PATIENT.FIRST_NAME,
HTTX_PATIENT.DATE_OF_BIRTH, HTTX_RECIPIENT_REGISTRATION.REGISTRATION_DATE,
HTTX_PATIENT.DATE_OF_DEATH, HTTX_DATA_DICTIONARY_TB.DESCRIPTION,
HTTX_PATIENT.PHTS_ID, [Forms]![frmPHTSDataReports]![StartDate],
[Forms]![frmPHTSDataReports]![StopDate], HTTX_PATIENT.PHTS_STUDY_IND,
DateDiff("d",[Forms]![frmPHTSDataReports]![StartDate],[registration_date]),
DateDiff("d",[registration_date],[Forms]![frmPHTSDataReports]![StopDate])
HAVING (((HTTX_PATIENT.PHTS_STUDY_IND)="000000000331") AND
((DateDiff("d",[Forms]![frmPHTSDataReports]![StartDate],[registration_date]))>=0)
AND
((DateDiff("d",[registration_date],[Forms]![frmPHTSDataReports]![StopDate]))>=0))
ORDER BY HTTX_PATIENT.LAST_NAME, HTTX_PATIENT.FIRST_NAME;

--
James


:

Post the complete SQL of the query. Open in design view, click on VIEW -
SQL View, highlight all, copy, and paste in a post.
--
Build a little, test a little.


:

I have converted an Access 97 database to an Access 2003 database. I have
queries that include the value of controls on a form (i.e. StartDate &
StopDate) and these queries are the record source for a report. The query
fields populate correctly in the 97 database but do not populate in the 2003
database.

Here is an example of my query field:
Start Date: [Forms]![frmMyForm]![StartDate]

Why can't I get this to work in 2003 when it works in 97?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top