using a calculation in a query

[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;

 

[rbeach]

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.

 

[Shel]

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

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;

 

[Shel]

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;

 

[rbeach]

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

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;

 

[KARL DEWEY]

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

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

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;

 

[Shel]

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

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

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

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;

 

[Shel]

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

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

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;

 

[KARL DEWEY]

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

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

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

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;