VBA String IIf - Query result returns blank

[Scaniafan]

Ok guys, I'm probably making a rookie mistake here with VBA, but I can't get grip on where I'm making it...:banghead:

I've got a DoCmd.runSQL in my VBA, which contains two IIf statements. To make the VBA readable for everyone who might need to work on it, I've pulled the IIf out from the DoCmd.runSQL and created a string as follows:

Dim strPODTarget As String

strPODTarget = IIf([QRY_Gross_Performance_3]![POD Target Time] = "", [QRY_Gross_Performance_3]![POD Gross], "Check - End Loc. Country not in TBL_Source_POD_Margin")

To check the outcome before I continue, I've built a simple MsgBox to display the strPODTarget.

For some reason, I don't get any value in the MsgBox, although in a simple query this IIf statement works like a charm.

I think I'm making a mistake in referring to the QRY_Gross_Performance_3, but I am staring blind at it.

Thanks for any help.

 

[Minty]

I'm confused - the code listed only has one IIf.
It would probably be better if you just posted your actual code, and the SQL view of the source query.

 

[arnelgp]

 I've pulled the IIf out from the DoCmd.runSQL and created a string as follows:

you need to use Dlookup:

Dim strPodGross As String
strPodGross = DLookup("[POD Gross]","[QRY_Gross_Performance_3]") & ""
strPODTarget = IIf(DLookup("[POD Target Time]","[QRY_Gross_Performance_3]") & "" = "", strPodGross, "Check - End Loc. Country not in TBL_Source_POD_Margin")

 

[Scaniafan]

I'm confused - the code listed only has one IIf.
It would probably be better if you just posted your actual code, and the SQL view of the source query.

I thought it was going to be an easy fix, as I thought it was in the link to the QRY_Gross_Performance_3. However, with the answer from Arnelgp, I've came up with below, which seems to fill the IIf statements, however, I now get the error

Below is the VBA code:

    Private Sub Gross_Performance_Carrier_Click()
          
         'make sure a from date has been entered
        If IsNull(From_Date) Then
          MsgBox "'From' date cannot be blank.", vbInformation, "Date required"
          Exit Sub
        End If
        
        'make sure a till date has been entered
        If IsNull(Till_Date) Then
          MsgBox "'Till' date cannot be blank.", vbInformation, "Date required"
          Exit Sub
        End If
        
        'make sure a selection has been made
        If Me.Forwarder_List.ItemsSelected.Count = 0 Then
          MsgBox "At least one forwarding agent must be selected.", vbInformation, "Forwarding agent required"
          Exit Sub
        End If
        
        DoCmd.SetWarnings False
        
        'Create string from selected carriers
        Dim varItem As Variant
        Dim strWhere As String
            
        For Each varItem In Me![Forwarder_List].ItemsSelected
        strWhere = strWhere & "(QRY_Gross_Performance_3.[Forwarding agent])=" & Chr(39) & Me![Forwarder_List].Column(0, varItem) & Chr(39) & " Or "
        Next varItem
        
        strWhere = Left(strWhere, Len(strWhere) - 4) 'Remove the last " Or "
        
        'Determines IIf results for DoCmd.RunSQL
        Dim strPODGross As String
        Dim strPODTarget As String
        Dim strDestCountry As String
                 
        strPODGross = DLookup("[POD Gross]", "[QRY_Gross_Performance_3]") & ""
        strPODTarget = IIf(DLookup("[POD Target Time]", "[QRY_Gross_Performance_3]") & "" = "", strPODGross, "Check - End Loc. Country not in TBL_Source_POD_Margin")
        strDestCountry = IIf(DLookup("[Endloc Country]", "[QRY_Gross_Performance_3]") & "" = DLookup("[Destination country code]", "[TBL_Source_POD_Margin]"), strPODGross, strPODTarget)
             
        'Displays MsgBox for Strings if ' is removed
        MsgBox strWhere, vbInformation, "Selected carriers"
        MsgBox strPODTarget, vbInformation, "POD Target result"
        MsgBox strDestCountry, vbInformation, "Destination Country result"
                
        DoCmd.RunSQL "SELECT QRY_Gross_Performance_3.[Shipment Number], QRY_Gross_Performance_3.[Mode], QRY_Gross_Performance_3.[Forwarding agent], QRY_Gross_Performance_3.[Shipping Pnt], QRY_Gross_Performance_3.[Startloc City], QRY_Gross_Performance_3.[Startloc Country], QRY_Gross_Performance_3.[Endloc Shipping Pnt],QRY_Gross_Performance_3.[Endloc City], QRY_Gross_Performance_3.[Endloc Country],QRY_Gross_Performance_3.[Departure Date Plann], QRY_Gross_Performance_3.[Departure Date], QRY_Gross_Performance_3.[Departure Gross], QRY_Gross_Performance_3.[Planned date for end], QRY_Gross_Performance_3.[Actual Date for END], QRY_Gross_Performance_3.[Actual Time for END], QRY_Gross_Performance_3.[Arrival Gross], QRY_Gross_Performance_3.[POD Target Date], QRY_Gross_Performance_3.[POD Target Time], QRY_Gross_Performance_3.[POD Date], QRY_Gross_Performance_3.[POD Time]," & _
        strDestCountry & " AS [POD Gross] INTO TBL_Gross_Performance_Carrier_Temp FROM TBL_Source_POD_Margin RIGHT JOIN QRY_Gross_Performance_3 ON TBL_Source_POD_Margin.[Destination country code] = QRY_Gross_Performance_3.[Endloc Country] WHERE (((QRY_Gross_Performance_3.[Departure Date]) Between [Forms]![KPI Database]![From_Date] And [Forms]![KPI Database]![Till_Date]) AND (" & strWhere & "));"
                                      
        DoCmd.OpenTable "TBL_Gross_Performance_Carrier_Temp"
        
        DoCmd.SetWarnings True
            
    End Sub

