Here's an interesting one. I have a crosstab query that uses a query, that uses another query which has a date field with a criteria that states the date must be between the dates the user enters in two text boxes in a form. Problem is, the report I run is based off the original crosstab query I referrenced above and is run when the underlying macros assigned to a button on the same form as the text boxes containing the dates the user enters is pushed. The error I'm getting is that the system cannot find the dates in the text boxes. I think the problem is that the crosstab query that the report is connected to is trying to grab records from the first query which are being determined by the form. This is some sort of messed up loop that won't work. Basically, when I try to link the text boxes for the detail in my report it can't find the date fields because they are linked to a closed form, and by the time the user gets the form open and enters the dates it's too late because the report is not set up right. Any suggestions on how to make this work correctly? Really I need to get dates in through a form that will feed the right records to my crosstab query wich will populate my report. Thanks!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Query Criteria Based on Form Data 2
- Thread starter pete1505
- Start date
-
1
- #2
go thru each query and in the PARAMETERS, put in the date text boxes from the form. Do this by opening the queries in Design View, right-click up in the grey area above the query grid, and choose PARAMTERS. put in something like
[Forms]![FormName]![txtBoxName] Date
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
[Forms]![FormName]![txtBoxName] Date
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
- Thread starter
- #3
- Thread starter
- #5
Actually, GingerR, for some reason this works for all dates up to 8/1/2004. All prior dates the report works just fine, but for any date after this I get an error that says "The Microsoft Jet Databae engine does not recognize " as a valid field name or expression". And then the thing blows up. Do you know what that is about. I looked through all my queries, macros, and parameters and I couldn't figure it out. Why would it work for those dates but nothing later?
- Thread starter
- #6
does the first query work?
does everything work if you type in dates instead of referencing the form controls?
if YES and YES, please post your queries here.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
does everything work if you type in dates instead of referencing the form controls?
if YES and YES, please post your queries here.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
- Thread starter
- #8
If I run the queries alone using typed in dates (not referencing the form controls), they work fine. So Yes, and Yes. Here are my queries:
Query 1: "FacNum5"
PARAMETERS [Forms]![Filter Form - Completed Facilites]![R1] DateTime, [Forms]![Filter Form - Completed Facilites]![R2] DateTime;
SELECT [S - Completed Facilities Query].WBS, [S - Completed Facilities Query].C_Name, [S - Completed Facilities Query].Number, [S - Completed Facilities Query].F_Name, [S - Completed Facilities Query].FT_Name, [S - Completed Facilities Query].MaxOfRec_Date
FROM [S - Completed Facilities Query]
GROUP BY [S - Completed Facilities Query].WBS, [S - Completed Facilities Query].C_Name, [S - Completed Facilities Query].Number, [S - Completed Facilities Query].F_Name, [S - Completed Facilities Query].FT_Name, [S - Completed Facilities Query].MaxOfRec_Date
HAVING ((([S - Completed Facilities Query].MaxOfRec_Date) Between [Forms]![Filter Form - Completed Facilites]![R1] And [Forms]![Filter Form - Completed Facilites]![R2]));
Query 2: "F - Completed Facilities Summary"
PARAMETERS [Forms]![Filter Form - Completed Facilites]![R1] DateTime, [Forms]![Filter Form - Completed Facilites]![R2] DateTime;
SELECT [FacNum(5)].WBS, [FacNum(5)].C_Name, Sum([FacNum(5)].Number) AS SumOfNumber, [FacNum(5)].FT_Name
FROM [FacNum(5)]
GROUP BY [FacNum(5)].WBS, [FacNum(5)].C_Name, [FacNum(5)].FT_Name;
Query 3: "F - Completed Facilities Summary Crosstab"
PARAMETERS [Forms]![Filter Form - Completed Facilites]![R1] DateTime, [Forms]![Filter Form - Completed Facilites]![R2] DateTime;
TRANSFORM Sum([F - Completed Facilities Summary].SumOfNumber) AS SumOfSumOfNumber
SELECT [F - Completed Facilities Summary].WBS, [F - Completed Facilities Summary].C_Name, Sum([F - Completed Facilities Summary].SumOfNumber) AS [Total Of SumOfNumber]
FROM [F - Completed Facilities Summary]
GROUP BY [F - Completed Facilities Summary].WBS, [F - Completed Facilities Summary].C_Name
PIVOT [F - Completed Facilities Summary].FT_Name;
Thanks again for your help. This one is perplexing to me.
Query 1: "FacNum5"
PARAMETERS [Forms]![Filter Form - Completed Facilites]![R1] DateTime, [Forms]![Filter Form - Completed Facilites]![R2] DateTime;
SELECT [S - Completed Facilities Query].WBS, [S - Completed Facilities Query].C_Name, [S - Completed Facilities Query].Number, [S - Completed Facilities Query].F_Name, [S - Completed Facilities Query].FT_Name, [S - Completed Facilities Query].MaxOfRec_Date
FROM [S - Completed Facilities Query]
GROUP BY [S - Completed Facilities Query].WBS, [S - Completed Facilities Query].C_Name, [S - Completed Facilities Query].Number, [S - Completed Facilities Query].F_Name, [S - Completed Facilities Query].FT_Name, [S - Completed Facilities Query].MaxOfRec_Date
HAVING ((([S - Completed Facilities Query].MaxOfRec_Date) Between [Forms]![Filter Form - Completed Facilites]![R1] And [Forms]![Filter Form - Completed Facilites]![R2]));
Query 2: "F - Completed Facilities Summary"
PARAMETERS [Forms]![Filter Form - Completed Facilites]![R1] DateTime, [Forms]![Filter Form - Completed Facilites]![R2] DateTime;
SELECT [FacNum(5)].WBS, [FacNum(5)].C_Name, Sum([FacNum(5)].Number) AS SumOfNumber, [FacNum(5)].FT_Name
FROM [FacNum(5)]
GROUP BY [FacNum(5)].WBS, [FacNum(5)].C_Name, [FacNum(5)].FT_Name;
Query 3: "F - Completed Facilities Summary Crosstab"
PARAMETERS [Forms]![Filter Form - Completed Facilites]![R1] DateTime, [Forms]![Filter Form - Completed Facilites]![R2] DateTime;
TRANSFORM Sum([F - Completed Facilities Summary].SumOfNumber) AS SumOfSumOfNumber
SELECT [F - Completed Facilities Summary].WBS, [F - Completed Facilities Summary].C_Name, Sum([F - Completed Facilities Summary].SumOfNumber) AS [Total Of SumOfNumber]
FROM [F - Completed Facilities Summary]
GROUP BY [F - Completed Facilities Summary].WBS, [F - Completed Facilities Summary].C_Name
PIVOT [F - Completed Facilities Summary].FT_Name;
Thanks again for your help. This one is perplexing to me.
No problem. perplexing me too 
What is "S - Completed Facilities Query" ?
In the second query, you reference "[FacNum(5)]" however the first query is named "FacNum5"?
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
What is "S - Completed Facilities Query" ?
In the second query, you reference "[FacNum(5)]" however the first query is named "FacNum5"?
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
- Thread starter
- #10
S - Completed Facilities Query" is actually the query that FacNum(5) is based on. Forgot to mention that. That query just gives me a list of facilities that have been comleted out of my overall list of all facilities in varying states of completion. FacNum(5) was then created to try to filter out only those facilities that were completed within a certain time-frame (hence the date entry controls on the form I've talked about). The next query, "F - Completed Facilities Summary", then sums up the number of facilities completed by facility type to get a total number completed for each facility type. The last query is just a crosstab on the prior one to get the number of completed facilities within the specified time frame by facility type and county in which the facility resides.
The FacNum(5) question you had was my error. I meant in my prior post to call it FacNum(5), NOT FacNum5.
It's really strange to me that the queries work when run standing alone by entering in parameter dates. All dates work and the correct numbers are spit out. For some reason though, when I try to run the report it pukes when I enter a date later than 7/18/2004! And why would it be telling me that " is not a valid field name or expression? I'm stumped!
The FacNum(5) question you had was my error. I meant in my prior post to call it FacNum(5), NOT FacNum5.
It's really strange to me that the queries work when run standing alone by entering in parameter dates. All dates work and the correct numbers are spit out. For some reason though, when I try to run the report it pukes when I enter a date later than 7/18/2004! And why would it be telling me that " is not a valid field name or expression? I'm stumped!
please post sql of "S - Completed Facilities Query"
when you (manually type in) make the criteria a date later than 7/18/04 does it work?
is there anything wrong with any of the dates in your table?
all i can tell you at this point is to start with the basic/first query, run it. put dates in the form. run it. take out parameters and type dates manually in. run it. do the same to the next query, and so forth, until you hit which one has the problem.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
when you (manually type in) make the criteria a date later than 7/18/04 does it work?
is there anything wrong with any of the dates in your table?
all i can tell you at this point is to start with the basic/first query, run it. put dates in the form. run it. take out parameters and type dates manually in. run it. do the same to the next query, and so forth, until you hit which one has the problem.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
- Thread starter
- #12
S - All Facilities Query":
SELECT TASK_ORDER.L_Name, COUNTRY.WBS, FACILITY.C_Name, FACILITY.F_Name, Count(DELIVERABLE.[Del_#]) AS [CountOfDel_#], DELIVERABLE_TYPE.Del_Code, Sum(FACILITY_DELIVERABLE.FD_Amount) AS SumOfFD_Amount, FACILITY.FT_Name, FACILITY.Number
FROM (DELIVERABLE_TYPE INNER JOIN DELIVERABLE ON DELIVERABLE_TYPE.Del_Code = DELIVERABLE.Del_Code) INNER JOIN ((COUNTRY INNER JOIN FACILITY ON COUNTRY.C_Name = FACILITY.C_Name) INNER JOIN (FACILITY_DELIVERABLE INNER JOIN TASK_ORDER ON FACILITY_DELIVERABLE.[Cont_#] = TASK_ORDER.[Cont_#]) ON (FACILITY.F_Name = FACILITY_DELIVERABLE.F_Name) AND (COUNTRY.C_Name = TASK_ORDER.C_Name)) ON (TASK_ORDER.[Cont_#] = DELIVERABLE.[Cont_#]) AND (DELIVERABLE.[Cont_#] = FACILITY_DELIVERABLE.[Cont_#]) AND (DELIVERABLE.[Del_#] = FACILITY_DELIVERABLE.[Del_#])
GROUP BY TASK_ORDER.L_Name, COUNTRY.WBS, FACILITY.C_Name, FACILITY.F_Name, DELIVERABLE_TYPE.Del_Code, FACILITY.FT_Name, FACILITY.Number
HAVING (((DELIVERABLE_TYPE.Del_Code)="U"));
"S - Completed Facilities Query":
SELECT [S - All Facilities Query].WBS, [S - All Facilities Query].C_Name, [S - All Facilities Query].L_Name, [S - All Facilities Query].F_Name, [S - All Facilities Query].Number, [S - All Facilities Query].FT_Name, [S - All Facilities Query].SumOfFD_Amount, [S - FacDelRecDate].MaxOfRec_Date
FROM [S - FacDelRecDate] INNER JOIN [S - All Facilities Query] ON [S - FacDelRecDate].F_Name = [S - All Facilities Query].F_Name
WHERE ((([S - FacDelRecDate].CountOfHQ_Approved)=[S - All Facilities Query.CountOfDel_#]));
Wow, I'd forgotten how dependant my queries had become on each other. I'd give your suggestion a try, but I'm not sure it's the queries. Each one works just fine whether I use parameters or type the dates into the query. I get good numbers. The problem is coming when I try to access the query "F - Completed Facilities Summary Crosstab" by clicking on the button in my form which is supposed to bring it up in print preview. This is the query that the report is generated from. For some reason I can run this report by entering dates in the form and clicking the button up until that 7/18/2004 date. Anything past that and the report won't work. But the queries run alone give any date I enter in the code! I think it has something to do with the way I've set up the report, but I can't figure it out.
SELECT TASK_ORDER.L_Name, COUNTRY.WBS, FACILITY.C_Name, FACILITY.F_Name, Count(DELIVERABLE.[Del_#]) AS [CountOfDel_#], DELIVERABLE_TYPE.Del_Code, Sum(FACILITY_DELIVERABLE.FD_Amount) AS SumOfFD_Amount, FACILITY.FT_Name, FACILITY.Number
FROM (DELIVERABLE_TYPE INNER JOIN DELIVERABLE ON DELIVERABLE_TYPE.Del_Code = DELIVERABLE.Del_Code) INNER JOIN ((COUNTRY INNER JOIN FACILITY ON COUNTRY.C_Name = FACILITY.C_Name) INNER JOIN (FACILITY_DELIVERABLE INNER JOIN TASK_ORDER ON FACILITY_DELIVERABLE.[Cont_#] = TASK_ORDER.[Cont_#]) ON (FACILITY.F_Name = FACILITY_DELIVERABLE.F_Name) AND (COUNTRY.C_Name = TASK_ORDER.C_Name)) ON (TASK_ORDER.[Cont_#] = DELIVERABLE.[Cont_#]) AND (DELIVERABLE.[Cont_#] = FACILITY_DELIVERABLE.[Cont_#]) AND (DELIVERABLE.[Del_#] = FACILITY_DELIVERABLE.[Del_#])
GROUP BY TASK_ORDER.L_Name, COUNTRY.WBS, FACILITY.C_Name, FACILITY.F_Name, DELIVERABLE_TYPE.Del_Code, FACILITY.FT_Name, FACILITY.Number
HAVING (((DELIVERABLE_TYPE.Del_Code)="U"));
"S - Completed Facilities Query":
SELECT [S - All Facilities Query].WBS, [S - All Facilities Query].C_Name, [S - All Facilities Query].L_Name, [S - All Facilities Query].F_Name, [S - All Facilities Query].Number, [S - All Facilities Query].FT_Name, [S - All Facilities Query].SumOfFD_Amount, [S - FacDelRecDate].MaxOfRec_Date
FROM [S - FacDelRecDate] INNER JOIN [S - All Facilities Query] ON [S - FacDelRecDate].F_Name = [S - All Facilities Query].F_Name
WHERE ((([S - FacDelRecDate].CountOfHQ_Approved)=[S - All Facilities Query.CountOfDel_#]));
Wow, I'd forgotten how dependant my queries had become on each other. I'd give your suggestion a try, but I'm not sure it's the queries. Each one works just fine whether I use parameters or type the dates into the query. I get good numbers. The problem is coming when I try to access the query "F - Completed Facilities Summary Crosstab" by clicking on the button in my form which is supposed to bring it up in print preview. This is the query that the report is generated from. For some reason I can run this report by entering dates in the form and clicking the button up until that 7/18/2004 date. Anything past that and the report won't work. But the queries run alone give any date I enter in the code! I think it has something to do with the way I've set up the report, but I can't figure it out.
if you type in dates instead of referencing the form controls for dates past 7/18/24, does the report work? Sorry for sounding like i'm asking the same question over and over but the answer to this one isn't clear to me yet. And sorry for all the questions, it's just part of troubleshooting.
What the code in the OnClick event of your button?
SO your recordsource of your report is exactly this: "F-Completed Facilities Summary Crosstab"?
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
What the code in the OnClick event of your button?
SO your recordsource of your report is exactly this: "F-Completed Facilities Summary Crosstab"?
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
-
1
- #14
- Thread starter
- #15
Okay, I figured out why it doesn't work past that date. I was not doing what you were asking. I took out all the parameters and entered a date range of 7/19/2004 - 1/24/2005 directly in the FacNum(5) query. The report wouldn't run. When I went into the report design it was not able to find certain types of "Facility Types" in the current recordset, or the crosstab query that I've linked to the report. That is because during that time-frame there were 2 facility types that had NO facilities completed. in the past there were factilies in these types completed and that's why the report was working, but lately none of these certain types had facilities completed.
So......my new question now becomes......how do I show a zero in that row if the query does not find anything for that type given that I want to show all types in my crosstab query and report. I'm thinking it has to be something I program into the crosstab query that says "even if no facilities of a certain type were completed during the time frame X to Y, show all facility types in the query anyway." Any thoughts on this?
So......my new question now becomes......how do I show a zero in that row if the query does not find anything for that type given that I want to show all types in my crosstab query and report. I'm thinking it has to be something I program into the crosstab query that says "even if no facilities of a certain type were completed during the time frame X to Y, show all facility types in the query anyway." Any thoughts on this?
- Thread starter
- #16
PHV,
You were kind of correct. Actually the number is decreasing in this case and the report is wanting to show numbers in a column for which no data exists in the crosstab query during certain time-frames. I need to get my crosstab query to show a zero "0" for these facility types instead of omitting the facility type all together in the query since the report is showing column headings for each facility type whether facilities of that type were completed or not during that time frame. I want to make is show zero "0" in that case. Can that be done? Thanks both of you for your continued help today!
You were kind of correct. Actually the number is decreasing in this case and the report is wanting to show numbers in a column for which no data exists in the crosstab query during certain time-frames. I need to get my crosstab query to show a zero "0" for these facility types instead of omitting the facility type all together in the query since the report is showing column headings for each facility type whether facilities of that type were completed or not during that time frame. I want to make is show zero "0" in that case. Can that be done? Thanks both of you for your continued help today!
- Thread starter
- #18
- Thread starter
- #19
Currently the last part of the crosstab query code looks like this:
PIVOT [F - Completed Facilities Summary].FT_Name;
How would I tell it to list out the actual FT_Names as column headings and keep them there even if no data is coming through for those facility types from the other queries this one is getting it's info from?
PIVOT [F - Completed Facilities Summary].FT_Name;
How would I tell it to list out the actual FT_Names as column headings and keep them there even if no data is coming through for those facility types from the other queries this one is getting it's info from?
- Status
Similar threads
- Locked
- Question
