How to ask user to criteria

[QB]

I have the following query sql statement (it works fine)

SELECT tbl_cngu_fire.cngu_no, Max(tbl_cngu_fire.fire_date) AS fireDate,
DateAdd("yyyy",1,[fireDate]) AS Due,
DateDiff("d",Date(),DateAdd("yyyy",1,[fireDate])) AS [No Days Till Due] FROM
tbl_lst_sites RIGHT JOIN tbl_cngu_fire ON tbl_lst_sites.site_id =
tbl_cngu_fire.fire_site GROUP BY tbl_cngu_fire.cngu_no ORDER BY
tbl_cngu_fire.cngu_no;

Now however, I wish to allow the user to specify the boundary (no days - the
[No Days Till Due] field) to limit the query to.

I tried simply adding [Please enter the maximum number of days to display]
as the criteria for the [No Days Till Due] field in the query, but the when I
go to run it, it pops up an input box for the [fireDate] field which it never
did before. What is the proper way to approach this situation? What am I
missing?

Thank you,

QB

 

[Douglas J. Steele]

What you did should have been sufficient.

What's the SQL of the query now that you've made that change?

 

[John Spencer]

SELECT tbl_cngu_fire.cngu_no
, Max(tbl_cngu_fire.fire_date) AS fireDate
, DateAdd("yyyy",1,Max(tbl_cngu_fire.fire_date)) AS Due
, DateDiff("d",Date(),DateAdd("yyyy",1,Max(tbl_cngu_fire.fire_date))) AS [No
Days Till Due]
FROM
tbl_lst_sites RIGHT JOIN tbl_cngu_fire
ON tbl_lst_sites.site_id = tbl_cngu_fire.fire_site
GROUP BY tbl_cngu_fire.cngu_no
ORDER BY tbl_cngu_fire.cngu_no;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

Sorry, that was a rather incomplete answer. Access will not always allow you
to use a field alias in another calculated field. You have to redo the
calculation or use the actual value.

This is especially true if you intend to apply criteria to the second field.
Because, the alias is NOT available when the where (or having) clause of a
query executes. I would try making the query look like the following

SELECT tbl_cngu_fire.cngu_no
, Max(tbl_cngu_fire.fire_date) AS fireDate
, DateAdd("yyyy",1,Max(tbl_cngu_fire.fire_date)) AS Due
, DateDiff("d",Date(),DateAdd("yyyy",1,Max(tbl_cngu_fire.fire_date))) AS
[NoDaysTillDue]
FROM tbl_lst_sites RIGHT JOIN tbl_cngu_fire
ON tbl_lst_sites.site_id = tbl_cngu_fire.fire_site
GROUP BY tbl_cngu_fire.cngu_no
ORDER BY tbl_cngu_fire.cngu_no;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I have the following query sql statement (it works fine)

SELECT tbl_cngu_fire.cngu_no, Max(tbl_cngu_fire.fire_date) AS
fireDate, DateAdd("yyyy",1,[fireDate]) AS Due,
DateDiff("d",Date(),DateAdd("yyyy",1,[fireDate])) AS [No Days Till
Due] FROM tbl_lst_sites RIGHT JOIN tbl_cngu_fire ON
tbl_lst_sites.site_id = tbl_cngu_fire.fire_site GROUP BY
tbl_cngu_fire.cngu_no ORDER BY tbl_cngu_fire.cngu_no;

Now however, I wish to allow the user to specify the boundary (no days
- the [No Days Till Due] field) to limit the query to.

I tried simply adding [Please enter the maximum number of days to
display] as the criteria for the [No Days Till Due] field in the
query, but the when I go to run it, it pops up an input box for the
[fireDate] field which it never did before. What is the proper way to
approach this situation? What am I missing?
Thank you,

QB