MS Access：过滤查询组合框错误 [英] MS Access: Filter query combo box error

问题描述

我的问题：我有一个查询过滤数据，取决于输入到窗体中的复选框，文本框和组合框中的数据。除了一个单一的组合框，所有这一切都很好。如果我输入数据到这个组合框，叫'cmbBodyType'，不管我输入什么数据，它每次都会返回0个结果。

My issue: I have a query which filters data depending on what is entered into checkboxes, textboxes and comboboxes located in a form. All of this works perfectly fine except for one single combo box. If I enter data into this combo box, called 'cmbBodyType', regardless of what data I enter, it will return 0 results every time.

我的代码到目前为止因此我将粘贴所有下面，然后我将粘贴我已经用于过滤组合框的片段。

My code so far is very lengthy so i will paste it all below, then i will paste a snippet of the i have used to filter for a combo box.

    SELECT [Car Table].Car_VIN, [Car Table].Car_Class, [Car Table].Car_BodyType,
[Car Table].Car_Colour, [Car Table].Car_Make, [Car Table].Car_Model, [Car
Table].Car_EngineType, [Car Table].Car_TransmissionType, [Car
Table].Car_GPSAvailability, [Car Table].Car_BootSpace, [Car
Table].Car_FuelConsumptRate, [Car Table].Car_SeatNumber, [Car
Table].Car_GreenStarRating, [Car Table].Car_ANCAPSafetyRating, [Car
Table].Car_DriveType
FROM [Car Table]

       WHERE ((([Car Table].Car_VIN) Like "*" & [Forms]![Navigation Form]!
    [NavigationSubform].[Form]![txtVIN] & "*") AND (([Car Table].Car_Class) Like "*"
    & [Forms]![Navigation Form]![NavigationSubform].[Form]![cmbClass] & "*") AND
    (([Car Table].Car_DriveType) Like "*" & [Forms]![Navigation Form]!
    [NavigationSubform].[Form]![cmbDriveType] & "*") AND (([Car Table].Car_BodyType)
    Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![cmbBodyType] &
    "*") AND (([Car Table].Car_Colour) Like "*" & [Forms]![Navigation Form]
    ![NavigationSubform].[Form]![cmbBodyType] & "*") AND (([Car Table].Car_Make)
    Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![txtMake] & "*")
    AND (([Car Table].Car_Model) Like "*" & [Forms]![Navigation Form]
    ![NavigationSubform].[Form]![txtModel] & "*") AND (([Car Table].Car_EngineType)
    Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![cmbEngineType]
    & "*") AND (([Car Table].Car_TransmissionType) Like "*" & [Forms]![Navigation
    Form]![NavigationSubform].[Form]![cmbTransmissionType] & "*") AND (([Car
    Table].Car_GPSAvailability) Like "*" & [Forms]![Navigation Form]
    ![NavigationSubform].[Form]![ChGPSAvailability] & "*") AND (([Car
     Table].Car_SeatNumber) Like "*" & [Forms]![Navigation Form]
    ![NavigationSubform].[Form]![txtSeatNumber] & "*") AND (([Car
     Table].Car_GreenStarRating) Like "*" & [Forms]![Navigation Form]
    ![NavigationSubform].[Form]![cmbGreenStarRating] & "*") AND (([Car
     Table].Car_ANCAPSafetyRating) Like "*" & [Forms]![Navigation Form]
    ![NavigationSubform].[Form]![cmbANCAPSafetyRating] & "*") AND
     ((Abs([car_fuelconsumptrate]-Nz([Forms]![Navigation Form]![NavigationSubform]
    .[Form]![txtFuelConsumption],[car_fuelconsumptrate]))<=Nz([2],9999)))
AND ((Abs([car_bootspace]-Nz([Forms]![Navigation Form]![NavigationSubform].[Form]
![txtBootSpace], [car_BootSpace]))<=Nz([100],9999))));

代码段（这将在WHERE中，或者更简单地说，在设计视图中显示）：

snippet (this would be in the WHERE are, or to put it more simply, this is the critera displayed in design view):

Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![cmbBodyType] & "*"

编辑：看来我的'green star rating'组合框在搜索不管它的值。看起来工作和非工作组合框之间的一切都是一样的...我注意到的一个事情是，不工作的两个组合框有比其他，工作，组合框更多的选择。

it also seems that my 'green star rating' combobox makes no difference in the search regardless of its value. It seems that everything between the working and non-working comboboxes are the same... one thing i have noticed is that the two comboboxes that don't work both have a larger number of choices than the other, working, comboboxes.

推荐答案

您可以在WHERE语句中使用

You could use a part in your WHERE statement like that

   ...AND [Car Table].Car_BodyType Like 
               IIf(IsNull([Forms]![Navigation Form]![NavigationSubform].[Form]![cmbBodyType]),"*" ,
               [Forms]![Navigation Form]![NavigationSubform].[Form]![cmbBodyType])...

找到 MS Access搜索表单需要从未绑定的组合框传递值到查询
阅读它进一步的解释，以及注释。

Found at MS Access Search Form need to pass values from unbound combo box to query Read it for further explanation, also the comments.

BTW：

(([Car Table].Car_Colour) Like "*" & [Forms]![Navigation Form]
    ![NavigationSubform].[Form]![cmbBodyType] & "*")

您在cmbBodyType中搜索Car_color

You search in Car_color from cmbBodyType

小心这个巨大的SQL

Be careful with this huge SQL

编辑：星级评分系统：这个字段是什么类型？如果是数字字段，LIKE不适用。 LIKE是用于文字afaik。

Star rating system: What type is this field? If it is a number field, LIKE does not apply. LIKE is meant for text afaik.

这篇关于MS Access：过滤查询组合框错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！