complex query/form results unexpected

[Bhaughbb]

I've got a query pulling data from a view I created that gives all the data I need. The query gets it's criteria from a form with 10 combo boxes, a radio box with 3 options and 2 date fields for the timeframe to run the report in.

The issue:
over half the columns of data that the radio buttons can limit results by have null values but only two of the columns are excluding records where the values in them are null. As any combination of the combo boxes are null I'm using the following format for my criteria per column

IIf(IsNull([Forms]![Margin Dialog]![InstallerOption]),[Installer_from_Web],[Forms]![Margin Dialog]![InstallerOption])

That is an example of a column that works, the following is a column that is excluding null values no matter what I do

IIf(IsNull([Forms]![Margin Dialog]![AOIOption]),[ProductType],[Forms]![Margin Dialog]![AOIOption])

I am not seeing the error and could use some thoughts.

 

[namliam]

I think stuff like this is much easier if you build the query on demand in VBA... Doing it like this is madness....

 

[Bhaughbb]

Primarily it wasn't built that way as this was the quickest to write solution that we thought up at the time and it runs quickly. This data problem wasn't noticed until the last couple days, the report has been in use for months, as we've started going back and confirming report accuracy and was curious if anyone had any ideas of a quick solution to the query's issue to avoid a major re-write such as VBA due to current workload but I'll look into rebuilding it. Thanks for the advice!

 

[namliam]

Any criteria will exclude null values... becuase null never equals anything.

The only way I know around that is doing an "or is null" to every clause you build in...

But like I said, its hell. In particular in maintenance. Doing it in VBA is much easier, both to build and maintain IMHO.

 

[Bhaughbb]

OK, I got the VBA generating the query and the report is running perfectly. Thanks for the pointer on that, actually made the report more functional.

Now I have one last problem, the other feature I had available to the powers that be was to dump the raw query results to an excel file so they could sort/filter to their hearts' content. I'm stumped as to how to get my query code from the variable I built it in out to a file, thoughts?

 

[namliam]

Just put your build query into a 'real' query and export it to a spreadsheet or something...

 

[Bhaughbb]

Found the solution, just had to define my variable type properly:

Dim qryString As AcObjectType.acQuery

Then I could run it in a DoCmd.Output to and treat the variable as my query:

DoCmd.OutputTo acOutputQuery, qryString, acFormatXLS

Works like a charm. Thanks again for the pointers.

 

[namliam]

Hmz

AcObjectType.acQuery

I didnt know that even excisted, so I too learn something today... Thanks...

 

[Bhaughbb]

Always glad to help, now to figure out why it seems to have failed as a solution.

It looked like a good idea. When I tested it after I came up with the idea on Friday morning it worked beautifully, fast and no flaws. Now I'm getting a "Compile error: Type mismatch" on the exact code that worked for my tests. The confusing part is why it worked the first times and not after I quit and came back to actually use the report later.

Here's the complete code:

Private Sub ExportData_Click()
' define variable as a query object so it can be acted upon by DoCmd.OutputTo
Dim qryString As AcObjectType.acQuery

' query field selection
qryString = "SELECT Job_Number, Part_No, Description, Rn_Create_Date, Quantity, Channel_Price, Ext_Channel_Price, Cost, Ext_Cost, Margin, [Margin%], Designer, Installer_Code, is_install_item, Installer_from_Web, ProductType AS Area_of_Interest, Store_Number, Pricing_Region, Upload_Date, vendor_code, category, type_code, ActiveStoreType AS Type " & _
    "FROM dbo_vw_sales_and_margin_US " & _
    "WHERE Upload_Date Between #" & [Start Date] & "# And #" & [End Date] & "#"

' query variable definition

Select Case RadioOption
    Case 1
        qryString = qryString & " AND is_install_item = 0"
    Case 2
        qryString = qryString & " AND is_install_item = 1"
    Case 3
    Case Else
End Select

If IsNull(InstallerOption) Then
Else
    qryString = qryString & " AND Installer_from_Web like '" & InstallerOption & "'"
End If

If IsNull(AOIOption) Then
Else
    qryString = qryString & " AND ProductType like '" & AOIOption & "'"
End If

If IsNull(StoreOption) Then
Else
    qryString = qryString & " AND Store_Number like '" & StoreOption & "'"
End If

If IsNull(PricingRegionOption) Then
Else
    qryString = qryString & " AND Pricing_Region like '" & PricingRegionOption & "'"
End If

If IsNull(VendorOption) Then
Else
    qryString = qryString & " AND vendor_code like '" & VendorOption & "'"
End If

If IsNull(InventoryOption) Then
Else
    qryString = qryString & " AND category like '" & InventoryOption & "'"
End If

