Solved Query to return objects without inspections in the year (1 Viewer)

tihmir

Registered User.
Local time
Today, 09:05
Joined
May 1, 2018
Messages
257
Hi,
I have 2 tables - tblObjects and tblInspections
tblObjects has object_id (primary key) and object_name.
tblInspections has inspection_id(INT primary key), inspection_name, inspection_date(Date/Time) and object_id (INT foreign key)
I need to build query to show me all objects that DO NOT have inspections in for example '2021'
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:05
Joined
May 7, 2009
Messages
19,169
select * from tblObjects Where object_id not in
(select object_id from tblInspections where year(inspection_date)=2021);

or

select T1.* from tblObjects As T1 Left Join
(Select * tblInspection Where Year(inspection_date) = 2021) As T2
On T1.object_id = T2.object_id
where ((T2.object_id) Is Null);
 

tihmir

Registered User.
Local time
Today, 09:05
Joined
May 1, 2018
Messages
257
select * from tblObjects Where object_id not in
(select object_id from tblInspections where year(inspection_date)=2021);

or

select T1.* from tblObjects As T1 Left Join
(Select * tblInspection Where Year(inspection_date) = 2021) As T2
On T1.object_id = T2.object_id
where ((T2.object_id) Is Null);

Thanks!
Works perfectly. I just added "FROM" into the nested SELECT.
And now how can I replace that hardcord value for example '2021'
with function to use that query in filter form?
 

June7

AWF VIP
Local time
Today, 08:05
Joined
Mar 9, 2014
Messages
5,423
Could just reference a textbox or combobox on form.

WHERE Year(inspection_date)=Forms!formname.cbxYear
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:05
Joined
May 7, 2009
Messages
19,169
you can also add a Parameter to your query:

SELECT T1.*
FROM tblObjects AS T1
LEFT JOIN
(SELECT * FROM tblInspections WHERE Year(inspection_date)=[enter year (YYYY)]) AS T2
ON T1.object_id = T2.object_id
WHERE (((T2.object_id) Is Null))
 

June7

AWF VIP
Local time
Today, 08:05
Joined
Mar 9, 2014
Messages
5,423
Yes, but cannot validate input to popup prompt.
 

tihmir

Registered User.
Local time
Today, 09:05
Joined
May 1, 2018
Messages
257
Could just reference a textbox or combobox on form.

WHERE Year(inspection_date)=Forms!formname.cbxYear
I reference a txtDate (Short Date) as you advised me, but in send me error "Syntax error in query expression YEAR (inspection_date)= Forms!FormName.txtDate
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:05
Joined
Feb 19, 2013
Messages
16,553
I reference a txtDate (Short Date)
surely user is just entering '2020', '2021', whatever, so not a date but a number

if they are actually entering a date then

YEAR(inspection_date)= datepart("yyyy",Forms!FormName.txtDate)
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:05
Joined
Sep 21, 2011
Messages
14,044
I reference a txtDate (Short Date) as you advised me, but in send me error "Syntax error in query expression YEAR (inspection_date)= Forms!FormName.txtDate
The prompt shows yyyy? :-(
 

tihmir

Registered User.
Local time
Today, 09:05
Joined
May 1, 2018
Messages
257
The query works perfect, but I want to use that queryin my form (SplitForm) with 2 text boxes (txtDateFrom and txtDateTo) to filter the period for which the objects are without checks.
How to do it in vba?
I usually use this code
Code:
    Dim strWhere As String
    Dim lngLen As Long
    Const conJetDate = "\#mm\/dd\/yyyy\#"

    If Not IsNull(Me.txtDateFrom) Then
        strWhere = strWhere & "([inspection_date] >= " & Format(Me.txtDateFrom, conJetDate) & ") AND "
    End If
   
    If Not IsNull(Me.txtDateTo) Then
        strWhere = strWhere & "([inspection_date] < " & Format(Me.txtDateTo + 1, conJetDate) & ") AND "
    End If
   
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "Attention.......", vbInformation, "Attention!"
    Else
        strWhere = Left$(strWhere, lngLen)
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
but now that I don't have dates in the field, I don't know how to do it
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:05
Joined
Feb 19, 2013
Messages
16,553
have an unbound control on your form called txtYear (to be populated by the user with 2020, 2021, whatever)

in your code

Code:
If Not IsNull(Me.txtYear) Then
    strWhere = strWhere & "year([inspection_date]) = " & txtYear
End If
 

tihmir

Registered User.
Local time
Today, 09:05
Joined
May 1, 2018
Messages
257
have an unbound control on your form called txtYear (to be populated by the user with 2020, 2021, whatever)

in your code

Code:
If Not IsNull(Me.txtYear) Then
    strWhere = strWhere & "year([inspection_date]) = " & txtYear
End If
It send me Run-time Error 3075 in Query expression (missing operator) in query 'year([inspection_date]) = '.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:05
Joined
Feb 19, 2013
Messages
16,553
well you haven't said whether you are using a number or a date but either way, there is no value
 

tihmir

Registered User.
Local time
Today, 09:05
Joined
May 1, 2018
Messages
257
It's done! Solved!
Thank you very much guys - arnelgp, June7, CJ_London, Gasman.

Code:
Private Sub cboYear_AfterUpdate()
    
    Dim SQL As String
    Dim Cancel As Integer
    
    If cboYear > Year(Now()) Then
        MsgBox "Attention...............", vbInformation, "Attention!"
        cboYear = Null
        cboYear.SetFocus
        Cancel = True
        Me!frm_formName_subform.Form.RecordSource = "qry_Objects_Without_Inspections"
        Exit Sub
    Else
        SQL = "SELECT T1.*, T2.DateInspection FROM tbl_Objects AS T1 LEFT JOIN" _
        & "(SELECT * FROM tbl_Inspections WHERE Year(DateInspection)=" & Me.cboYear & " )  AS T2 ON T1.ObjectID = T2.ObjectID WHERE (((T1.status )='ACTING') AND ((T2.ObjectID) Is Null))"
    
        Me.frm_formName_subform.Form.RecordSource = SQL
        Me.frm_formName_subform.Form.Requery
    End If
    
End Sub
 

Users who are viewing this thread

Top Bottom