I apologize, I did not intend to be confusing...unfortunately, I was confused
and have been dealing with this issue for several days.
You mentioned a calculation for 'Next Service Date' but not any criteria
on that date.
Any time I try to include a criteria, no records come back.
Criteria should limit 'records', not fields.
My bad, that's what I meant.
Below is the query SQL:
SELECT DateAdd("yyyy",5,[Svc_Date]) AS [Next Service Date], [TBL_Service
Orders].[Service Order], [TBL_Service Orders].Status, [TBL_Service
Orders].CompanyNameID, [TBL_Service Orders].ContactName, [TBL_Service
Orders].ContactPhone, [TBL_Service Orders].Svc_City, [TBL_Service
Orders].Svc_State, [TBL_Service Orders].Svc_Type, [TBL_Service
Orders].[Svc_Date], [TBL_Service Orders].[Date Complete]
FROM [TBL_Service Orders]
WHERE ((([TBL_Service Orders].[Svc_Type])="Electrical"))
ORDER BY DateAdd("yyyy",5,[Svc_Date]);
The Table is TBL_Service Orders. Field Names, data type and samples below
Service Order = Text = 999800789 (our internal job-tracking number)
Status = Text = C (complete), P (in progress)
CompanyNameID = Text = The client name (related to another table)
ContactName = Text = the specific contact name
ContactPhone = Text = contact phone
Svc_City = Text = city where the job was located
Svc_State = Text = 2-letter state abbreviation (related to another table)
Svc_Type = Text = Electrical, mechanical, etc. (related to another table)
Svc_Date = Date/Time = 1/1/10
Date Complete = Date/Time = 1/31/10
I've tried to set the criteria in numerous ways (Between...And - asking for
user input) and either the criteria is ignored and ALL results come back, or
NO results come back. Basically, I'd like to have user input to set the
beginning and ending dates of the records so that I don't see items that were
due last year and items that aren't due for another 10 years. What criteria
should I use to return the records I'd like?
Thanks for your help.
--
kim s
KARL DEWEY said:
The problem lies with the fact that the ALL results come back, Confused I
am. You mentioned a calculation for 'Next Service Date' but not any criteria
on that date.
......so as to limit the number of fields that are returned.
Criteria should limit 'records', not fields.
Every parameter I've tried results in the query running, but no data in the fields.
First thing above says 'ALL results come back', so which is it?
Post your query SQL bu opening in design view, click on 'SQL View',
highlight all, copy and paste in a post. Also provide table and field names
with datatype and sample data.
--
Build a little, test a little.
:
I'm working in Access 07 but creating a database that is 2003 compatible.
I am absolutely sure that I am over-thinking this but can't seem to resolve
my problem. I currently have a query that calculates the Next Service Date
due (5 years from the Service Date [Svc_Date]) correctly. My expression is
as follows:
Next Service Date: DateAdd("yyyy",5,[Svc_Date])
The problem lies with the fact that the ALL results come back, including
ones that were due in the past. I would like to be able to set a parameter
which allows for user input (i.e., Beginning Date and Ending Date), or
Services due this year only, so as to limit the number of fields that are
returned. Every parameter I've tried results in the query running, but no
data in the fields. Is there any way to do this?
Thanks for your help.
