Need a little help with Query by form

[henniec]

Good day,

I have a query form to assists others in extracting data from tables/queries. It is not used often but once in awhile someone will try to use the form.

I tried it again this morning as I needed to import it into a different database using different data. I tried to extract data for a time period and used two different criteria with the date. The result is not exactly what I want.

Doing it manualy showed me the error in the sql for the first query

Sql form QBF
SELECT Sample, SampleDate, CorrTSS FROM qryTSSCorrected WHERE Sample = "Wednesday" Or Sample = "Friday" And SampleDate >= #2008/01/01#

Sql from Manual query

SELECT qryTSSCorrected.Sample, qryTSSCorrected.SampleDate, qryTSSCorrected.TSS, qryTSSCorrected.CorrTSS
FROM qryTSSCorrected
WHERE (((qryTSSCorrected.Sample)="Wednesday") AND ((qryTSSCorrected.SampleDate)>=#1/1/2008#)) OR (((qryTSSCorrected.Sample)="Friday") AND ((qryTSSCorrected.SampleDate)>=#1/1/2008#));

I need to place a second date in the criteria grid. How could I change the code to add the second date in the criteria line?

This is part of the code to to create the above sql.

If Not IsNull(Me("cboField" & iCount)) And _
Not IsNull(Me("cboComp" & iCount)) And _
Not IsNull(Me("txtValue" & iCount)) Then

strSQL = " " & strSQL & Me("cboField" & iCount).Value & " " & Me("cboComp" & iCount).Value _
& " " & Me("txtValue" & iCount).Value & " "
Debug.Print strSQL

If Me("cboConnect" & iCount) = "" Then
'Do nothing
Else
strSQL = strSQL & " " & Me("cboConnect" & iCount).Value & " "
End If
Debug.Print strSQL

End If

Is there a way to do it by code to achieve the second sql or shoud I just increase the number of fields on my form. I have five rows now, four that start with a choice of and/or.

Thank you for your time.

Hennie

 

[PHV]

the Sql form QBF should be:
SELECT Sample, SampleDate, CorrTSS FROM qryTSSCorrected WHERE Sample IN ('Wednesday','Friday') And SampleDate >= #2008/01/01#

Or at least use proper parentheses:
SELECT Sample, SampleDate, CorrTSS FROM qryTSSCorrected WHERE (Sample = "Wednesday" Or Sample = "Friday") And SampleDate >= #2008/01/01#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[henniec]

PHV,

Thank you for your reply. The problem changing it to your format I will need to change the format of the query form. As it is now there are 4 criteria boxes. Connector (OR or AND) Field (Combo box for selecting the criteria, (date and Sample), combo Box for Comparison (>, <, etc.) and last unbound field, Value, for the parameter.

It seems to me I should include IN in the Comparison combo box and perhaps in the Value box try to accept more than one value.

Thanks. I will pursue this avenue.

Hennie