[AccessD] Query problem in VBA

[Gustav Brock]

Hi Chester 

In VBA, the SQL doesn't know about the form.

The easy workaround is a small helper function:

Public Function GetDateListMonth()
    GetDateListMonth = [Forms]![Form1]![PIH_Date_List].Value
End Function

And then use this:

SELECT 
    PIH_Monthly_Allocated_Volumes.ProductionMonth, 
    Sum(PIH_Monthly_Allocated_Volumes.Oil) AS SumOfOil 
INTO 
    [tbl PIH Monthly Oil] 
FROM 
    PIH_Monthly_Allocated_Volumes 
GROUP BY 
    PIH_Monthly_Allocated_Volumes.ProductionMonth
HAVING 
    PIH_Monthly_Allocated_Volumes.ProductionMonth = GetDateListMonth()

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD [mailto:accessd-bounces at databaseadvisors.com] På vegne af Kaup, Chester
Sendt: 8. januar 2018 15:31
Til: 'Access Developers discussion and problem solving' <accessd at databaseadvisors.com>
Emne: [AccessD] Query problem in VBA

The following query runs fine when executed from the design window or query list. When I try to run it from vba it complains about a missing parameter. When It runs in code is it not picking up the list box value? Thanks for any assistance.

SELECT PIH_Monthly_Allocated_Volumes.ProductionMonth, Sum(PIH_Monthly_Allocated_Volumes.Oil) AS SumOfOil INTO [tbl PIH Monthly Oil] FROM PIH_Monthly_Allocated_Volumes GROUP BY PIH_Monthly_Allocated_Volumes.ProductionMonth
HAVING (((PIH_Monthly_Allocated_Volumes.ProductionMonth)=[Forms]![Form1]![PIH_Date_List]));