Query with combo box

[goslincm]

When I run the query below, it works fine, until I want to filter on the [name] or [expense] fields, both of which are a combo box. Cannot enter a criteria in either of those fields?

SELECT tbl_LUG.Name, tbl_EXPENSE.exp_type, tbl_EXPENSE.exp_amount, tbl_EXPENSE.exp_date
FROM tbl_LUG
INNER JOIN tbl_EXPENSE ON (tbl_LUG.AUDIT_ID = tbl_EXPENSE.Audit_id) AND (tbl_LUG.LUG_ID = tbl_EXPENSE.LUG_id)
WHERE (((tbl_EXPENSE.exp_date) Between "5/1/2007" And "5/30/2007"));

 

[goslincm]

Look like I need to search by the bound field of my combo box.

 

[dhookom]

Fields are Text or Numeric or Date or... They might be displayed as a combo box in datasheet view if you have used the mis-feature of lookup fields

http://www.mvps.org/access/lookupfields.htm.

<soapbox>
IMHO all criteria values for queries, reports, forms,... should be entered into controls on forms. Parameter prompts aren't acceptable in applications.
</soapbox>

Also, I would expect your query to not work as expected if your criteria is truly [blue]Between "5/1/2007" And "5/30/2007"[/blue]. Dates should normally be delimited with #s.




Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[goslincm]

Thanks for the information Duane. My expense table has a text field called [exp_type]. On my form that the user is using, I want them to only select from the expense types in my expense type table.

What is the correct way to set this up? Via a relationship between my expenses table and expense types table?

I believe I tried that, having table TYPE with a the primary key being a text field called [type] and linking it to my [exp_type] field in the table EXPENSES.

 

[dhookom]

To limit users to a specific group of values, you should create a table of those values. Then use a form with a combo box that is set to limit to the list. The Row Source of the combo box would be the table of all possible values.

Your lookup table might contain only one field like:
[tt][blue]
tblGenders
=============
Male
Female
Undecided
[/blue][/tt]

or may have multiple fields like:
[tt][blue]
tblGenders
=============
M Male
F Female
U Undecided
[/blue][/tt]

or even using an autonumber field
[tt][blue]
tblGenders
=============
1 M Male
2 F Female
3 U Undecided
[/blue][/tt]

Your main table (expenses) should store the field that is the primary key field of your lookup table.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[goslincm]

I have table_expenses which contains:

exp_id (pk)
exp_date
exp_type
exp_comments
exp_mileage

I also have table_TYPES which contains:

type (pk)
type_PNumber
type_OCode

For each expense type, when the user is in that field now in my form, they have a combo box which have the following types of expenses available for them to choose from:

Premium Mileage
Standard Mileage
Lodging
Meals
Other

Everything seems to work fine now witin the form for the user to select the type of expense, BUT, when I want to run a query now to it won't run. I'm trying to run a report that list all expenses for a given voucher in the body of the report, but then summarize all my expenses types and amounts for that same voucher.

I was going to do this by inserting a subreport for the summary information however I cannot get it working in my main report.


SELECT Sum(tbl_EXPENSE.exp_amount) AS SumOfexp_amount, tbl_voucher.voucher_id, tbl_EXPENSE_TYPES.Type, tbl_EXPENSE_TYPES.Type_Index, tbl_EXPENSE_TYPES.Type_PCA

FROM (tbl_voucher INNER JOIN tbl_NATURE_OF_BUSINESS ON tbl_voucher.voucher_id = tbl_NATURE_OF_BUSINESS.voucher_id)
INNER JOIN (tbl_EXPENSE INNER JOIN tbl_EXPENSE_TYPES ON tbl_EXPENSE.TYPE = tbl_EXPENSE_TYPES.Type) ON tbl_NATURE_OF_BUSINESS.audit_id = tbl_EXPENSE.Audit_id

GROUP BY tbl_voucher.voucher_id, tbl_EXPENSE_TYPES.Type, tbl_EXPENSE_TYPES.Type_Index, tbl_EXPENSE_TYPES.Type_PCA;

 

[dhookom]

It's near impossible for us to trouble-shoot since you have tables and data we have no knowledge of. I generally remove table from the query until it works and make a note of which table/join caused the issue.

Your statements "it won't run" and "cannot get it working" have very little value if you don't tell use what caused you to state them.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[goslincm]

I apologize for not doing a better job of explaining my situation. I have this expense summary query which I run and it works perfectly well. It is summarizing my daily expenses according to voucher_id.

I've taken this query and made a report which I insert as sub report into my voucher report. It is when I bring the two together into one report that my expenses are no longer being summarized by type of expense for that particular voucher being reported.

QUERY FOR EXPENSE SUMMARY
SELECT Sum(tbl_EXPENSE.exp_amount) AS SumOfexp_amount, tbl_voucher.voucher_id, tbl_EXPENSE.TYPE, tbl_EXPENSE_TYPES.Type_Index, tbl_EXPENSE_TYPES.Type_PCA


FROM tbl_voucher
INNER JOIN (tbl_LUG INNER JOIN ((tbl_NATURE_OF_BUSINESS INNER JOIN tbl_EXPENSE ON tbl_NATURE_OF_BUSINESS.audit_id = tbl_EXPENSE.Audit_id) INNER JOIN tbl_EXPENSE_TYPES ON tbl_EXPENSE.TYPE = tbl_EXPENSE_TYPES.Type) ON (tbl_NATURE_OF_BUSINESS.audit_id = tbl_LUG.AUDIT_ID) AND (tbl_LUG.AUDIT_ID = tbl_EXPENSE.Audit_id) AND (tbl_LUG.LUG_ID = tbl_EXPENSE.LUG_id)) ON tbl_voucher.voucher_id = tbl_NATURE_OF_BUSINESS.voucher_id

