Solved Check SQL for number

[oxicottin]

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?

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]

put the sql in a query. then :

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

 

[Gasman]

Why do you have vbCrLf in the string?

 

[Minty]

Open a recordset based on that query (and as @Gasman said remove the crlf!)

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]

Thanks your suggestions worked...