using a calculation in a query

  • Thread starter Thread starter Shel
  • Start date Start date
S

Shel

I am writing a query from a table. This table pulls from a form that has an
age calculation in one field. This result does not store in the table, and I
have been told I need to create a new field in the query and insert this
calculation. When I put the calculation in the criteria field, it makes all
records vanish from the query. What am I doing wrong? In case it's helpful,
I'm including the sql view below:

SELECT Demographics.VRS, Demographics.SSN, Demographics.[Last Name],
Demographics.[First Name], Demographics.Phone, Demographics.Age,
Demographics.[Service Connection], Referral.[Scheduling Deadline],
Referral.[Consult Deadline], IT.[IT End Deadline], SE.[SE End Deadline],
TWE.[TWE End Deadline]
FROM (((Demographics INNER JOIN IT ON Demographics.SSN = IT.SSN) INNER JOIN
Referral ON Demographics.SSN = Referral.SSN) INNER JOIN SE ON
Demographics.SSN = SE.SSN) INNER JOIN TWE ON Demographics.SSN = TWE.SSN;
 
On the form, go to the properties of the text box with the age results and
verify the name of the text box and use this for "Name" in the below formula.
In the criteria put the below formula.

=[Forms]![YourFormName]![Name]

Be sure the results will match the items in the table. Example: If the
results of the calculation is only the month/day and the table has
month/day/year then this will not give any results either.
 
Will you please walk me through this? I am really green as an Access user.

KARL DEWEY said:
Post your SQL the includes the calculation in the criteria field.

Shel said:
I am writing a query from a table. This table pulls from a form that has an
age calculation in one field. This result does not store in the table, and I
have been told I need to create a new field in the query and insert this
calculation. When I put the calculation in the criteria field, it makes all
records vanish from the query. What am I doing wrong? In case it's helpful,
I'm including the sql view below:

SELECT Demographics.VRS, Demographics.SSN, Demographics.[Last Name],
Demographics.[First Name], Demographics.Phone, Demographics.Age,
Demographics.[Service Connection], Referral.[Scheduling Deadline],
Referral.[Consult Deadline], IT.[IT End Deadline], SE.[SE End Deadline],
TWE.[TWE End Deadline]
FROM (((Demographics INNER JOIN IT ON Demographics.SSN = IT.SSN) INNER JOIN
Referral ON Demographics.SSN = Referral.SSN) INNER JOIN SE ON
Demographics.SSN = SE.SSN) INNER JOIN TWE ON Demographics.SSN = TWE.SSN;
Click to expand...
Click to expand...
 
I did this, and it created a parameter prompt for age, which I do not want.
Did I do something wrong?

rbeach said:
On the form, go to the properties of the text box with the age results and
verify the name of the text box and use this for "Name" in the below formula.
In the criteria put the below formula.

=[Forms]![YourFormName]![Name]

Be sure the results will match the items in the table. Example: If the
results of the calculation is only the month/day and the table has
month/day/year then this will not give any results either.

--
Rick


Shel said:
I am writing a query from a table. This table pulls from a form that has an
age calculation in one field. This result does not store in the table, and I
have been told I need to create a new field in the query and insert this
calculation. When I put the calculation in the criteria field, it makes all
records vanish from the query. What am I doing wrong? In case it's helpful,
I'm including the sql view below:

SELECT Demographics.VRS, Demographics.SSN, Demographics.[Last Name],
Demographics.[First Name], Demographics.Phone, Demographics.Age,
Demographics.[Service Connection], Referral.[Scheduling Deadline],
Referral.[Consult Deadline], IT.[IT End Deadline], SE.[SE End Deadline],
TWE.[TWE End Deadline]
FROM (((Demographics INNER JOIN IT ON Demographics.SSN = IT.SSN) INNER JOIN
Referral ON Demographics.SSN = Referral.SSN) INNER JOIN SE ON
Demographics.SSN = SE.SSN) INNER JOIN TWE ON Demographics.SSN = TWE.SSN;
Click to expand...
Click to expand...
 
As long as the form is open when the query is run, the field will
automatically be filled in with the information from the form. If the form is
not open, you will receive a dialog box.
--
Rick


Shel said:
I did this, and it created a parameter prompt for age, which I do not want.
Did I do something wrong?

rbeach said:
On the form, go to the properties of the text box with the age results and
verify the name of the text box and use this for "Name" in the below formula.
In the criteria put the below formula.

=[Forms]![YourFormName]![Name]

Be sure the results will match the items in the table. Example: If the
results of the calculation is only the month/day and the table has
month/day/year then this will not give any results either.

--
Rick


Shel said:
I am writing a query from a table. This table pulls from a form that has an
age calculation in one field. This result does not store in the table, and I
have been told I need to create a new field in the query and insert this
calculation. When I put the calculation in the criteria field, it makes all
records vanish from the query. What am I doing wrong? In case it's helpful,
I'm including the sql view below:

