Solved Check SQL for number (1 Viewer)

oxicottin

Learning by pecking away....
Local time
Today, 10:49
Joined
Jun 26, 2007
Messages
851
Hello I have a SQL that gives either a null or a number and I want to use it to determine opening a form. If the strSQL is null then give a message but if there is a number then open the form. How would this be written?

Code:
Dim strSQL As String

strSQL = "SELECT Count(tbl_YearCalendar.AbsenceDate) AS CountOfAbsenceDate " & vbCrLf & _
         "FROM tbluEmployees INNER JOIN tbl_YearCalendar ON tbluEmployees.EmployeeID = tbl_YearCalendar.EmployeeID " & vbCrLf & _
         "WHERE (((tbl_YearCalendar.EmployeeID)=[forms]![frm_YearCalendar]![cboEmployee]) AND ((Year([AbsenceDate]))=[forms]![frm_Switchboard]![cboYear]));"
 

Ranman256

Well-known member
Local time
Today, 10:49
Joined
Apr 9, 2015
Messages
4,339
put the sql in a query. then :

Code:
if dcount("*","qsMyQuery") = 0 then
   msgbox "no value"
else
   docmd.openform "fMyForm"
endif
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:49
Joined
Sep 21, 2011
Messages
14,044
Why do you have vbCrLf in the string? :unsure:
 

Minty

AWF VIP
Local time
Today, 14:49
Joined
Jul 26, 2013
Messages
10,354
Open a recordset based on that query (and as @Gasman said remove the crlf!)

Code:
Dim rs as Recordset

set rs = currentdb.openreordset(strSQL, dbopensnapshot)

If rs.recordcount = 0 then
  rs.close
  exit sub
end if

rs.close

docmd.openform "YourForm"
 

oxicottin

Learning by pecking away....
Local time
Today, 10:49
Joined
Jun 26, 2007
Messages
851
Thanks your suggestions worked...
 

Users who are viewing this thread

Top Bottom