[AccessD] Am I missing something simple? - function inquerycriteria

Thanks Andy / Gustav - the second part of your reply here solved it -

Many thanks

Kath


----- Original Message ----- 
  From: Gustav Brock 
  To: accessd at databaseadvisors.com 
  Sent: Friday, March 04, 2005 9:56 PM
  Subject: Re: [AccessD] Am I missing something simple? - function inquerycriteria


  Hit Kath

  This line is in error:

              "[AccountNo] = '" & StrAccountno & " '" & " And
  [reportcycleid] = " & IntRepCycleID))

  Should read:

              "[AccountNo] = '" & StrAccountno & "'" & " And
  [reportcycleid] = " & IntRepCycleID & ""))

  Also, this should read:

      GetTemplateld =
  [Forms]![FrmClientDetails]![NameOfYourSubformControl].Form![TemplateID]

  /gustav


  >>> KP at sdsonline.net 04-03-2005 04:52:55 >>>
  Hi all - 

  I am using a function in my query criteria. The function works and
  returns a value of  52275 (I can see that from the debug window).

  If I type in 52275 as the query criteria it rerurns a result, but if I
  use the function it doesn't. After much head banging I am looking for
  something basic I may be doing wrong. Any ideas? 

  Function is:

  Public Function GetTemplateld() As Long
  Dim strerrormsg As String
  Dim StrAccountno As String
  Dim IntRepCycleID As Integer
  'On Error GoTo Err_Handler

  If IsFormOpen("FrmClientDetails") = True Then
      GetTemplateld =
  [Forms]![FrmClientDetails]![FrmClientMarketValueHistorySubform]![TemplateID]

  ElseIf IsFormOpen("FrmReportCycleWorksheet_Master") = True Then
      GetTemplateld =
  [Forms]![FrmReportCycleWorksheet_Master]![TemplateID]
      
  ElseIf IsFormOpen("FrmMainMenu") = True Then
      StrAccountno = Forms![FrmMainMenu]![TxtReportingAccountNo]
      IntRepCycleID = Forms![FrmMainMenu]![TxtReportCycleID]
      gettemplateid = Nz(DLookup("[TemplateID]",
  "TblHistory_SubAccountTemplate", _
              "[AccountNo] = '" & StrAccountno & " '" & " And
  [reportcycleid] = " & IntRepCycleID))
  Else
      gettemplateid = 0
  End If

  Debug.Print gettemplateid

  Normal_exit:
      Exit Function
  Err_Handler:
      MsgBox "Error: [" & Err.Number & "]  " & IIf(Len(strerrormsg) > 0,
  strerrormsg, Err.Description), vbCritical, "Error Message"
      Resume Normal_exit
  End Function

  ______________________________________________
  Kath Pelletti
  Software Design & Solutions Pty Ltd.
  Ph: 9505-6714
  Fax: 9505-6430
  KP at SDSOnline.net 

  -- 
  AccessD mailing list
  AccessD at databaseadvisors.com
  http://databaseadvisors.com/mailman/listinfo/accessd
  Website: http://www.databaseadvisors.com