Check value using SQL and pass to form

[tractorvix]

Hi,

I seem to have a bit of a mental block with the code below. I basically need it to check the value of a field in a query and if that value is >35 then to do one thing and if it's not then to do another.

current code is

Option Compare Database

Private Sub Command6_Click()
Dim curhours As Long

'curhours = WHAT??!!
'I need this to pull the sumwkhours value from the qrytotalweekhours where field date = me.date
'have tried
'strsql = "SELECT qryweekhours.date, qryweekhours.sumwkhours FROM qryweekhours WHERE (((qryweekhours.date)=[Forms]![Form1]![date]));"

'DoCmd.RunSQL strsql
'but no joy

If Me.hours >= 35 Then
MsgBox "Schedule will split over " & (Me.hours / 35) & " weeks"
Else
If curhours >= 35 Then
fullinput = MsgBox("Selected week is full. Would you like the scheduler to assign to available time?", vbYesNo, "Busy Week!")
If fullinput = vbYes Then
PopSchedule
MsgBox "Start date has been set to " & newstartdate
Else
MsgBox "Please select new start date"
Me.date.SetFocus
End If
Else
End If



End If
End Sub

any guidance would be great.

Thanks

TV

 

[Golom]

You need to pull the result of your SQL into a recordset so that you can test it. RunSQL processes Action and Data Definition statements ... not Select statements.

Dim curhours As Long
Dim rs As DAO.Recordset

   strsql = "SELECT [date], sumwkhours FROM qryweekhours 
             WHERE [date]=[Forms]![Form1]![date]"

   Set rs = CurrentDB.Openrecordset ( strsql )
   If rs.EOF Then
      CurHours = 0
   Else
      CurHours = rs![sumwkhours]
   End If
   Set rs = Nothing

 

[tractorvix]

Thanks for your response Golem
I've actually tried using a DLookup instead as this seems a much simpler way of doing it, but for some reason it doesn't seem to want to match my dates. I assume that it's a format problem but have checked that both the form field and the query field are set to shortdate and still no joy.

curhours = DLookup("sumwkhours", "qryweekhours", "[date] = "
& Forms!Form1!date)

I just seem to get a null value back even if I populate with a date I know is in the qryweekhours table.

Any ideas?

TV

 

[tractorvix]

d'oh, incorrect spacing and quotes now works wonderfully with

curhours = DLookup("sumwkhours", "qryweekhours", "[date] = Forms!Form1!date")


hoorah!