Which currently, after the help of Arnelgp, the VBA script now results in

Run-time error '3075':

Syntax error (missing operator) in query expression 'Check - End Loc. Country not in TBL_Source_POD_Margin'

What I'm trying to replicate in VBA is below query, but where the fixed Forwarding Agent is based on a multiselect listbox

SELECT QRY_Gross_Performance_3.[Shipment Number], QRY_Gross_Performance_3.Mode, QRY_Gross_Performance_3.[Forwarding agent], QRY_Gross_Performance_3.[Shipping Pnt], QRY_Gross_Performance_3.[Startloc City], QRY_Gross_Performance_3.[Startloc Country], QRY_Gross_Performance_3.[Endloc Shipping Pnt], QRY_Gross_Performance_3.[Endloc City], QRY_Gross_Performance_3.[Endloc Country], QRY_Gross_Performance_3.[Departure Date Plann], QRY_Gross_Performance_3.[Departure Date], QRY_Gross_Performance_3.[Departure Gross], QRY_Gross_Performance_3.[Planned date for end], QRY_Gross_Performance_3.[Actual Date for END], QRY_Gross_Performance_3.[Actual Time for END], QRY_Gross_Performance_3.[Arrival Gross], QRY_Gross_Performance_3.[POD Target Date], QRY_Gross_Performance_3.[POD Target Time], QRY_Gross_Performance_3.[POD Date], QRY_Gross_Performance_3.[POD Time], IIf([QRY_Gross_Performance_3]![Endloc Country]=[TBL_Source_POD_Margin]![Destination country code],[QRY_Gross_Performance_3]![POD Gross],IIf([QRY_Gross_Performance_3]![POD Target Time]="",[QRY_Gross_Performance_3]![POD Gross],"Check - End Loc. Country not in TBL_Source_POD_Margin")) AS [POD Gross]
FROM TBL_Source_POD_Margin RIGHT JOIN QRY_Gross_Performance_3 ON TBL_Source_POD_Margin.[Destination country code] = QRY_Gross_Performance_3.[Endloc Country]
WHERE (((QRY_Gross_Performance_3.[Forwarding agent])="Forwarding Agent") AND ((QRY_Gross_Performance_3.[Departure Date]) Between [Forms]![KPI Database]![From_Date] And [Forms]![KPI Database]![Till_Date]));

 

[Minty]

As a general point I would build your sql query string separately (call it something like strSql) Then debug.print it before you do your DoCmd.RunSQL strSql this will show you what your actual SQL string is.

I think your problem might be the form! references in the vba string - I don't think it can interpret them. You can test that by forcing a temporary hard coded value in there instead.

 

[Scaniafan]

When I change the last part to

