Expression on Click (1 Viewer)

[jasn_78]

Hey everyone I am having some problems on some pc's with a form and a query, it all works fine from my pc but not on ones where I install just the access runtime

Attached is an error message I am getting when i install this database on any remote computers below is the code which is causing the problem any ideas would be great

Option Compare Database

Private Sub cmdEXIT_Click()
DoCmd.Close
End Sub

Private Sub cmdREPORT_Click()

    Dim stdocname As String
    Dim subject As String
    Dim db As DAO.Database
    Dim qds As DAO.QueryDef
    Dim strselect As String
    Dim strfrom As String
    Dim strwhere As String
    Dim strgroup As String
    Dim strorder As String
    Dim strsql As String
    Dim querycount As Integer
    Dim storeval As Integer
    Dim groupby As Integer
    
    Set db = CurrentDb
        
    For Each qds In db.QueryDefs
    If qds.Name = "qryHOURTILLS" Then
    querycount = 1
    End If
    Next

    If querycount = 1 Then
    db.QueryDefs.Delete "qryHOURTILLS"
    Set qds = db.CreateQueryDef("qryHOURTILLS")
    Else
    Set qds = db.CreateQueryDef("qryHOURTILLS")
    End If
    
    groupby = [Forms]![frmHOURTOTALS]![subfrmGROUPBY]![frameGROUPBY].VALUE
        
    strselect = "SELECT HOURTBL.HOUR_DATE AS [SALES_DATE], " & _
                " SYSCTBL.SYSC_COMPANY AS STORE, " & _
                " Format(TimeSerial(IIf([Hour_Hour]=24,0,[Hour_Hour]),0,0),'HH:MM') AS [HOUR OF DAY], " & _
                " Sum(HOURTBL.HOUR_VALUE) AS AMOUNT, Sum(HOURTBL.HOUR_CUSTOMERS) AS SALES, " & _
                "Sum(HOURTBL.HOUR_QTY) AS ITEMS "
    strfrom = "FROM SYSCTBL, HOURTBL "
    strwhere = "WHERE SYSCTBL.SYSC_NUMBER = HOURTBL.HOUR_SYSC_NUMBER " & _
                "AND HOURTBL.HOUR_DATE Between " & _
                "[Forms]![frmHOURTOTALS]![txtFROM] And [Forms]![frmHOURTOTALS]![txtTO] "
    strgroup = "GROUP BY HOURTBL.HOUR_DATE, SYSCTBL.SYSC_COMPANY, " & _
                "Format(TimeSerial(IIf([Hour_Hour]=24,0,[Hour_Hour]),0,0),'HH:MM'), " & _
                "HOURTBL.HOUR_SYSC_NUMBER "
    strorder = "ORDER BY HOURTBL.HOUR_DATE, HOURTBL.HOUR_SYSC_NUMBER, " & _
                "Format(TimeSerial(IIf([Hour_Hour]=24,0,[Hour_Hour]),0,0),'HH:MM')"
                
    Select Case groupby
    Case Is = 1
    'strselect = strselect
    'strfrom = strfrom
    'strwhere = strwhere
    'strgroup = strgroup
    'strorder = strorder
    Case Is = 2
        strselect = strselect & ", AREATBL.AREA_DESC AS AREA "
        strfrom = strfrom & ", AREATBL "
        strwhere = strwhere & " AND SYSCTBL.SYSC_NUMBER = AREATBL.AREA_SYSC_NUMBER " & _
                                "AND AREATBL.AREA_NUMBER = HOURTBL.HOUR_AREA_NUMBER "
        strgroup = strgroup & ", HOURTBL.HOUR_AREA_NUMBER, AREATBL.AREA_DESC "
        strorder = "ORDER BY HOURTBL.HOUR_DATE, HOURTBL.HOUR_SYSC_NUMBER, HOURTBL.HOUR_AREA_NUMBER, " & _
                    "Format(TimeSerial(IIf([Hour_Hour]=24,0,[Hour_Hour]),0,0),'HH:MM')"
    Case Is = 3
        strselect = strselect & ", AREATBL.AREA_DESC AS AREA, TILLTBL.TILL_DESC AS TILL "
        strfrom = strfrom & ", AREATBL, TILLTBL "
        strwhere = strwhere & " AND SYSCTBL.SYSC_NUMBER = AREATBL.AREA_SYSC_NUMBER " & _
                              "AND AREATBL.AREA_NUMBER = HOURTBL.HOUR_AREA_NUMBER " & _
                              "AND TILLTBL.TILL_NUMBER = HOURTBL.HOUR_TILL_NUMBER " & _
                              "AND AREATBL.AREA_NUMBER = TILLTBL.TILL_AREA_NUMBER "
        strgroup = strgroup & ", HOURTBL.HOUR_AREA_NUMBER, AREATBL.AREA_DESC, " & _
                                "HOURTBL.HOUR_TILL_NUMBER, TILLTBL.TILL_DESC "
        strorder = "ORDER BY HOURTBL.HOUR_DATE, HOURTBL.HOUR_SYSC_NUMBER, HOURTBL.HOUR_AREA_NUMBER, " & _
                    "HOURTBL.HOUR_TILL_NUMBER, Format(TimeSerial(IIf([Hour_Hour]=24,0,[Hour_Hour]),0,0),'HH:MM')"
    End Select
    
    strsql = strselect & strfrom & strwhere & strgroup & strorder
     
    storeval = 0
    querycount = 0
    

    
    qds.sql = strsql
    
    DoCmd.TransferSpreadsheet TransferType:=acExport, _
    SpreadsheetType:=acSpreadsheetTypeExcel8, _
    TableName:="qryHOURTILLS", _
    FileName:=CurrentProject.Path & "\TillHour.xls", _
    Hasfieldnames:=True
              
End Sub

Private Sub Form_Load()

Me.txtFROM = Format((Now() - 1), "dd/mm/yyyy")
Me.txtTO = Format((Now() - 1), "dd/mm/yyyy")

End Sub

 

[jal]

I removed this post because I now realize that the sandbox value should be set to 2, not 3 as I had previously thought.

 

[HiTechCoach]

A common cause for the built-in functions, like Format(), to stop working is that there is a missing reference(s) to a library.

I would recommend that you check for a missing reference.

This may help: attached is a Simple splash screen and reference checker

 

[jasn_78]

thanks hitech coach it is that cant figure which reference atm but removing the format command from the query has fixed it