GROUP BY tbl_voucher.voucher_id, tbl_EXPENSE.TYPE, tbl_EXPENSE_TYPES.Type_Index, tbl_EXPENSE_TYPES.Type_PCA;

QUERY FOR MY VOUCHER REPORT
SELECT tbl_EMPLOYEES.emp_lname, tbl_EMPLOYEES.emp_fname, tbl_EMPLOYEES.emp_bus_phone, tbl_EMPLOYEES.emp_street, tbl_voucher.voucher_begin_date, tbl_NATURE_OF_BUSINESS.audit_type, tbl_NATURE_OF_BUSINESS.audit_id, tbl_EXPENSE.exp_amount, tbl_EXPENSE.TYPE, tbl_EXPENSE.exp_date, tbl_EXPENSE.exp_comments, tbl_EXPENSE.exp_miles_travelled, tbl_voucher.voucher_fiscal_year, tbl_EMPLOYEES.emp_city, tbl_EMPLOYEES.emp_state, tbl_EMPLOYEES.emp_zip, tbl_EMPLOYEES.emp_work_station, tbl_EMPLOYEES.emp_contact, tbl_EMPLOYEES.emp_contact_phone, tbl_LUG.Name, tbl_EMPLOYEES.emp_comments, tbl_voucher.voucher_id, tbl_EMPLOYEES.emp_id, tbl_voucher.voucher_end_date, tbl_EMPLOYEES.emp_state_car

FROM (tbl_EMPLOYEES
INNER JOIN tbl_voucher ON tbl_EMPLOYEES.emp_id = tbl_voucher.emp_id) INNER JOIN (tbl_LUG INNER JOIN (tbl_NATURE_OF_BUSINESS INNER JOIN tbl_EXPENSE ON tbl_NATURE_OF_BUSINESS.audit_id = tbl_EXPENSE.Audit_id) ON (tbl_NATURE_OF_BUSINESS.audit_id = tbl_LUG.AUDIT_ID) AND (tbl_LUG.AUDIT_ID = tbl_EXPENSE.Audit_id) AND (tbl_LUG.LUG_ID = tbl_EXPENSE.LUG_id)) ON tbl_voucher.voucher_id = tbl_NATURE_OF_BUSINESS.voucher_id

GROUP BY tbl_EMPLOYEES.emp_lname, tbl_EMPLOYEES.emp_fname, tbl_EMPLOYEES.emp_bus_phone, tbl_EMPLOYEES.emp_street, tbl_voucher.voucher_begin_date, tbl_NATURE_OF_BUSINESS.audit_type, tbl_NATURE_OF_BUSINESS.audit_id, tbl_EXPENSE.exp_amount, tbl_EXPENSE.TYPE, tbl_EXPENSE.exp_date, tbl_EXPENSE.exp_comments, tbl_EXPENSE.exp_miles_travelled, tbl_voucher.voucher_fiscal_year, tbl_EMPLOYEES.emp_city, tbl_EMPLOYEES.emp_state, tbl_EMPLOYEES.emp_zip, tbl_EMPLOYEES.emp_work_station, tbl_EMPLOYEES.emp_contact, tbl_EMPLOYEES.emp_contact_phone, tbl_LUG.Name, tbl_EMPLOYEES.emp_comments, tbl_voucher.voucher_id, tbl_EMPLOYEES.emp_id, tbl_voucher.voucher_end_date, tbl_EMPLOYEES.emp_state_car

HAVING (((tbl_voucher.voucher_id)=[forms]![frm_employees]![frm_vouchers]![voucher_id]));

Hopefully this will help so someone can assist me.

 

[dhookom]

Does your final query return any records? I assume you have a subform since you are using "[forms]![frm_employees]![frm_vouchers]![voucher_id]". Is this syntax correct? What do you see if you open the debug window (press Ctrl+G) and enter

  ?[forms]![frm_employees]![frm_vouchers]![voucher_id]

Do you see a legitimate/expected value?

Also, Name is not a good name for anything in Access since every object has a Name property.

Also, your query should apply the filter in the WHERE clause, not the HAVING clause.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[goslincm]

The report is correct, is does provide legitimate values for the voucher the form is displaying. The subreport however does not return legitimate values when running within the report, the values being returned are not specific to the voucher id.


When I click my button to run the report (rpt_travel_expense_report) then click on properties and look at my data source, the query displays the correct expenses for the voucher.

I'm wondering when I inserted my subreport, I placed it in the page footer section. I also only refer the the main report "rpt_travel_expense_report" behind my button, I don't include the name of the subreport.

 

[dhookom]

I didn't understand that you were still attempting to use a subreport. If you want to filter a subreport based on a value in a main report, you can use the Link Master/Child properties.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[goslincm]

I thought I had to use a subreport thats why I did and I did the linking of the subreport into the main report (page footer area) by linking master/child via voucher_id.

 

[dhookom]

I can't image putting a subreport of multiple records in a page footer. Page headers and footers are the only sections of reports that can't grow. Is there a reason why you used the page footer rather than a group footer or other section?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[goslincm]

No reason other than inexperience

 

[goslincm]

What I want the report to do, after itemizing all expenses, is to group them by type and provide a total. At the bottom of my report for a given voucher.

 

[dhookom]

the bottom of my report" is the Report Footer section. You can use the same basic query for your subreport as your main report except group by Type and don't include details (only sums and group bys).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]