CODEWITHSUNDEEP
sqlms-accesscombobox
BlueRhapsody
BlueRhapsody

Reputation: 93

MS Access: Filter query combo box error

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))));

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] & "*"

Edit: 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.

Upvotes: 2

Views: 418

Answers (1)

asdev
asdev

Reputation: 943

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])...

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] & "*")

You search in Car_color from cmbBodyType

Be careful with this huge SQL

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

Upvotes: 1

Related Questions