MS Access Using ComboBox Value in SQL Field

0
votes

Trying to set up a query where one of the fields is selected by the value in a combobox. Can't figure out the right syntax. I'm in the SQL view of the query builder, using the following:

SELECT Deviations.Deviation, Deviations.Rev, Deviations.Title, Deviations.Issued, Deviations.ExpiryDate, Deviations.ExpiryHours, Deviations.ExpOther, Deviations.Active, Deviations.[Forms]![DeviationSelectionForm]![cmbAircraft]
FROM Deviations
WHERE Deviations.[Forms]![DeviationSelectionForm]![cmbAircraft]=True
ORDER BY Deviations.Deviation DESC

The combo box selects the tail number of the aircraft which is a checkbox in the table. There are multiple aircraft tail numbers as checkbox fields. So if there were no combobox and I wanted the query to use as example aircraft 416, the query would be: 

SELECT Deviations.Deviation, Deviations.Rev, Deviations.Title, Deviations.Issued, Deviations.ExpiryDate, Deviations.ExpiryHours, Deviations.ExpOther, Deviations.Active, Deviations.[416]
FROM Deviations
WHERE Deviations.[416]=True
ORDER BY Deviations.Deviation DESC

When I test this query it works as I need it to.

So the question is how to I create the query with the combobox in a form identifying the field name of a table?

1
sqlms-accesscombobox

1 Answers

1
votes

Build the Query SQL viacode in the combobox afterUpdate event.

Dim strSql as String
strSql = "SELECT Deviations.Deviation, Deviations.Rev, Deviations.Title, Deviations.Issued, " _
& "Deviations.ExpiryDate, Deviations.ExpiryHours, Deviations.ExpOther, " _
& "Deviations.Active, Deviations." & Me!cmbAircraft _
& " FROM Deviations " _
& "WHERE Deviations." & Me!cmbAircraft & " =True " _
& "ORDER BY Deviations.Deviation DESC"
CurrentDb.QueryDefs(<yourqueryname>).SQL = strSql

That said, it seems like you have built a spreadsheet instead of a database. Your tables are not normalized. Aircraft should be records, not columns. I suggest you read up on database design and normalization.