A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Reload this Page yes/no or both Error 3071
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

yes/no or both Error 3071


« Previous Thread | Next Thread »

 
 
Thread Tools Display Modes
  #1  
Old December 15th, 2009, 05:05 PM posted to microsoft.public.access.queries
javablood
external usenet poster
  
Posts: 84
Default yes/no or both Error 3071
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,
--
javablood
  #2  
Old December 16th, 2009, 05:31 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
  
Posts: 10,767
Default yes/no or both Error 3071
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" wrote:

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,
--
javablood
  #3  
Old December 16th, 2009, 06:48 PM posted to microsoft.public.access.queries
javablood
external usenet poster
  
Posts: 84
Default yes/no or both Error 3071
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" wrote:

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" wrote:

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,
--
javablood
 



« Previous Thread | Next Thread »

Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:20 PM.

Feed Icon - Contact Us - OfficeFrustration excel & word help home - FAQ - Links - Privacy Statement - Top

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.