SQL to recordsource

[Guest]

Can you tell me how to apply SQL to recordsource of the form instead to the
SQL property of the query my form is based on. I am not sure how to do it.

Thanks.

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?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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]

In the form's module, after you have built the SQL string:
Me.RecordSource = strSql

It is generally a good idea to save the record in the form first:
If Me.Dirty Then
Me.Dirty = False
End If

If it is another form, replace Me with: Forms![NameOfYourFormhere]