Results 1 to 11 of 11
  1. #1
    MisterPepe is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    7

    Can't execute make table query from a Module

    Greetings to all:

    I am trying to execute a Make Table Query from a Module. The Make Table Query works manually, but does not work when i call it via VBA.

    This is the code:


    "
    Public Sub actualizarDisponible()

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("Disponible")

    'borrar tabla disponible actual (solo si existe)
    If ifTableExists("DisponibleTABLE") Then
    dbs.TableDefs.Delete "DisponibleTABLE"
    End If

    'Set the value of the QueryDef's parameter
    qdf.Parameters("fecha_calc").Value = Format(Now(), "yyyy-mm-dd")

    'Execute the query
    qdf.Execute dbFailOnError

    'Clean up
    qdf.Close
    Set qdf = Nothing
    Set dbs = Nothing

    End Sub

    Public Function ifTableExists(tblName As String) As Boolean
    'función comprueba si una tabla access existe

    If DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1 Then

    ifTableExists = True

    End If

    End Function
    "
    The Make Table Query is called Disponible and it doesn't work this way. I've searched a lot on the net but nothing seems to work.

    I would appreciate it very much if someone could give me an advice on how to make this code work.

    Regards



    MisterPepe

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Welcome to the forum

    If I'm reading the code correctly, its saying:
    If the table exists , delete it
    AFAIK it doesn't tell you to create it

    Code:
    Public Sub actualizarDisponible()
    
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("Disponible")
    
    'borrar tabla disponible actual (solo si existe)
    'delete table if it exists
    If ifTableExists("DisponibleTABLE") Then
    dbs.TableDefs.Delete "DisponibleTABLE"
    End If
    
    'Set the value of the QueryDef's parameter
    qdf.Parameters("fecha_calc").Value = Format(Now(), "yyyy-mm-dd")
    
    'Execute the query
    'what is the query?
    qdf.Execute dbFailOnError
    
    'Clean up
    qdf.Close
    Set qdf = Nothing
    Set dbs = Nothing
    
    End Sub
    
    Public Function ifTableExists(tblName As String) As Boolean
    'función comprueba si una tabla access existe
    
    'suggest you change as shown below in case you have more than one object with the same name
    'not a good idea but possible
    'If DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1 Then
    If DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") > 0 Then
         ifTableExists = True
    End If
    
    End Function
    Please explain why you are deleting the table & where the make table query is.
    NOTE: If you use a make table query, it will overwrite any existing table of that name
    Last edited by isladogs; 04-11-2018 at 01:22 PM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    MisterPepe is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    7
    Thanks for the response ridders52

    That is exactly what the code does.

    This is the query disponible:

    SELECT [fecha_calc] AS Fecha, ProductoBodega.Producto, ProductoBodega.CODBARRA, ProductoBodega.Familia AS M_Estilo, ProductoBodega.f_Talla AS M_Talla, ProductoBodega.f_Color AS M_Color, IIf([ProductoBodega].[COSTO]<=0 Or IsNull([ProductoBodega].[COSTO]),[ProductoBodega].[COSTOESTANDAR],[ProductoBodega].[COSTO]) AS COSTO, ProductoBodega.M_PRECIOVENT, IIf([ProductoBodega].[Bodega]="" Or IsNull([ProductoBodega].[Bodega]),"CD",[ProductoBodega].[Bodega]) AS Bodega, TOTTRASPASOS_EGRESOS.Cantidad AS Transpasos_Egresos, TOTTRASPASOS_INGRESOS.Cantidad AS Traspasos_Ingresos, TOTAJUSTES.Cantidad AS Ajustes, TOTMERMA.Cantidad AS Merma, Nz([TOTTRASPASOS_EGRESOS].[Cantidad],0)-Nz([TOTTRASPASOS_INGRESOS].[Cantidad],0)-Nz([TOTAJUSTES].[Cantidad],0)+Nz([TOTMERMA].[Cantidad],0) AS AJUSTES_TRASPASOS_MERMAS, Nz([RECEP].[RECEP_Cantidad],0)+Nz([RECEP_VME].[RECEP_Cantidad],0) AS Recepciones, Nz([FORECAST_IMP].[ForecastPorRecepcionar],0)+Nz([FORECAST_IMP_VME].[ForecastPorRecepcionar],0) AS EnTransito, [Recepciones]+[EnTransito] AS StockInicial, Facturas.FACT_Cantidad AS Venta, Nz([Facturas].[FACT_Cantidad],0)-Nz([NC].[NC_Cantidad],0) AS VentaNeta, Nz([FORECAST].[FORECASTporFacturar],0)+Nz([NV].[NVporFacturar],0) AS Reserva, Nz([RECEP].[RECEP_Cantidad],0)+Nz([RECEP_VME].[RECEP_Cantidad],0)+Nz([TOTTRASPASOS_INGRESOS].[Cantidad],0)+Nz([TOTAJUSTES].[Cantidad],0)-Nz([TOTTRASPASOS_EGRESOS].[Cantidad],0)-Nz([TOTMERMA].[Cantidad],0)-Nz([Facturas].[FACT_Cantidad],0)+Nz([NC].[NC_Cantidad],0) AS Stock, Nz([RECEP].[RECEP_Cantidad],0)+Nz([RECEP_VME].[RECEP_Cantidad],0)+Nz([FORECAST_IMP].[ForecastPorRecepcionar],0)+Nz([FORECAST_IMP_VME].[ForecastPorRecepcionar],0)+Nz([TOTTRASPASOS_INGRESOS].[Cantidad],0)+Nz([TOTAJUSTES].[Cantidad],0)-Nz([TOTTRASPASOS_EGRESOS].[Cantidad],0)-Nz([TOTMERMA].[Cantidad],0)-Nz([Facturas].[FACT_Cantidad],0)+Nz([NC].[NC_Cantidad],0) AS StockConTransito, Nz([RECEP].[RECEP_Cantidad],0)+Nz([RECEP_VME].[RECEP_Cantidad],0)+Nz([TOTTRASPASOS_INGRESOS].[Cantidad],0)+Nz([TOTAJUSTES].[Cantidad],0)-Nz([TOTTRASPASOS_EGRESOS].[Cantidad],0)-Nz([TOTMERMA].[Cantidad],0)-Nz([Facturas].[FACT_Cantidad],0)+Nz([NC].[NC_Cantidad],0)-Nz([FORECAST].[FORECASTporFacturar],0)-Nz([NV].[NVporFacturar],0) AS Disponible, Nz([RECEP].[RECEP_Cantidad],0)+Nz([RECEP_VME].[RECEP_Cantidad],0)+Nz([FORECAST_IMP].[ForecastPorRecepcionar],0)+Nz([FORECAST_IMP_VME].[ForecastPorRecepcionar],0)+Nz([TOTTRASPASOS_INGRESOS].[Cantidad],0)+Nz([TOTAJUSTES].[Cantidad],0)-Nz([TOTTRASPASOS_EGRESOS].[Cantidad],0)-Nz([TOTMERMA].[Cantidad],0)-Nz([Facturas].[FACT_Cantidad],0)+Nz([NC].[NC_Cantidad],0)-Nz([FORECAST].[FORECASTporFacturar],0)-Nz([NV].[NVporFacturar],0) AS DisponibleConTransito, Int([Stock]*[Costo]) AS [$_Stock], Int([StockConTransito]*[Costo]) AS [$_StockConTransito], Int([Disponible]*[Costo]) AS [$_Disponible], Int([DisponibleConTransito]*[Costo]) AS [$_DisponibleConTransito], Maestro.M_Temp, Maestro.M_Licencia, Maestro.M_DTR, Maestro.M_Linea, Maestro.M_Departamento, Maestro.M_Edad, Maestro.M_Genero, Maestro.M_Coleccion, Maestro.[M_Cliente/Canal], Maestro.M_Canal, Maestro.[M_NAC/FOB], Maestro.M_Marca, Maestro.M_Negocio, Maestro.M_Contrato, Maestro.M_Temporada, Maestro.M_Tallas, Maestro.M_Colores, Maestro.M_Curva, Maestro.M_Diseñadora, Maestro.M_Observaciones, Maestro.M_Composicion, Maestro.M_Tela, Maestro.M_PVPRetail, Maestro.[M_Fecha Inicial de Embarque], Maestro.[M_Fecha Arribo a Chile], Maestro.[M_Tipo de Venta], Maestro.M_ContratoVigente, Maestro.M_URL INTO DisponibleTable
    FROM ((((((((((((ProductoBodega LEFT JOIN TOTAJUSTES ON (ProductoBodega.Bodega = TOTAJUSTES.Bodega) AND (ProductoBodega.Producto = TOTAJUSTES.Producto)) LEFT JOIN TOTMERMA ON (ProductoBodega.Bodega = TOTMERMA.Bodega) AND (ProductoBodega.Producto = TOTMERMA.Producto)) LEFT JOIN TOTTRASPASOS_EGRESOS ON (ProductoBodega.Bodega = TOTTRASPASOS_EGRESOS.Bodega) AND (ProductoBodega.Producto = TOTTRASPASOS_EGRESOS.Producto)) LEFT JOIN TOTTRASPASOS_INGRESOS ON (ProductoBodega.Bodega = TOTTRASPASOS_INGRESOS.Bodega) AND (ProductoBodega.Producto = TOTTRASPASOS_INGRESOS.Producto)) LEFT JOIN FORECAST_IMP_VME ON (ProductoBodega.Bodega = FORECAST_IMP_VME.Bodega) AND (ProductoBodega.Producto = FORECAST_IMP_VME.Producto)) LEFT JOIN RECEP_VME ON (ProductoBodega.Bodega = RECEP_VME.Bodega) AND (ProductoBodega.Producto = RECEP_VME.Producto)) LEFT JOIN RECEP ON (ProductoBodega.Bodega = RECEP.Bodega) AND (ProductoBodega.Producto = RECEP.Producto)) LEFT JOIN FORECAST_IMP ON (ProductoBodega.Bodega = FORECAST_IMP.Bodega) AND (ProductoBodega.Producto = FORECAST_IMP.Producto)) LEFT JOIN FORECAST ON (ProductoBodega.Bodega = FORECAST.Bodega) AND (ProductoBodega.Producto = FORECAST.Producto)) LEFT JOIN NV ON (ProductoBodega.Bodega = NV.Bodega) AND (ProductoBodega.Producto = NV.Producto)) LEFT JOIN Facturas ON (ProductoBodega.Bodega = Facturas.Bodega) AND (ProductoBodega.Producto = Facturas.Producto)) LEFT JOIN NC ON (ProductoBodega.Bodega = NC.Bodega) AND (ProductoBodega.Producto = NC.Producto)) LEFT JOIN Maestro ON ProductoBodega.FAMILIA = Maestro.ID;

    As you can see it is a big one.

    The thing is that when i execute this query manually it works fine, but when i try to execute via VBA, it shows dbfailonerror 128

    Is there a way in wich i can gather more info about the error that is going on?

    Thanks so much for your time

    Regards

    MisterPepe

    PS: I inherited this code from other guy. I think it deletes first so there are no questions asked by the program. So it gets deleted because of that. This part of the code does not make an error ocur. The only part that has an error is this line:

    qdf.Execute dbFailOnError

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    OK - I'm not going to pretend I've tried to follow the details of that make table query.
    Its certainly complex with many Nz clauses, outer joins and a parameter.
    You say it works as a query but not when part of your procedure

    In a well designed database, make table queries should almost never be used.
    To repeatedly delete & make the table again is VERY bad practice.
    It will cause your database to bloat significantly & can lead to instability & database crashes

    Better alternatives include (in order of preference):
    1. If the data exists in other tables, use queries to get that data - don't copy it into a new table
    2. Append new records to the table but keep the existing records
    3. Empty the existing table if necessary then use an append query to repopulate with new data

    So overall I would scrap the current approach and use one of the above

    In the meantime, add error handling to the procedure to try & identify the issue
    The line qdf.Execute dbFailOnError does not cause the error - it just states an error occurred
    Full error handling will provide more info

    Code:
    Public Sub actualizarDisponible()
    
    On Error GoTo Err_Handler
    
    ... all your code goes here
    
    Exit_Handler:
        Exit Sub
        
    Err_Handler:
        MsgBox "Error " & Err.number & " in actualizarDisponible procedure : " & Err.description, vbOKOnly + vbCritical
        Resume Exit_Handler
    
    End Sub
    Run the code & check the error message.
    Then I recommend you think about a redesign
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If I'm not mistaken, the error code is about something that cannot be resolved. Will look more but the first problem I see is IsNull.
    That is a vba function; pretty sure it cannot be used in sql. Use Is Null.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I agree with not repeatedly deleting tables and queries.
    I would also avoid special characters/spaces in any names (save for perhaps the underscore _). You seem to be doing so here: [$_Stock]; including square brackets in an alias name also doesn't look good; it presents itself as a field name, which may be contributing to the error. You are also using ~ and \, not that I think these are a factor in the issue.

    Usually the reason db.execute will fail while the sql will work if you plunk it into a query is that .execute bypasses Access and uses Jet directly, thus something relevant to the Access portion isn't being resolved by .execute (Jet does not recognize the Access thing, whatever that is). However, I'm surprised IsNull will work in a query, unless Access is just converting it to Is Null without your knowledge. You can test this by dumping the sql into a new query and switch to design view to check.

  7. #7
    MisterPepe is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    7
    Thanks for the reply ridders52 you are really helping me on this one =)

    Redesigning is out of the question for the moment because it would take a lot of time and we need to keep things moving just for now.

    The error message is this:

    "Error 3035 in actualizarDisponible procedure: System Resource Exceeded"

    Is there an actual difference between calling the Make Table Query manually and via VBA? Can i make something so the query does execute?

    Thanks for the advice

    Regards

    MisterPepe

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Redesigning is out of the question for the moment because it would take a lot of time and we need to keep things moving just for now.
    I was expecting that response but in the end that has to be the solution needed

    On the immediate issues, if you haven't already done so, please note the responses from Micron.

    On the rare occasions I've experienced a similar 'System Resource Exceeded' issue, it was where I was updating huge datasets e.g. table of 2.5 million UK postcodes
    You need to optimise the SQL code used in your query

    There are also 2 registry changes you can make which may help
    a) Increase MaxLocksPerFile - see this thread for info: https://www.access-programmers.co.uk...axLocksPerFile
    b) Modify Access DiskBuffer size - see https://www.access-programmers.co.uk...axLocksPerFile

    If you do try either of these, make changes in small steps or you may imbalance system resources in a different way & cause other issues

    Good luck
    Last edited by isladogs; 04-11-2018 at 09:06 PM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    MisterPepe is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    7
    Thanks for the reply guys.

    I tried Increasing MaxLocksPerFile and Modify Access DiskBuffer size, and tried changing to Is Null the following way:

    SELECT [fecha_calc] AS Fecha, ProductoBodega.Producto, ProductoBodega.CODBARRA, ProductoBodega.Familia AS M_Estilo, ProductoBodega.f_Talla AS M_Talla, ProductoBodega.f_Color AS M_Color, IIf(ProductoBodega.COSTO<=0 Or IIf(ProductoBodega.COSTO Is Null,1,0),ProductoBodega.COSTOESTANDAR,ProductoBodega.COSTO ) AS COSTO, ProductoBodega.M_PRECIOVENT, IIf(ProductoBodega.Bodega="" Or IIf(ProductoBodega.Bodega Is Null,1,0),"CD",ProductoBodega.Bodega) AS Bodega, TOTTRASPASOS_EGRESOS.Cantidad AS Transpasos_Egresos, TOTTRASPASOS_INGRESOS.Cantidad AS Traspasos_Ingresos, TOTAJUSTES.Cantidad AS Ajustes, TOTMERMA.Cantidad AS Merma, Nz([TOTTRASPASOS_EGRESOS].[Cantidad],0)-Nz([TOTTRASPASOS_INGRESOS].[Cantidad],0)-Nz([TOTAJUSTES].[Cantidad],0)+Nz([TOTMERMA].[Cantidad],0) AS AJUSTES_TRASPASOS_MERMAS, Nz([RECEP].[RECEP_Cantidad],0)+Nz([RECEP_VME].[RECEP_Cantidad],0) AS Recepciones, Nz([FORECAST_IMP].[ForecastPorRecepcionar],0)+Nz([FORECAST_IMP_VME].[ForecastPorRecepcionar],0) AS EnTransito, [Recepciones]+[EnTransito] AS StockInicial, Facturas.FACT_Cantidad AS Venta, Nz([Facturas].[FACT_Cantidad],0)-Nz([NC].[NC_Cantidad],0) AS VentaNeta, Nz([FORECAST].[FORECASTporFacturar],0)+Nz([NV].[NVporFacturar],0) AS Reserva, Nz([RECEP].[RECEP_Cantidad],0)+Nz([RECEP_VME].[RECEP_Cantidad],0)+Nz([TOTTRASPASOS_INGRESOS].[Cantidad],0)+Nz([TOTAJUSTES].[Cantidad],0)-Nz([TOTTRASPASOS_EGRESOS].[Cantidad],0)-Nz([TOTMERMA].[Cantidad],0)-Nz([Facturas].[FACT_Cantidad],0)+Nz([NC].[NC_Cantidad],0) AS Stock, Nz([RECEP].[RECEP_Cantidad],0)+Nz([RECEP_VME].[RECEP_Cantidad],0)+Nz([FORECAST_IMP].[ForecastPorRecepcionar],0)+Nz([FORECAST_IMP_VME].[ForecastPorRecepcionar],0)+Nz([TOTTRASPASOS_INGRESOS].[Cantidad],0)+Nz([TOTAJUSTES].[Cantidad],0)-Nz([TOTTRASPASOS_EGRESOS].[Cantidad],0)-Nz([TOTMERMA].[Cantidad],0)-Nz([Facturas].[FACT_Cantidad],0)+Nz([NC].[NC_Cantidad],0) AS StockConTransito, Nz([RECEP].[RECEP_Cantidad],0)+Nz([RECEP_VME].[RECEP_Cantidad],0)+Nz([TOTTRASPASOS_INGRESOS].[Cantidad],0)+Nz([TOTAJUSTES].[Cantidad],0)-Nz([TOTTRASPASOS_EGRESOS].[Cantidad],0)-Nz([TOTMERMA].[Cantidad],0)-Nz([Facturas].[FACT_Cantidad],0)+Nz([NC].[NC_Cantidad],0)-Nz([FORECAST].[FORECASTporFacturar],0)-Nz([NV].[NVporFacturar],0) AS Disponible, Nz([RECEP].[RECEP_Cantidad],0)+Nz([RECEP_VME].[RECEP_Cantidad],0)+Nz([FORECAST_IMP].[ForecastPorRecepcionar],0)+Nz([FORECAST_IMP_VME].[ForecastPorRecepcionar],0)+Nz([TOTTRASPASOS_INGRESOS].[Cantidad],0)+Nz([TOTAJUSTES].[Cantidad],0)-Nz([TOTTRASPASOS_EGRESOS].[Cantidad],0)-Nz([TOTMERMA].[Cantidad],0)-Nz([Facturas].[FACT_Cantidad],0)+Nz([NC].[NC_Cantidad],0)-Nz([FORECAST].[FORECASTporFacturar],0)-Nz([NV].[NVporFacturar],0) AS DisponibleConTransito, Int([Stock]*[Costo]) AS [$_Stock], Int([StockConTransito]*[Costo]) AS [$_StockConTransito], Int([Disponible]*[Costo]) AS [$_Disponible], Int([DisponibleConTransito]*[Costo]) AS [$_DisponibleConTransito], Maestro.M_Temp, Maestro.M_Licencia, Maestro.M_DTR, Maestro.M_Linea, Maestro.M_Departamento, Maestro.M_Edad, Maestro.M_Genero, Maestro.M_Coleccion, Maestro.[M_Cliente/Canal], Maestro.M_Canal, Maestro.[M_NAC/FOB], Maestro.M_Marca, Maestro.M_Negocio, Maestro.M_Contrato, Maestro.M_Temporada, Maestro.M_Tallas, Maestro.M_Colores, Maestro.M_Curva, Maestro.M_Diseñadora, Maestro.M_Observaciones, Maestro.M_Composicion, Maestro.M_Tela, Maestro.M_PVPRetail, Maestro.[M_Fecha Inicial de Embarque], Maestro.[M_Fecha Arribo a Chile], Maestro.[M_Tipo de Venta], Maestro.M_ContratoVigente, Maestro.M_URL INTO DisponibleTable
    FROM ((((((((((((ProductoBodega LEFT JOIN TOTAJUSTES ON (ProductoBodega.Bodega = TOTAJUSTES.Bodega) AND (ProductoBodega.Producto = TOTAJUSTES.Producto)) LEFT JOIN TOTMERMA ON (ProductoBodega.Bodega = TOTMERMA.Bodega) AND (ProductoBodega.Producto = TOTMERMA.Producto)) LEFT JOIN TOTTRASPASOS_EGRESOS ON (ProductoBodega.Bodega = TOTTRASPASOS_EGRESOS.Bodega) AND (ProductoBodega.Producto = TOTTRASPASOS_EGRESOS.Producto)) LEFT JOIN TOTTRASPASOS_INGRESOS ON (ProductoBodega.Bodega = TOTTRASPASOS_INGRESOS.Bodega) AND (ProductoBodega.Producto = TOTTRASPASOS_INGRESOS.Producto)) LEFT JOIN FORECAST_IMP_VME ON (ProductoBodega.Bodega = FORECAST_IMP_VME.Bodega) AND (ProductoBodega.Producto = FORECAST_IMP_VME.Producto)) LEFT JOIN RECEP_VME ON (ProductoBodega.Bodega = RECEP_VME.Bodega) AND (ProductoBodega.Producto = RECEP_VME.Producto)) LEFT JOIN RECEP ON (ProductoBodega.Bodega = RECEP.Bodega) AND (ProductoBodega.Producto = RECEP.Producto)) LEFT JOIN FORECAST_IMP ON (ProductoBodega.Bodega = FORECAST_IMP.Bodega) AND (ProductoBodega.Producto = FORECAST_IMP.Producto)) LEFT JOIN FORECAST ON (ProductoBodega.Bodega = FORECAST.Bodega) AND (ProductoBodega.Producto = FORECAST.Producto)) LEFT JOIN NV ON (ProductoBodega.Bodega = NV.Bodega) AND (ProductoBodega.Producto = NV.Producto)) LEFT JOIN Facturas ON (ProductoBodega.Bodega = Facturas.Bodega) AND (ProductoBodega.Producto = Facturas.Producto)) LEFT JOIN NC ON (ProductoBodega.Bodega = NC.Bodega) AND (ProductoBodega.Producto = NC.Producto)) LEFT JOIN Maestro ON ProductoBodega.FAMILIA = Maestro.ID;


    With all the changes above, the problem is still the same.

    So with all that done, the only solution would be change de query?

    Thanks in advance for your help

    Regards

    Misterpepe

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You are well below the sql character limit, so what about the tables being queried? I haven't seen anything that indicates that there size may be a contributing factor.
    You might have to post a zipped copy of the db for us to get any further - assuming the data set isn't too large. However, not sure if there would be a language barrier for some of us. First though, I would start over with a new query and add things in stages (1 or 2 tables first) and build from there. If you test in small increments, you have a better chance of pinpointing the problem. While you're at that, consider the following...

    looking again, neither of these seem right (and could be causing the problem)
    IIf([ProductoBodega].[COSTO]<=0 Or IsNull([ProductoBodega].[COSTO])
    IIf(ProductoBodega.Bodega="" Or IIf(ProductoBodega.Bodega Is Null,1,0)

    Either of the portions on the left or right side of OR ought to be evaluating to True or False, but what seems to be missing is the second half; i.e. what to return when the boolean logic is evaluated. So IF something is >=0 OR something is Null, then what?? Or is this "CD" for example, what is to be returned if either of the tests are True, in which case it is lying outside of the parentheses () and should not be, thus might be interpreted as a field?
    Last edited by Micron; 04-12-2018 at 03:48 PM. Reason: spelin and gramur
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    MisterPepe is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    7
    Quote Originally Posted by Micron View Post
    looking again, neither of these seem right (and could be causing the problem)
    IIf([ProductoBodega].[COSTO]<=0 Or IsNull([ProductoBodega].[COSTO])
    IIf(ProductoBodega.Bodega="" Or IIf(ProductoBodega.Bodega Is Null,1,0)

    Either of the portions on the left or right side of OR ought to be evaluating to True or False, but what seems to be missing is the second half; i.e. what to return when the boolean logic is evaluated. So IF something is >=0 OR something is Null, then what?? Or is this "CD" for example, what is to be returned if either of the tests are True, in which case it is lying outside of the parentheses () and should not be, thus might be interpreted as a field?
    Hey there Micron:

    Thanks for the replies so much! =) Well i chopped every IIF function on the query and it still doesn't run, so it isn't that T_T

    I'll try to do it as you say, by small increments.

    I'll post whenev i find something worthy sharing.

    Thanks so much!

    Regards
    MisterPepe

Please reply to this thread with any new information or opinions.

Similar Threads

  1. RunSQL vs. QueryDef.Execute vs. CurrentDB.Execute
    By caubetiep1337 in forum Queries
    Replies: 8
    Last Post: 12-16-2015, 05:35 PM
  2. Replies: 5
    Last Post: 08-10-2015, 02:07 PM
  3. Execute a module?
    By philip.mccollum in forum Access
    Replies: 1
    Last Post: 07-08-2015, 05:57 AM
  4. Replies: 10
    Last Post: 02-27-2015, 11:28 AM
  5. Replies: 2
    Last Post: 02-17-2015, 01:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums