no records when using "Like" and Referencing a Form

[Manos39]

Hi all,
i wish i knew what is wrong with the query supposed i need returning records
Main form called Fr_ScheduleEditor, subform Frmsub_CourseLastRecords.
Subform has a single table as rowsourse, managed by

SELECT Tbl_CourseSessions.courseID, Tbl_CourseSessions.instructorID, Tbl_CourseSessions.CourseDate, Tbl_CourseSessions.CourseTime, Tbl_CourseSessions.courtID
FROM Tbl_CourseSessions
WHERE (((Tbl_CourseSessions.courseID) Like "*" & [Forms]![Fr_ScheduleEditor]![cboCource] & "*") AND ((Tbl_CourseSessions.CourseDate) Like IIf(IsNull([Forms]![Fr_ScheduleEditor]![cboCourseDate]),"_ ;[Forms]![Fr_ScheduleEditor]![cboCourseDate]")))
ORDER BY Tbl_CourseSessions.courseID, Tbl_CourseSessions.CourseDate;

Subform needs to load with all records and then by two criteria from combos from main form, needs to be requeried.
For first criteria for courseID, alone

Like "*" & [Forms]![Fr_ScheduleEditor]![cboCource] & "*"

works fine
then after if plus previous requeried by

Like IIf(IsNull([Forms]![Fr_ScheduleEditor]![cboCourseDate]);"_ ;[Forms]![Fr_ScheduleEditor]![cboCourseDate]")

( expect to select date ) no records are shown sinse 2cond criteria is put in query..

 

[pbaldy]

Does this help?

Queries: Use a parameter to return all records if Null

 

[Manos39]

Does this help?

Queries: Use a parameter to return all records if Null

Thank you i tried no..

 

[pbaldy]

It might help to see how you tried to apply it. To be honest I don't really understand how the IIf() is supposed to work. I never use them in criteria.

 

[June7]

You mean subform has a single table as "recordsource", not rowsource?

Is the SQL in a query object or directly in RecordSource property?

I don't understand this syntax "_ ;[Forms]![Fr_ScheduleEditor]![cboCourseDate]". What is purpose of underscore and why is the combobox reference within quote marks?

 

[Manos39]

You mean subform has a single table as "recordsource", not rowsource?

Is the SQL in a query object or directly in RecordSource property?

I don't understand this syntax "_ ;[Forms]![Fr_ScheduleEditor]![cboCourseDate]". What is purpose of underscore and why is the combobox reference within quote marks?

I am sorry it is recordsourse

 

[Manos39]

This is my db and form is Fr_ScheduleEditor

I keep trying things so it has some changes still no luck though

 

[Manos39]

You mean subform has a single table as "recordsource", not rowsource?

Is the SQL in a query object or directly in RecordSource property?

I don't understand this syntax "_ ;[Forms]![Fr_ScheduleEditor]![cboCourseDate]". What is purpose of underscore and why is the combobox reference within quote marks?

quote marks was a way to show it like instead press CODE

 

[June7]

The form seems to work just fine. I don't use dynamic parameterized queries. I prefer to use VBA to build filter criteria. Review http://allenbrowne.com/ser-62.html

 

[Gasman]

Is this not the same issue?, certainly you were advised like does not work with dates?

Solved - Syntax error from clause

I cannot correct it now with Private Sub cmdAllAthletes_Click() Dim s1 As String s1 = "" s1 = s1 & "INSERT INTO Tbl_CourseAttendances " s1 = s1 & "(courseID, CourseDate, athleteID) " s1 = s1 & " SELECT Parent.[courseID], " & Format(Parent.[CourseDate], "\#yyyy\/mm\/dd\#") & "," s1 = s1 & "...

www.access-programmers.co.uk

 

[June7]

LIKE and wildcard will work with number and date/time fields, I mean it won't error, however, the output might not be what is expected.

As far as I can tell, the form in question and its parameterized query are working correctly.

 

[June7]

That's one way to define dynamic SQL.

I was referring to query objects with non-static filter criteria, i.e., parameters that utilize popup input or reference to controls on form. That is 'dynamic' input.