Combo-Box source query issue

[cheuschober]

Hiya! So, I have a form with a cascading combo-ish type solution. In actuality it's an option group that helps feed a combo-box. The option group, however, is unbound and the two choices represented in it span several choices elsewhere.

The field I'm reducing with a cascading solution is my [ExpenseCode]field. Each [ExpenseCode] is assigned one of 6 [ExpenseType]'s, numbered 0-6. Deductible expenses range from 1-4, non-deductible expenses cover 5, and 0 is used only by the system in the creation of dummy records and is unnecessary to the data-entry form. Using the [ExpenseType] directly would be confusing to the end user but is important to us during aggregation.

I am attempting to use an option group to simply select "Deductible" or "Non-Deductible" from the listed fields and have my combo-box update itself in kind.

The form is: frmExpenseLogsEntry
The option group is: optExpense1 (where Non-Deductible=1 and Deductible=2)
The combo-box in question is: cboExpense
The table with the combo-source is: lkupExpenseCode
The field I'm populating the combo-box with is: ExpenseCodeID
And the qualifying field is: ExpenseTypeID

The SQL I was using for my control source is as follows:

SELECT lkupExpenseCode.ExpenseCodeID,
FROM lkupExpenseCode
WHERE (((lkupExpenseCode.ExpenseTypeID)=IIf([Forms]![frmExpenseLogEntry]![optExpense1]=1,5,Between 1 and 4)));

but Access keeps changing it and erroring. I think I've figured it out enough to know that this type of statement won't work but I'm a bit stuck for an alternate idea.

Any help would be sincerely appreciated.
~Chad

 

[RichO]

The error you're getting may be from the comma after the first line...

Try your SQL this way:

SELECT ExpenseCodeID
FROM lkupExpenseCode
WHERE ExpenseTypeID Between IIf([Forms]![frmExpenseLogEntry]![optExpense1]=1,5,1) And IIf([Forms]![frmExpenseLogEntry]![optExpense1]=1,5,4);

 

[cheuschober]

Ahh, that comma was actually just a code pasting issue as I simplified it for posting by taking out a couple fields that had no real bearing to the discussion...

However...

Rich, your fix worked like a gem! Thank you so much!
~Chad