I am attempting to run a search query from a form. I have done it before with no problems, but for some reason this one is not wanting to work right. All the information shows up, but when I enter the condition, it will not bring up the record I want it to, just returns a blank form when I use the search form that i created. It does work when I run it without using the search form and input the criteria when it askes for it. Here is the SQL for the query, any suggestions on what I am doing wrong?


SELECT tblVehicles.*, tbluGSATag.[GSATag #], [tbluDivision POC].[Division POC], tbluDrivers.Name, [tbluDivision List].Division, tbluShop.Shop, tbluMake.VehicleMake, tbluYear.VehicleYear, [tbluVehicle Description].[Vehicle Description], tblVehicles.[Bumper #]
FROM [tbluVehicle Description] INNER JOIN (tbluShop INNER JOIN ([tbluDivision List] INNER JOIN (tbluYear INNER JOIN (tbluMake INNER JOIN (tbluDrivers INNER JOIN ([tbluDivision POC] INNER JOIN (tbluGSATag INNER JOIN tblVehicles ON tbluGSATag.[GSA TagID] = tblVehicles.GSATagID) ON [tbluDivision POC].DivisionID = tblVehicles.[Division POC_ID]) ON tbluDrivers.[Driver ID] = tblVehicles.[Driver ID]) ON tbluMake.VehicleMakeID = tblVehicles.MakeID) ON tbluYear.VehicleYearID = tblVehicles.YearID) ON [tbluDivision List].DivisionID = tblVehicles.DivisionID) ON tbluShop.ShopID = tblVehicles.ShopID) ON [tbluVehicle Description].VehicleID = tblVehicles.VehicleID
WHERE (((tbluGSATag.[GSATag #])=[Forms]![frmVehicleSearch]![GSATagNumber]));

Thanks,
Kat

what type of fields are these??

tbluGSATag.[GSATag #])

[Forms]![frmVehicleSearch]![GSATagNumber

They are both text fields.

Ok, it works if I use an unbound text box, but is it possible to get it to work with a cbo so the user has to enter something that is on the list?

see what happens if you use the trim on each of the fields. Perhaps you have something like this happening where there might be a space in the front or back






EDIT:

if you use a CBO, make sure what you have for column counts and bound column.

Ok, that works with the unbound text box, but is there a way to make the search work using a combo box instead of the text box?

you can do it with a combobox. just have to make sure that the bound column is the column that has the information you want to use as the criteria.
so, you original syntax would be ok, just have to be careful

Ok, I got the cbo to work with it...I was trying to run the search against the table with the list in it vs the table that actually has the information in it. Once I switched it over, it works perfectly.

Thanks!

glad you got it working.
good luck with the rest of the project

ok... I have 5/6 of the boxes working. the combo box for the bumper # is not working. I created it and built it in the query the same way I did all the others. Not sure what the problem is...


SELECT tblVehicles.ID, tblVehicles.GSATagID, tbluGSATag.[GSATag #], tblVehicles.[Bumper #], [tbluDivision POC].[Division POC], tblVehicles.[Division POC_ID], tbluDrivers.Name, [tbluDivision List].Division, tblVehicles.DivisionID, tbluShop.Shop, tblVehicles.ShopID, tbluMake.VehicleMake, tblVehicles.MakeID, tbluYear.VehicleYear, tblVehicles.YearID, [tbluVehicle Description].[Vehicle Description], tblVehicles.[Card (04980)], tblVehicles.VIN, tblVehicles.GVWR, tblVehicles.[GSA EQ CD], tblVehicles.Notes
FROM [tbluVehicle Description] INNER JOIN (tbluShop INNER JOIN ([tbluDivision List] INNER JOIN (tbluYear INNER JOIN (tbluMake INNER JOIN (tbluDrivers INNER JOIN ([tbluDivision POC] INNER JOIN (tbluGSATag INNER JOIN tblVehicles ON tbluGSATag.[GSA TagID] = tblVehicles.GSATagID) ON [tbluDivision POC].DivisionID = tblVehicles.[Division POC_ID]) ON tbluDrivers.[Driver ID] = tblVehicles.[Driver ID]) ON tbluMake.VehicleMakeID = tblVehicles.MakeID) ON tbluYear.VehicleYearID = tblVehicles.YearID) ON [tbluDivision List].DivisionID = tblVehicles.DivisionID) ON tbluShop.ShopID = tblVehicles.ShopID) ON [tbluVehicle Description].VehicleID = tblVehicles.VehicleID
WHERE (((tblVehicles.GSATagID)=[Forms]![frmVehicleSearch]![Combo33])) OR (((tblVehicles.[Bumper #])=[Forms]![frmVehicleSearch]![Bumper #])) OR (((tblVehicles.DivisionID)=[Forms]![frmVehicleSearch]![Combo35])) OR (((tblVehicles.ShopID)=[Forms]![frmVehicleSearch]![Shop])) OR (((tblVehicles.MakeID)=[Forms]![frmVehicleSearch]![Make])) OR (((tblVehicles.YearID)=[Forms]![frmVehicleSearch]![Year])) OR (((tblVehicles.[Division POC_ID])=[Forms]![frmVehicleSearch]![Division POC]));

Im not sure if the problem is the control source for the bumper number "looks funny!" Not sure how else to describe it... It looks different from the other fields and I'm not sure why.

Thanks!
Kat