WHERE (((QRY_Gross_Performance_3.[Departure Date]) Between #10/01/2015# And #10/31/2015#)  AND (" & strWhere & "));"

I still get the same error message

 

[Minty]

So what is the actual complete SQL string in the you are trying to execute?

 

[Scaniafan]

The entire SQL is as below:

Dim strSQL As String
        
        strSQL = "SELECT QRY_Gross_Performance_3.[Shipment Number], QRY_Gross_Performance_3.[Mode], QRY_Gross_Performance_3.[Forwarding agent], QRY_Gross_Performance_3.[Shipping Pnt], QRY_Gross_Performance_3.[Startloc City], QRY_Gross_Performance_3.[Startloc Country], QRY_Gross_Performance_3.[Endloc Shipping Pnt],QRY_Gross_Performance_3.[Endloc City], QRY_Gross_Performance_3.[Endloc Country],QRY_Gross_Performance_3.[Departure Date Plann], QRY_Gross_Performance_3.[Departure Date], QRY_Gross_Performance_3.[Departure Gross], QRY_Gross_Performance_3.[Planned date for end], QRY_Gross_Performance_3.[Actual Date for END], QRY_Gross_Performance_3.[Actual Time for END], QRY_Gross_Performance_3.[Arrival Gross], QRY_Gross_Performance_3.[POD Target Date], QRY_Gross_Performance_3.[POD Target Time], QRY_Gross_Performance_3.[POD Date], QRY_Gross_Performance_3.[POD Time]," & _
        strDestCountry & " AS [POD Gross] INTO TBL_Gross_Performance_Carrier_Temp FROM TBL_Source_POD_Margin RIGHT JOIN QRY_Gross_Performance_3 ON TBL_Source_POD_Margin.[Destination country code] = QRY_Gross_Performance_3.[Endloc Country] WHERE (((QRY_Gross_Performance_3.[Departure Date]) Between #10/01/2015# And #10/31/2015#) AND (" & strWhere & "));"
                                      
        Debug.Print
                                      
        DoCmd.RunSQL strSQL

Where the Debug highlights the DoCmd.RunSQL strSQL

The same error message and debug highlight are shown if I remove the

WHERE (((QRY_Gross_Performance_3.[Departure Date]) Between #10/01/2015# And #10/31/2015#) AND (" & strWhere & "))

from the SQL as variable, which basically drives the selection on date and forwarder name.

----------


Edit


I'm getting a step closer, if I remove the "Check - End Loc. Country not in TBL_Source_POD_Margin" and replace it with "Test" in the code below, I get an Enter Parameter Value box stating "Test". If I continue without entering anything, I almost get the expected results (correct number of lines etc.) except for the fact that the column POD Gross in the table is not populated with the results from QRY_Gross_Performance_3.[POD Gross]

strPODTarget = IIf(DLookup("[POD Target Time]", "[QRY_Gross_Performance_3]") & "" = "", strPODGross, "Test")

I've tried it with "Test", "Country", "Country test" in the last part of the code above, it seems to result in an error as soon as I include any blank (space) in the text.

 

[Minty]

Add the bit in red - you will then see the string as passed to the query in the immediate window in debug mode. That will show you where your string formatting is wrong

Dim strSQL As String
        
        strSQL = "SELECT QRY_Gross_Performance_3.[Shipment Number], QRY_Gross_Performance_3.[Mode], QRY_Gross_Performance_3.[Forwarding agent], QRY_Gross_Performance_3.[Shipping Pnt], QRY_Gross_Performance_3.[Startloc City], QRY_Gross_Performance_3.[Startloc Country], QRY_Gross_Performance_3.[Endloc Shipping Pnt],QRY_Gross_Performance_3.[Endloc City], QRY_Gross_Performance_3.[Endloc Country],QRY_Gross_Performance_3.[Departure Date Plann], QRY_Gross_Performance_3.[Departure Date], QRY_Gross_Performance_3.[Departure Gross], QRY_Gross_Performance_3.[Planned date for end], QRY_Gross_Performance_3.[Actual Date for END], QRY_Gross_Performance_3.[Actual Time for END], QRY_Gross_Performance_3.[Arrival Gross], QRY_Gross_Performance_3.[POD Target Date], QRY_Gross_Performance_3.[POD Target Time], QRY_Gross_Performance_3.[POD Date], QRY_Gross_Performance_3.[POD Time]," & _
        strDestCountry & " AS [POD Gross] INTO TBL_Gross_Performance_Carrier_Temp FROM TBL_Source_POD_Margin RIGHT JOIN QRY_Gross_Performance_3 ON TBL_Source_POD_Margin.[Destination country code] = QRY_Gross_Performance_3.[Endloc Country] WHERE (((QRY_Gross_Performance_3.[Departure Date]) Between #10/01/2015# And #10/31/2015#) AND (" & strWhere & "));"
                                      
        Debug.Print[COLOR="Red"] strSQL[/COLOR]
                                      
        DoCmd.RunSQL strSQL

 

[Minty]

In fact also add a debug.print strWhere that should help as well.

 

[Scaniafan]

Ok, based on your help I got below:

Private Sub Gross_Performance_Carrier_Click()
          
         'make sure a from date has been entered
        If IsNull(From_Date) Then
          MsgBox "'From' date cannot be blank.", vbInformation, "Date required"
          Exit Sub
        End If
        
        'make sure a till date has been entered
        If IsNull(Till_Date) Then
          MsgBox "'Till' date cannot be blank.", vbInformation, "Date required"
          Exit Sub
        End If
        
        'make sure a selection has been made
        If Me.Forwarder_List.ItemsSelected.Count = 0 Then
          MsgBox "At least one forwarding agent must be selected.", vbInformation, "Forwarding agent required"
          Exit Sub
        End If
        
        'DoCmd.SetWarnings False
        
        'Create string from selected carriers
        Dim varItem As Variant
        Dim strWhere As String
            
        For Each varItem In Me![Forwarder_List].ItemsSelected
        strWhere = strWhere & "(QRY_Gross_Performance_3.[Forwarding agent])=" & Chr(39) & Me![Forwarder_List].Column(0, varItem) & Chr(39) & " Or "
        Next varItem
        
        strWhere = Left(strWhere, Len(strWhere) - 4) 'Remove the last " Or "
        
        'Determines IIf results for DoCmd.RunSQL
        Dim strPODGross As String
        Dim strPODTarget As String
        Dim strDestCountry As String
                 
        strPODGross = DLookup("[POD Gross]", "[QRY_Gross_Performance_3]")
        strPODTarget = IIf(DLookup("[POD Target Date]", "[QRY_Gross_Performance_3]") = "", "Check", [COLOR="Red"]strPODGross[/COLOR])
        strDestCountry = IIf(DLookup("[Endloc Country]", "[QRY_Gross_Performance_3]") = DLookup("[Destination country code]", "[TBL_Source_POD_Margin]"), strPODGross, strPODTarget)
             
        'Below strSQL is QRY_Gross_Performance_4
        Dim strSQL As String
                
        strSQL = "SELECT QRY_Gross_Performance_3.[Shipment Number], QRY_Gross_Performance_3.[Mode], QRY_Gross_Performance_3.[Forwarding agent], QRY_Gross_Performance_3.[Shipping Pnt], QRY_Gross_Performance_3.[Startloc City], QRY_Gross_Performance_3.[Startloc Country], QRY_Gross_Performance_3.[Endloc Shipping Pnt],QRY_Gross_Performance_3.[Endloc City], QRY_Gross_Performance_3.[Endloc Country],QRY_Gross_Performance_3.[Departure Date Plann], QRY_Gross_Performance_3.[Departure Date], QRY_Gross_Performance_3.[Departure Gross], QRY_Gross_Performance_3.[Planned date for end], QRY_Gross_Performance_3.[Actual Date for END], QRY_Gross_Performance_3.[Actual Time for END], QRY_Gross_Performance_3.[Arrival Gross], QRY_Gross_Performance_3.[POD Target Date], QRY_Gross_Performance_3.[POD Target Time], QRY_Gross_Performance_3.[POD Date], QRY_Gross_Performance_3.[POD Time]," _
        & strDestCountry & " AS [POD Gross] INTO TBL_Gross_Performance_Carrier_Temp FROM TBL_Source_POD_Margin RIGHT JOIN QRY_Gross_Performance_3 ON TBL_Source_POD_Margin.[Destination country code] = QRY_Gross_Performance_3.[Endloc Country] WHERE (((QRY_Gross_Performance_3.[Departure Date]) Between [Forms]![FRM_KPI_Database_Main]![From_Date] And [Forms]![FRM_KPI_Database_Main]![Till_Date]) AND(" & strWhere & "));"
                                                                  
        Debug.Print strWhere
        
        Debug.Print strPODGross
        
        Debug.Print strPODTarget
      
        Debug.Print strDestCountry
                                                                  
        Debug.Print strSQL
                                                    
        DoCmd.RunSQL strSQL
                                      
        DoCmd.OpenTable "TBL_Gross_Performance_Carrier_Temp"
        
        'DoCmd.SetWarnings True
            
    End Sub

Debug.Print strWhere:

(QRY_Gross_Performance_3.[Forwarding agent])='Carrier1' Or (QRY_Gross_Performance_3.[Forwarding agent])='Carrier2'

Debug.Print strPODGross

POD Late by Date

Debug.Print strPODTarget

POD Late by Date

Debug.Print strDestCountry

POD Late by Date

Debug.Print strSQL:

SELECT QRY_Gross_Performance_3.[Shipment Number], QRY_Gross_Performance_3.[Mode], QRY_Gross_Performance_3.[Forwarding agent], QRY_Gross_Performance_3.[Shipping Pnt], QRY_Gross_Performance_3.[Startloc City], QRY_Gross_Performance_3.[Startloc Country], QRY_Gross_Performance_3.[Endloc Shipping Pnt],QRY_Gross_Performance_3.[Endloc City], QRY_Gross_Performance_3.[Endloc Country],QRY_Gross_Performance_3.[Departure Date Plann], QRY_Gross_Performance_3.[Departure Date], QRY_Gross_Performance_3.[Departure Gross], QRY_Gross_Performance_3.[Planned date for end], QRY_Gross_Performance_3.[Actual Date for END], QRY_Gross_Performance_3.[Actual Time for END], QRY_Gross_Performance_3.[Arrival Gross], QRY_Gross_Performance_3.[POD Target Date], QRY_Gross_Performance_3.[POD Target Time], QRY_Gross_Performance_3.[POD Date], QRY_Gross_Performance_3.[POD Time],POD Late by Date AS [POD Gross] INTO TBL_Gross_Performance_Carrier_Temp FROM TBL_Source_POD_Margin RIGHT JOIN QRY_Gross_Performance_3 ON TBL_Source_POD_Margin.[Destination c
ountry code] = QRY_Gross_Performance_3.[Endloc Country] WHERE (((QRY_Gross_Performance_3.[Departure Date]) Between [Forms]![FRM_KPI_Database_Main]![From_Date] And [Forms]![FRM_KPI_Database_Main]![Till_Date]) AND((QRY_Gross_Performance_3.[Forwarding agent])='Carrier1' Or (QRY_Gross_Performance_3.[Forwarding agent])='Carrier2'));

For confidentiality I changed the forwarding agent names to Carrier1 etc.


- The strWhere is in line with the carriers I select in my Multiselect listbox on the form
- The "From" and "Till" dates are filtered correctly based on the dates I enter on the form (01-oct-2015 - 31-oct-2015)
- When I change the red strPODGross to "Check1", the SELECT SQL returns the correct number of lines in the table based on above criteria
- The POD Gross doesn't return correct. Based on the code it should return "Check1", however it returns blank and I get a Enter Parameter Value box before the table is created.

- If I change the "Check1" back to strPODGross I get below 3075 error

Run-time error '3075':

Syntax error (missing operator) in query expression 'POD Late by Date'.

- The "POD Late by Date" is one of the POD Gross results from QRY_Gross_Performance_3 for the 14 example lines



Looking at the three strings forming the IIf statement, below statements are working correct. However, the result is the error.

strPODGross = DLookup("[POD Gross]", "[QRY_Gross_Performance_3]")

- The example lines all have a (text) value in this field, being either "POD Late by Date" or "POD On Time"

strPODTarget = IIf(DLookup("[POD Target Date]", "[QRY_Gross_Performance_3]") = "", "Check", strPODGross)

- The POD Target Date is for all 14 lines populated, so it returns the strPODGross

strDestCountry = IIf(DLookup("[Endloc Country]", "[QRY_Gross_Performance_3]") = DLookup("[Destination country code]", "[TBL_Source_POD_Margin]"), strPODGross, strPODTarget)

- The Endloc Country is available in the TBL_Source_POD_Margin, so above returns the strPODGross

 

[Minty]

This is your problem;

SELECT QRY_Gross_Performance_3.[Shipment Number], QRY_Gross_Performance_3.[Mode], QRY_Gross_Performance_3.[Forwarding agent], QRY_Gross_Performance_3.[Shipping Pnt], QRY_Gross_Performance_3.[Startloc City], QRY_Gross_Performance_3.[Startloc Country], QRY_Gross_Performance_3.[Endloc Shipping Pnt],QRY_Gross_Performance_3.[Endloc City], QRY_Gross_Performance_3.[Endloc Country],QRY_Gross_Performance_3.[Departure Date Plann], QRY_Gross_Performance_3.[Departure Date], QRY_Gross_Performance_3.[Departure Gross], QRY_Gross_Performance_3.[Planned date for end], QRY_Gross_Performance_3.[Actual Date for END], QRY_Gross_Performance_3.[Actual Time for END], QRY_Gross_Performance_3.[Arrival Gross], QRY_Gross_Performance_3.[POD Target Date], QRY_Gross_Performance_3.[POD Target Time],
QRY_Gross_Performance_3.[POD Date], QRY_Gross_Performance_3.[POD Time],[COLOR="Red"]POD Late by Date[/COLOR] AS [POD Gross] INTO TBL_Gross_Performance_Carrier_Temp 
FROM TBL_Source_POD_Margin 
RIGHT JOIN QRY_Gross_Performance_3 ON TBL_Source_POD_Margin.[Destination country code] = QRY_Gross_Performance_3.[Endloc Country] WHERE (((QRY_Gross_Performance_3.[Departure Date]) Between [Forms]![FRM_KPI_Database_Main]![From_Date] And [Forms]![FRM_KPI_Database_Main]![Till_Date]) AND((QRY_Gross_Performance_3.[Forwarding agent])='Carrier1' Or (QRY_Gross_Performance_3.[Forwarding agent])='Carrier2'));

This needs to be in single quotes if it's a string or [ ] if it's referring to a field.

 

[Scaniafan]

Ok, I've changed the string to strPODGross = "'" & DLookup("[POD Gross]", "[QRY_Gross_Performance_3]") & "'"

Which now populates the POD Gross as 'POD Late by Date' for all 14 example lines, although the way I read it, I should get the POD Gross from QRY_Gross_Performance_3, which is either 'POD Late by Date' or 'POD On Time'.

One step closer to the solution but I am starting to get hang on why people say that there shouldn't be any sharp objects near you when coding VBA.

 

[Minty]

Your dlookup has no criteria, so will only ever return the first record it finds. You need to set it for each record, so in my simple view, you should probably be pulling it through from the query somewhere.

 

[Scaniafan]

Ok, I didn't know that. I'm going to work on it and if I have any questions on that part, I'll come back here. Thank you for your time for now!

 

[Minty]

You've worked through this really well so far, so keep at it.
I suspect you have learnt a lot more doing it the slightly "hard" way, rather than just being sent a solution (which to be honest I couldn't have done from your original post).

Normally if you are using a lot of dlookups you maybe haven't formatted your query quite correctly. Sometimes you maybe need to pull all your data into a saved query then use VBA built query to effectively filter those initial query results.

 

[Scaniafan]

Ever since your comment, I think that I'm over-engineering this part from the beginning though. Anyhow, Monday will be the next moment I'll be working on it... Weekend it is

 

[Scaniafan]

And it is solved:banghead:

With your latest statement I started this morning by simply building the IIf statement in an additional query, and having the strSQL pull the data from that query and thus only having to filter on date and selected carriers.

Final strSQL is now

strSQL = "SELECT QRY_Gross_Performance_4.[Shipment Number], QRY_Gross_Performance_4.[Mode], QRY_Gross_Performance_4.[Forwarding agent], QRY_Gross_Performance_4.[Shipping Pnt], QRY_Gross_Performance_4.[Startloc City], QRY_Gross_Performance_4.[Startloc Country], QRY_Gross_Performance_4.[Endloc Shipping Pnt], QRY_Gross_Performance_4.[Endloc City], QRY_Gross_Performance_4.[Endloc Country], QRY_Gross_Performance_4.[Departure Date Plann], QRY_Gross_Performance_4.[Departure Date], QRY_Gross_Performance_4.[Departure Gross], QRY_Gross_Performance_4.[Planned date for end], QRY_Gross_Performance_4.[Actual Date for END], QRY_Gross_Performance_4.[Actual Time for END], QRY_Gross_Performance_4.[Arrival Gross], QRY_Gross_Performance_4.[POD Target Date], QRY_Gross_Performance_4.[POD Target Time], QRY_Gross_Performance_4.[POD Date], QRY_Gross_Performance_4.[POD Time], QRY_Gross_Performance_4.[POD Gross] INTO TBL_Gross_Performance_Carrier_Temp " _
        & "FROM QRY_Gross_Performance_4 WHERE ((" & strWhere & ") AND ((QRY_Gross_Performance_4.[Departure Date]) Between [Forms]![FRM_KPI_Database_Main]![From_Date] And [Forms]![FRM_KPI_Database_Main]![Till_Date]));"

 

[Minty]

Glad you have it sorted out!