SQL statements

[Senad]

Hi All,

I have the following statement behind the button in forms:
strSQL = "SELECT * FROM Stories WHERE (((Stories.Date) >=
[Forms]![Form1].[From] And (Stories.Date) <= [Forms]![Form1].[To]))ORDER BY
Stories.Date, Stories.Country;"

Now, I have to add one more condition to the SQL based on the user input
(check box) and I thought I can do it the following way:

If Me.BriefingStories = truth Then
strSQL = strSQL + "AND [stories].[briegin story]=truth"
End If
qdf.SQL = strSQL
DoCmd.OpenForm "AdvanceSearch"
DoCmd.Close acForm, "Form1", acSaveNo

Nice idea, but it does not work.
I would appreciate if someone could help.
Thanks
Senad

 

[Allen Browne]

Use # as the delimiter around the literal dates in your SQL statement, and
make sure they are formatted mm/dd/yyyy.

This example shows how to build up the WHERE clause from any number of text
boxes where the user has made an entry:
--------------------code starts------------------
Dim strSQL As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
Const strcStub = ""SELECT Stories.* FROM Stories"
const strcTail = " ORDER BY Stories.Date, Stories.Country;"

With Forms!Form1
If Not IsNull( ![From]) Then
strSQL = strSQL & "(Stories.Date >= " & _
Format( ![From], conJetDate) & ") AND "
End If

If Not IsNull( ![To]) Then
strSQL = strSQL & "(Stories.Date <= " & _
Format( ![To], conJetDate) & ") AND "
End If

If Me.BriefingStories = "truth" Then
strSQL = strSQL & "([stories].[briegin story] = ""truth"") AND "
End If

'etc for other text boxes.
End With

lngLen = Len(strSQL) - 5 'Without training " AND ".
If lngLen <= 0 Then 'no criteria
strSQL = strcStub & strcTail
Else
strSQL = strcStub & " WHERE " & Left$(strSQL, lngLen) & strcTail
End If

Debug.Print strSQL 'To see if it's okay.
--------------------code ends------------------

Notes:
1. If the text boxes are unbound, set their Format property to Short Date or
similar so Access recognises the data type, and invalid dates are not
accepted.

2. Presumably you are writing this to the SQL property of the QueryDef that
your AdvancedSearch form is based on? Might it be easier to just apply the
SQL string to the RecordSource of the form?