SELECT Demographics.VRS, Demographics.SSN, Demographics.[Last Name],
Demographics.[First Name], Demographics.Phone, Demographics.Age,
Demographics.[Service Connection], Referral.[Scheduling Deadline],
Referral.[Consult Deadline], IT.[IT End Deadline], SE.[SE End Deadline],
TWE.[TWE End Deadline]
FROM (((Demographics INNER JOIN IT ON Demographics.SSN = IT.SSN) INNER JOIN
Referral ON Demographics.SSN = Referral.SSN) INNER JOIN SE ON
Demographics.SSN = SE.SSN) INNER JOIN TWE ON Demographics.SSN = TWE.SSN;
Click to expand...
Click to expand...
Click to expand...
 
The SQL you posted earlier did not have any criteria. Post the one with the
calculation in the criteria.

Shel said:
Will you please walk me through this? I am really green as an Access user.

KARL DEWEY said:
Post your SQL the includes the calculation in the criteria field.

Shel said:
I am writing a query from a table. This table pulls from a form that has an
age calculation in one field. This result does not store in the table, and I
have been told I need to create a new field in the query and insert this
calculation. When I put the calculation in the criteria field, it makes all
records vanish from the query. What am I doing wrong? In case it's helpful,
I'm including the sql view below:

SELECT Demographics.VRS, Demographics.SSN, Demographics.[Last Name],
Demographics.[First Name], Demographics.Phone, Demographics.Age,
Demographics.[Service Connection], Referral.[Scheduling Deadline],
Referral.[Consult Deadline], IT.[IT End Deadline], SE.[SE End Deadline],
TWE.[TWE End Deadline]
FROM (((Demographics INNER JOIN IT ON Demographics.SSN = IT.SSN) INNER JOIN
Referral ON Demographics.SSN = Referral.SSN) INNER JOIN SE ON
Demographics.SSN = SE.SSN) INNER JOIN TWE ON Demographics.SSN = TWE.SSN;
Click to expand...
Click to expand...
Click to expand...
 
Hopefully this will be more helpful. I appreciate the assistance...

SELECT Demographics.VRS, Demographics.SSN, Demographics.[Last Name],
Demographics.[First Name], Demographics.Phone, Demographics.Age,
Demographics.[Service Connection], Referral.[Scheduling Deadline],
Referral.[Consult Deadline], IT.[IT End Deadline], SE.[SE End Deadline],
TWE.[TWE End Deadline]
FROM (((Demographics INNER JOIN IT ON Demographics.SSN = IT.SSN) INNER JOIN
Referral ON Demographics.SSN = Referral.SSN) INNER JOIN SE ON
Demographics.SSN = SE.SSN) INNER JOIN TWE ON Demographics.SSN = TWE.SSN
WHERE (((Demographics.Age)=[Forms]![Demographics]![Demographics]));

KARL DEWEY said:
The SQL you posted earlier did not have any criteria. Post the one with the
calculation in the criteria.

Shel said:
Will you please walk me through this? I am really green as an Access user.

KARL DEWEY said:
Post your SQL the includes the calculation in the criteria field.

:

I am writing a query from a table. This table pulls from a form that has an
age calculation in one field. This result does not store in the table, and I
have been told I need to create a new field in the query and insert this
calculation. When I put the calculation in the criteria field, it makes all
records vanish from the query. What am I doing wrong? In case it's helpful,
I'm including the sql view below:

SELECT Demographics.VRS, Demographics.SSN, Demographics.[Last Name],
Demographics.[First Name], Demographics.Phone, Demographics.Age,
Demographics.[Service Connection], Referral.[Scheduling Deadline],
Referral.[Consult Deadline], IT.[IT End Deadline], SE.[SE End Deadline],
TWE.[TWE End Deadline]
FROM (((Demographics INNER JOIN IT ON Demographics.SSN = IT.SSN) INNER JOIN
Referral ON Demographics.SSN = Referral.SSN) INNER JOIN SE ON
Demographics.SSN = SE.SSN) INNER JOIN TWE ON Demographics.SSN = TWE.SSN;
Click to expand...
Click to expand...
Click to expand...
 
You're right. Having the form open when running the query stops the criteria
dialog box from popping up. Now my problem is that this only appears to be
pulling in one blank record when there should be 10 populated records. I've
created this as a test database just so I can play with it while I'm learning
all of the programming specifics. Below is the sequel text. Any help you can
offer would be appreciated.

