yes/no or both Error 3071

J

javablood

I was following a thread on 11.19.09 by Owl and responded by Karl Dewey and
John Spencer and thought I had my answer but do not. I have a form in which
I have several combo boxes in which selections are made and input into a
query to get the records of interest. For the "Detected" field I want the
option of viewing the records in which there is a "Yes", "No", or both.

I tried Karl's solution as follows:

PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ),
[Forms]![frmSearch]![lbodatbegin] DateTime, [Forms]![frmSearch]![lbodatend]
DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ),
[Forms]![frmSearch]![cboSamptype] Text ( 255 ), [Forms]![frmSearch]![cboDet]
Bit;
SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE, tblHardage.PARAMETER,
tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS, tblHardagePAR.F1_GROUP,
tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain,
tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED
FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON
tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN
tblHardageSiteIdentification ON tblHardage.STATION_ID =
tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY =
tblHardage.QC
WHERE (((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin]
And [Forms]![frmSearch]![lbodatend]) AND
((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND
((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND
((tblHardageSiteIdentification.ID_Group_Explain)=[Forms]![frmSearch]![cboGroup])
AND ((tblHardage.DETECTED)=IIf([Forms]![frmSearch]![cboDet]="Yes",True,False)
Or (tblHardage.DETECTED) Like IIf([Forms]![frmSearch]![cboDet] Is
Null,"*",Null)))
ORDER BY tblHardagePAR.F1_GRPORDR;

but I get Error 3071. I also tried John's solution:

PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ),
[Forms]![frmSearch]![lbodatbegin] DateTime, [Forms]![frmSearch]![lbodatend]
DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ),
[Forms]![frmSearch]![cboSamptype] Text ( 255 ), [Forms]![frmSearch]![cboDet]
Bit;
SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE, tblHardage.PARAMETER,
tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS, tblHardagePAR.F1_GROUP,
tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain,
tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED, [Forms]![frmSearch]![cboDet]
AS Expr1
FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON
tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN
tblHardageSiteIdentification ON tblHardage.STATION_ID =
tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY =
tblHardage.QC
WHERE (((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin]
And [Forms]![frmSearch]![lbodatend]) AND
((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND
((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND
((tblHardageSiteIdentification.ID_Group_Explain)=[Forms]![frmSearch]![cboGroup])
AND (([Forms]![frmSearch]![cboDet])=Yes)) OR ((([Forms]![frmSearch]![cboDet])
Is Null))
ORDER BY tblHardagePAR.F1_GRPORDR;

but I do not think I did it correctly because I get the whole database with
Null; yes and no records with Yes; and no records with No.

I would appreciate any help offered to set me on the correct track.

Thanks,
 
K

KARL DEWEY

I tried Karl's solution ..... but I get Error 3071.
Try removing parts of the WHERE until you no longer get the error.

--
Build a little, test a little.


javablood said:
I was following a thread on 11.19.09 by Owl and responded by Karl Dewey and
John Spencer and thought I had my answer but do not. I have a form in which
I have several combo boxes in which selections are made and input into a
query to get the records of interest. For the "Detected" field I want the
option of viewing the records in which there is a "Yes", "No", or both.

I tried Karl's solution as follows:

PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ),
[Forms]![frmSearch]![lbodatbegin] DateTime, [Forms]![frmSearch]![lbodatend]
DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ),
[Forms]![frmSearch]![cboSamptype] Text ( 255 ), [Forms]![frmSearch]![cboDet]
Bit;
SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE, tblHardage.PARAMETER,
tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS, tblHardagePAR.F1_GROUP,
tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain,
tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED
FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON
tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN
tblHardageSiteIdentification ON tblHardage.STATION_ID =
tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY =
tblHardage.QC
WHERE (((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin]
And [Forms]![frmSearch]![lbodatend]) AND
((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND
((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND
((tblHardageSiteIdentification.ID_Group_Explain)=[Forms]![frmSearch]![cboGroup])
AND ((tblHardage.DETECTED)=IIf([Forms]![frmSearch]![cboDet]="Yes",True,False)
Or (tblHardage.DETECTED) Like IIf([Forms]![frmSearch]![cboDet] Is
Null,"*",Null)))
ORDER BY tblHardagePAR.F1_GRPORDR;

but I get Error 3071. I also tried John's solution:

PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ),
[Forms]![frmSearch]![lbodatbegin] DateTime, [Forms]![frmSearch]![lbodatend]
DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ),
[Forms]![frmSearch]![cboSamptype] Text ( 255 ), [Forms]![frmSearch]![cboDet]
Bit;
SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE, tblHardage.PARAMETER,
tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS, tblHardagePAR.F1_GROUP,
tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain,
tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED, [Forms]![frmSearch]![cboDet]
AS Expr1
FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON
tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN
tblHardageSiteIdentification ON tblHardage.STATION_ID =
tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY =
tblHardage.QC
WHERE (((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin]
And [Forms]![frmSearch]![lbodatend]) AND
((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND
((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND
((tblHardageSiteIdentification.ID_Group_Explain)=[Forms]![frmSearch]![cboGroup])
AND (([Forms]![frmSearch]![cboDet])=Yes)) OR ((([Forms]![frmSearch]![cboDet])
Is Null))
ORDER BY tblHardagePAR.F1_GRPORDR;

but I do not think I did it correctly because I get the whole database with
Null; yes and no records with Yes; and no records with No.

I would appreciate any help offered to set me on the correct track.

Thanks,
Click to expand...
 
J

javablood

Karl,

I had to change the IIF statement at the end to this:

((tblHardage.DETECTED) Like IIf([Forms]![frmSearch]![cboDet] Is
Null,"*",IIf([Forms]![frmSearch]![cboDet]=Yes,True,False))))

and it is working now.

thanks,
--
javablood


KARL DEWEY said:
Try removing parts of the WHERE until you no longer get the error.

--
Build a little, test a little.


javablood said:
I was following a thread on 11.19.09 by Owl and responded by Karl Dewey and
John Spencer and thought I had my answer but do not. I have a form in which
I have several combo boxes in which selections are made and input into a
query to get the records of interest. For the "Detected" field I want the
option of viewing the records in which there is a "Yes", "No", or both.

I tried Karl's solution as follows:

PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ),
[Forms]![frmSearch]![lbodatbegin] DateTime, [Forms]![frmSearch]![lbodatend]
DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ),
[Forms]![frmSearch]![cboSamptype] Text ( 255 ), [Forms]![frmSearch]![cboDet]
Bit;
SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE, tblHardage.PARAMETER,
tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS, tblHardagePAR.F1_GROUP,
tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain,
tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED
FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON
tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN
tblHardageSiteIdentification ON tblHardage.STATION_ID =
tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY =
tblHardage.QC
WHERE (((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin]
And [Forms]![frmSearch]![lbodatend]) AND
((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND
((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND
((tblHardageSiteIdentification.ID_Group_Explain)=[Forms]![frmSearch]![cboGroup])
AND ((tblHardage.DETECTED)=IIf([Forms]![frmSearch]![cboDet]="Yes",True,False)
Or (tblHardage.DETECTED) Like IIf([Forms]![frmSearch]![cboDet] Is
Null,"*",Null)))
ORDER BY tblHardagePAR.F1_GRPORDR;

but I get Error 3071. I also tried John's solution:

PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ),
[Forms]![frmSearch]![lbodatbegin] DateTime, [Forms]![frmSearch]![lbodatend]
DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ),
[Forms]![frmSearch]![cboSamptype] Text ( 255 ), [Forms]![frmSearch]![cboDet]
Bit;
SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE, tblHardage.PARAMETER,
tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS, tblHardagePAR.F1_GROUP,
tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain,
tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED, [Forms]![frmSearch]![cboDet]
AS Expr1
FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON
tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN
tblHardageSiteIdentification ON tblHardage.STATION_ID =
tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY =
tblHardage.QC
WHERE (((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin]
And [Forms]![frmSearch]![lbodatend]) AND
((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND
((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND
((tblHardageSiteIdentification.ID_Group_Explain)=[Forms]![frmSearch]![cboGroup])
AND (([Forms]![frmSearch]![cboDet])=Yes)) OR ((([Forms]![frmSearch]![cboDet])
Is Null))
ORDER BY tblHardagePAR.F1_GRPORDR;

but I do not think I did it correctly because I get the whole database with
Null; yes and no records with Yes; and no records with No.

I would appreciate any help offered to set me on the correct track.

Thanks,
Click to expand...
Click to expand...
 
Top