Parameter queries to limit records selected in computed field 1

[uscitizen]

my table contains "On Study Date" and "Days" for each record. my query computes "Next F/U Due: [On Study Date] + [Days]" but when i enter the following criterion "Between [Earliest] And [Latest]" as the criterion for "Next F/U Due" and enter two dates, it gives me dates that lie outside the range specified?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[lespaul]

What is your SQL for the query?

Leslie

 

[uscitizen]

my sql is

SELECT [Patients on F/U].[Last Name], [Formula Table].Days, [Patients on F/U].[On-Study Date], [On-Study Date]+[Days] AS [Next F/U Due]
FROM [Formula Table] INNER JOIN [Patients on F/U] ON [Formula Table].[Dummy Number] = [Patients on F/U].[Dummy Number]
WHERE ((([On-Study Date]+[Days]) Between [Earliest] And [Latest]))
ORDER BY [Patients on F/U].[Last Name], [Formula Table].Days;

but i wonder if it doesn't have something to do with formatting or 'converting' the value of 'Next F/U Due'?


“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[lespaul]

So, is [On-Study Date] a Date Field? Are you trying to increment this date by the number of DAYS to come up with [Next F/U Due]? You'll need to use the DateAdd function instead of just adding those together.

maybe something like:

SELECT [Patients on F/U].[Last Name], [Formula Table].Days, [Patients on F/U].[On-Study Date], DateAdd("d", [On-Study Date],[Days]) AS [Next F/U Due]
FROM [Formula Table] INNER JOIN [Patients on F/U] ON [Formula Table].[Dummy Number] = [Patients on F/U].[Dummy Number]
WHERE (((DateAdd("d", [On-Study Date],[Days])) Between [Earliest] And [Latest]))
ORDER BY [Patients on F/U].[Last Name], [Formula Table].Days;

Leslie

 

[uscitizen]

well, yes it is a date field. and i remembered seeming the DateAdd function after i posted my inquiry, and just for grins created another column which used it alongside the first one and they equated so i assumed it didn't matter. are you saying that it does?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[uscitizen]

i thought i'd go ahead and replace the sql with code above and still no cigar. using start/stop dates still gets a subset of records some of which are within and some of which are not within the range spec'd?!!?? weird.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[lespaul]

can you post some sample data and what kind of results you are getting? Does the field in the table include the time?

Leslie

 

[uscitizen]

haven't got a photographic memory so i'll have to wait 'til tomorrow am when i get back to the office. as to whether the time was or was not included as part of the date --- the dates are straight dates.

i have been giving some thought to the idea of converting the select query to a make table one (minus the begin/end parameter query) and then running a separate query on that table's data which includes the parameter query. am i just being superstitious? have you tried this out on some sample data of yours?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[uscitizen]

here are some hypothetical data:


Last Name Days On-Study Date Next F/U Due
Migraine 91 5/3/2004 8/2/2004
Migraine 182 5/3/2004 11/1/2004
Migraine 272 5/3/2004 1/30/2005
Migraine 365 5/3/2004 5/3/2005
Migraine 456 5/3/2004 8/2/2005
Migraine 547 5/3/2004 11/1/2005
Migraine 638 5/3/2004 1/31/2006
Migraine 730 5/3/2004 5/3/2006
Migraine 912 5/3/2004 11/1/2006
Migraine 1095 5/3/2004 5/3/2007
Migraine 1277 5/3/2004 11/1/2007
Migraine 1460 5/3/2004 5/2/2008
Migraine 1641 5/3/2004 10/30/2008
Migraine 1824 5/3/2004 5/1/2009
Migraine 2189 5/3/2004 5/1/2010
Migraine 2554 5/3/2004 5/1/2011
Migraine 2919 5/3/2004 4/30/2012
Migraine 3284 5/3/2004 4/30/2013
Migraine 3649 5/3/2004 4/30/2014


“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[uscitizen]

is there something in ms a2k that prevents a parameter query's operating on the fly (on a field as it's being computed). i'd almost be willing to bet that converting the select query to a make table query, and placing the parameter query under discussion on the field in a secondary query (that used the table generated by the 1st query) would work.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[lespaul]

that could work, I'd say give it a try!

leslie

 

[uscitizen]

the thing i find disconcerting is the fact that if it succeeds, i won't know why it didn't when i tried it w/in the select query :-(

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln