Thank you for your patience ....
I have followed all of your notes, and can report:
1. Removing the criteria from Step 2 does provide identical results to Step
1. There is no error.
2. Changing the formula for KeeperFlag works in Step 1, and displays 0 or -1
for all records. Step 2 provides the same results until I put in a criteria.
I used <>0 as the criteria, and again got a data type mismatch error.
3. I also added the parameters statement to the SQL in both queries.
Without a criteria, Step 2 provides the same results as Step 1, but the
criteria <>0 still causes a data type mismatch.
Going back to Karl's "build a little, test a little" concept, I'm going to
start simplifying Step 1, removing the parameter for example, until I
identify the individual piece that causes my problem. Coincidentally, I
thought I was building a little, testing a little all along, by adding more
complex pieces to step 1 one at a time, and testing for results. It was only
that last piece, the criteria, that caused any problems, and seems to
implicate one of the earlier steps.
I'll respond again when I find the specific piece causing my problem. As a
side note, I've found that using a macro to run step 1 as a make table query,
and running step 2 on the new table works fine, albeit inefficiently, and
without teaching me why my two step select query doesn't work. Then again,
sometimes you need to get things working first, and worry about elegance
afterwards....
Thanks again.
Dale Fye said:
Mark,
The point, as Karl is so fond of saying, is to build a little, test a
little.
By deleting the WHERE clause from the 2nd query, we should get results
identical to those from Query 1. Do you get the same set of results as when
you run query 1 by itself?
If the whole point of the KeeperFlag field is to decide whether to include
that record in the 2nd phase of things, I think I would rewrite query1 with
the following:
cbool([MondayFlag] + [TuesdayFlag]) AS KeeperFlag
since any number other than a zero will be interpreted as True, if either
MondayFlag or TuesdayFlag = 1 then the sum of those fields will be one,
which will be interpreted as true.
Since you are using a parameter query, have you declared your parameter as a
date/time field using this string:
Parameter [Enter Monday Date: mm/dd/yyyy] DateTime;
This line should immediately preceed the SQL statement in SQL view, in BOTH
Query1 and Query2.
Dale
I'm sorry, but the whole point of the second query is to pull only the
records marked as "XXX". The fields from Step 1 are entirely unchanged,
except for the criteria to select "XXX" records, so removing the criteria
would mean Step 2 and Step 1 are exactly the same.
I'm trying to understand why, since Step 2 does nothing except apply
criteria, why the criteria causes a mismatch problem.
I understand your point regarding the calculations and aliases in step 1,
but doesn't separating the calculation query from the criteria query
ensure
that all the work from Step 1 is complete before Step 2 starts ? Is there
a
way to force more completion from Step 1 before Step 2 starts ?
Thanks for all your assistance !
:
Try removing the criteria from the second query --
WHERE ((([Task Library with Dates - Step 1].KeeperFlag)="XXX"))
I see that you have calculated fields with aliases used in the same query
that did the calculation. Sometimes the is a problem in that it tries
to
use the alias before it does the calculation.
--
KARL DEWEY
Build a little - Test a little
:
When the [Step 1] query runs, I do in fact see "XXX" and "YYY" as I
expect to
see them. Below is the SQL for Step 1 as Access provided it: (If I
had
known I was going to have to send this code for public analysis, I'd
have
used shorter names!)
SELECT Program_Library.[No], Program_Library.TeamMember,
Program_Library.Type, Program_Library.Task, Program_Library.StartDate,
Program_Library.Program, Program_Library.[MF output],
Program_Library.[PC
output], Program_Library.Distribution, Program_Library.[New system
idea],
Program_Library.Frequency, Program_Library.FrequencyOption,
RunDate([StartDate],[Frequency],[FrequencyOption]) AS RunDates, [Enter
Monday
Date: mm/dd/yyyy] AS PeriodStart,
IIf(InStr([RunDates],[PeriodStart])=0,0,1)
AS MondayFlag,
IIf(InStr([RunDates],DateAdd("d",1,[PeriodStart]))=0,0,1) AS
TuesdayFlag, IIf([MondayFlag]>0,"XXX",IIf([TuesdayFlag]>0,"XXX","YYY"))
AS
KeeperFlag
FROM Program_Library
WHERE (((Program_Library.Type)="REGULAR") AND
((Program_Library.Frequency)
Is Not Null));
:
What happens when you just run query [Task Library with Dates - Step
1]? Do
you see values for "XXX" and "YYY" in the [KeeperFlag] field?
Post the SQL for your query: [Task Library with Dates - Step 1]
--
Don''t forget to rate the post if it was helpful!
Email address is not valid.
Please reply to newsgroup only.
:
This is the SQL generated by Access:
SELECT [Task Library with Dates - Step 1].[No], [Task Library with
Dates -
Step 1].TeamMember, [Task Library with Dates - Step 1].Type, [Task
Library
with Dates - Step 1].Task, [Task Library with Dates - Step
1].StartDate,
[Task Library with Dates - Step 1].Program, [Task Library with
Dates - Step
1].[MF output], [Task Library with Dates - Step 1].[PC output],
[Task Library
with Dates - Step 1].Distribution, [Task Library with Dates - Step
1].[New
system idea], [Task Library with Dates - Step 1].Frequency, [Task
Library
with Dates - Step 1].FrequencyOption, [Task Library with Dates -
Step
1].RunDates, [Task Library with Dates - Step 1].PeriodStart, [Task
Library
with Dates - Step 1].MondayFlag, [Task Library with Dates - Step
1].TuesdayFlag, [Task Library with Dates - Step 1].KeeperFlag
FROM [Task Library with Dates - Step 1]
WHERE ((([Task Library with Dates - Step 1].KeeperFlag)="XXX"));
:
Post the offending query SQL.
--
KARL DEWEY
Build a little - Test a little
:
Folks:
This is such a simple query - and yet it's driving me crazy!
I have several fields in a table, and I use an iif function to
set a new
field based on the others. To remove any problems with data
type, and
confusions with Yes/No and "Yes"/"No", I'm setting my new field
to either
"XXX" or "YYY". The query views fine.
In a second query, based on the first, I include all the fields
and include
criteria "XXX" (in quotes) for my new field. I keep getting
the "Data type
mismatch" error. As a second query, I can count on the first
query being
complete, can't I? That would simply to imply to me that there
isn't any
issue (as I've seen in other discussions) with the order of SQL
statements.
Does anyone have a hint for me?