If IsNull(ProductGroupOption) Then
Else
    qryString = qryString & " AND type_code like '" & ProductGroupOption & "'"
End If

If IsNull(ChannelOption) Then
Else
    qryString = qryString & " AND ActiveStoreType like '" & ChannelOption & "'"
End If

If IsNull(DesignerOption) Then
Else
    qryString = qryString & " AND Designer_Code like '" & DesignerOption & "'"
End If

If IsNull(SKUOption) Then
Else
    qryString = qryString & " AND Part_No like '" & SKUOption & "'"
End If

qryString = qryString & ";"

' Debug.Print qryString

' export data
DoCmd.OutputTo acOutputQuery, qryString, acFormatXLS

End Sub

 

[namliam]

I would say check your query if you have strings and number 'properly' matched...
This one for example
" AND Part_No like '" & SKUOption & "'"
part_No would suggest a number field but you are comparing it with a string...
" AND Part_No = " & SKUOption & " "

Would seem much more logical...

 

[Bhaughbb]

I'll give that a shot and share the results.

When it errors it's flagging the 'Dim qryString...' line. What confuses me is I saved the code and ran it from the form to test the first couple times but now it fails.

--edit--

The query fails when when I eliminate the single quotes that end up around the contents of the form field names.

 

[Bhaughbb]

OK, I just hit upon a major flaw wiht the approach I took to building this query in VBA.

For my reports I had the code

Private Sub SummaryButton_Click()

Dim rptString As String
Dim qryString As String

rptString = "a-Sales Report - Summary"

' query field selection
qryString = "SELECT Job_Number, Part_No, Description, Rn_Create_Date, Quantity, Channel_Price, Ext_Channel_Price, Designer, Installer_Code, is_install_item, Installer_from_Web, ProductType AS Area_of_Interest, Store_Number, Pricing_Region, Upload_Date, vendor_code, category, type_code, ActiveStoreType AS Type " & _
    "FROM dbo_vw_sales_and_margin_US " & _
    "WHERE Upload_Date Between #" & [Start Date] & "# And #" & [End Date] & "#"

' query variable definition

Select Case RadioOption
    Case 1
        qryString = qryString & " AND is_install_item = 0"
    Case 2
        qryString = qryString & " AND is_install_item = 1"
    Case 3
    Case Else
End Select

If Not IsNull(InstallerOption) Then
    qryString = qryString & " AND Installer_Code like '" & InstallerOption & "'"
End If

If Not IsNull(AOIOption) Then
    qryString = qryString & " AND ProductType like '" & AOIOption & "'"
End If

If Not IsNull(StoreOption) Then
    qryString = qryString & " AND Store_Number like '" & StoreOption & "'"
End If

If Not IsNull(PricingRegionOption) Then
    qryString = qryString & " AND Pricing_Region like '" & PricingRegionOption & "'"
End If

If Not IsNull(VendorOption) Then
    qryString = qryString & " AND vendor_code like '" & VendorOption & "'"
End If

If Not IsNull(InventoryOption) Then
    qryString = qryString & " AND category like '" & InventoryOption & "'"
End If

If Not IsNull(ProductGroupOption) Then
    qryString = qryString & " AND type_code like '" & ProductGroupOption & "'"
End If

If Not IsNull(ChannelOption) Then
    qryString = qryString & " AND ActiveStoreType like '" & ChannelOption & "'"
End If

If Not IsNull(DesignerOption) Then
    qryString = qryString & " AND Designer_Code like '" & DesignerOption & "'"
End If

If Not IsNull(SKUOption) Then
    qryString = qryString & " AND Part_No like '" & SKUOption & "'"
End If

qryString = qryString & ";"

Debug.Print qryString

' Don't display prompts
DoCmd.Echo False

' apply query to report - opening in design view and setting query as data source, save and close
DoCmd.OpenReport rptString, acViewDesign
With Reports(rptString)
    .RecordSource = qryString
End With
DoCmd.Close , , acSaveYes

' show prompts
DoCmd.Echo True

' open report
DoCmd.OpenReport rptString, acViewPreview

End Sub

Unfortunately at any given time several people can be in this same access file at the same time across the network which means the query in the report does not update.

Is there a way to make this happen in a multi user setting or should I go back to my previous query method of a ton of IIF statements to determine my criteria?

 

[namliam]

The way you are doing it is not very wize, it will cause bloating and is not suited to having a multi user environment as you are discovering.

You should put the query building in the on open event of the Report. Then simply fill the Me.Recordsource, this should work nicely in a multiuser environment, prevent bloating and make live generaly easier for you.

Regards

 

[Bhaughbb]

I'd completely forgotten about those events in reports, I'll take a look and give it a try. Thanks!

And in 6 months we get to throw these out and rebuild them in .Net .. the joys of stepped system changes.