SELECT Demographics.VRS, Demographics.SSN, Demographics.[Last Name],
Demographics.[First Name], Demographics.Phone, Demographics.Age,
Demographics.[Service Connection], Referral.[Scheduling Deadline],
Referral.[Consult Deadline], IT.[IT End Deadline], SE.[SE End Deadline],
TWE.[TWE End Deadline]
FROM (((Demographics INNER JOIN IT ON Demographics.SSN = IT.SSN) INNER JOIN
Referral ON Demographics.SSN = Referral.SSN) INNER JOIN SE ON
Demographics.SSN = SE.SSN) INNER JOIN TWE ON Demographics.SSN = TWE.SSN
WHERE (((Demographics.Age)=[Forms]![Demographics]![Age]));

rbeach said:
As long as the form is open when the query is run, the field will
automatically be filled in with the information from the form. If the form is
not open, you will receive a dialog box.
--
Rick


Shel said:
I did this, and it created a parameter prompt for age, which I do not want.
Did I do something wrong?

rbeach said:
On the form, go to the properties of the text box with the age results and
verify the name of the text box and use this for "Name" in the below formula.
In the criteria put the below formula.

=[Forms]![YourFormName]![Name]

Be sure the results will match the items in the table. Example: If the
results of the calculation is only the month/day and the table has
month/day/year then this will not give any results either.

--
Rick


:

I am writing a query from a table. This table pulls from a form that has an
age calculation in one field. This result does not store in the table, and I
have been told I need to create a new field in the query and insert this
calculation. When I put the calculation in the criteria field, it makes all
records vanish from the query. What am I doing wrong? In case it's helpful,
I'm including the sql view below:

SELECT Demographics.VRS, Demographics.SSN, Demographics.[Last Name],
Demographics.[First Name], Demographics.Phone, Demographics.Age,
Demographics.[Service Connection], Referral.[Scheduling Deadline],
Referral.[Consult Deadline], IT.[IT End Deadline], SE.[SE End Deadline],
TWE.[TWE End Deadline]
FROM (((Demographics INNER JOIN IT ON Demographics.SSN = IT.SSN) INNER JOIN
Referral ON Demographics.SSN = Referral.SSN) INNER JOIN SE ON
Demographics.SSN = SE.SSN) INNER JOIN TWE ON Demographics.SSN = TWE.SSN;
Click to expand...
Click to expand...
Click to expand...
 
If you run this query with the form close and enter a number when it prompts
you do you get the 10 records?

If you do then try this --
WHERE (((Demographics.Age)=Val([Forms]![Demographics]![Age])));


Shel said:
You're right. Having the form open when running the query stops the criteria
dialog box from popping up. Now my problem is that this only appears to be
pulling in one blank record when there should be 10 populated records. I've
created this as a test database just so I can play with it while I'm learning
all of the programming specifics. Below is the sequel text. Any help you can
offer would be appreciated.

SELECT Demographics.VRS, Demographics.SSN, Demographics.[Last Name],
Demographics.[First Name], Demographics.Phone, Demographics.Age,
Demographics.[Service Connection], Referral.[Scheduling Deadline],
Referral.[Consult Deadline], IT.[IT End Deadline], SE.[SE End Deadline],
TWE.[TWE End Deadline]
FROM (((Demographics INNER JOIN IT ON Demographics.SSN = IT.SSN) INNER JOIN
Referral ON Demographics.SSN = Referral.SSN) INNER JOIN SE ON
Demographics.SSN = SE.SSN) INNER JOIN TWE ON Demographics.SSN = TWE.SSN
WHERE (((Demographics.Age)=[Forms]![Demographics]![Age]));

rbeach said:
As long as the form is open when the query is run, the field will
automatically be filled in with the information from the form. If the form is
not open, you will receive a dialog box.
--
Rick


Shel said:
I did this, and it created a parameter prompt for age, which I do not want.
Did I do something wrong?

:

On the form, go to the properties of the text box with the age results and
verify the name of the text box and use this for "Name" in the below formula.
In the criteria put the below formula.

=[Forms]![YourFormName]![Name]

Be sure the results will match the items in the table. Example: If the
results of the calculation is only the month/day and the table has
month/day/year then this will not give any results either.

--
Rick


:

I am writing a query from a table. This table pulls from a form that has an
age calculation in one field. This result does not store in the table, and I
have been told I need to create a new field in the query and insert this
calculation. When I put the calculation in the criteria field, it makes all
records vanish from the query. What am I doing wrong? In case it's helpful,
I'm including the sql view below:

SELECT Demographics.VRS, Demographics.SSN, Demographics.[Last Name],
Demographics.[First Name], Demographics.Phone, Demographics.Age,
Demographics.[Service Connection], Referral.[Scheduling Deadline],
Referral.[Consult Deadline], IT.[IT End Deadline], SE.[SE End Deadline],
TWE.[TWE End Deadline]
FROM (((Demographics INNER JOIN IT ON Demographics.SSN = IT.SSN) INNER JOIN
Referral ON Demographics.SSN = Referral.SSN) INNER JOIN SE ON
Demographics.SSN = SE.SSN) INNER JOIN TWE ON Demographics.SSN = TWE.SSN;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top