no records when using "Like" and Referencing a Form (1 Viewer)

Manos39

Registered User.
Local time
Yesterday, 22:33
Joined
Feb 14, 2011
Messages
248
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
Code:
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
Code:
Like "*" & [Forms]![Fr_ScheduleEditor]![cboCource] & "*"
works fine
then after if plus previous requeried by
Code:
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..
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:33
Joined
Aug 30, 2003
Messages
36,118
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

AWF VIP
Local time
Yesterday, 21:33
Joined
Mar 9, 2014
Messages
5,424
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

Registered User.
Local time
Yesterday, 22:33
Joined
Feb 14, 2011
Messages
248
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

Registered User.
Local time
Yesterday, 22:33
Joined
Feb 14, 2011
Messages
248
This is my db and form is Fr_ScheduleEditor

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

Attachments

  • Apollon Club 10-5-2022.accdb
    5.7 MB · Views: 158

Manos39

Registered User.
Local time
Yesterday, 22:33
Joined
Feb 14, 2011
Messages
248
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:33
Joined
Feb 19, 2002
Messages
42,981
Like IIf(IsNull([Forms]![Fr_ScheduleEditor]![cboCourseDate]);"_ ;[Forms]![Fr_ScheduleEditor]![cboCourseDate]")
LIKE is used with STRING values and is useless without wildcards. Dates are NOT strings. They are double precision numbers.

I also do not understand what you are trying to do here.

When working with dates, you either want a specific date, in which case you would use the = relational operator and not LIKE. If you want a range, you would use Between.

Where SomeDate Between Forms!yourform!StartDate AND Forms!yourform!EndDate
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:33
Joined
Sep 21, 2011
Messages
14,048
Is this not the same issue?, certainly you were advised like does not work with dates?
 

June7

AWF VIP
Local time
Yesterday, 21:33
Joined
Mar 9, 2014
Messages
5,424
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:33
Joined
Feb 19, 2002
Messages
42,981
I don't use dynamic parameterized queries.
Querydefs with parameters are static SQL, NOT dynamic. The static/dynamic refers to the structure of the query rather than to the value passed to an argument. A dynamic query has something structural is not fixed such as the columns in the select clause or the relational operators in the where clause or whether you want Top 10 or Top 5. There is no way to change 10 to 5 in a Querydef. You would need to create the SQL using VBA. That is Dynamic SQL. The other difference is that static SQL (i.e. querydefs) are compiled the first time the query is run and its execution plan is saved and reused. Dynamic SQL is always created using VBA or COBOL or whatever language you are coding in and is compiled every time you run it. So if you open the same form 100 times during the day, the execution plan is recreated 100 times whereas if you are using a querydef, the same execution plan is reused. So, technically querydefs are more efficient but there are gotchas.

In the old days when I was using COBOL against IBM's DB2, compilation was a two step process. First you compiled the static SQL to create the execution plans. Then you compiled the COBOL code. Then you ran the Link-Edit step which combined all the parts into the executable. Dynamic queries were compiled on the fly as they are using VBA.
 
Last edited:

June7

AWF VIP
Local time
Yesterday, 21:33
Joined
Mar 9, 2014
Messages
5,424
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:33
Joined
Feb 19, 2002
Messages
42,981
Dynamic and Static have specific meanings when referring to SQL and that is what I described. A query that takes a parameter is not dynamic. Whether a query is looking for ID = 1 or 224 is irrelevant. The execution plan would be the same. I guess another way to look at the difference is that for Static SQL, the execution plan is always the same unless the schema changes or the number of rows goes from something small that would typically be held entirely in memory to something larger. When the SQL is dynamic, the execution plan could be different.
 

Users who are viewing this thread

Top Bottom