List box criteria

[UT227]

I have a list box, lstVehicleMileage.
The SQL in the Row Source is: SELECT [MileageID], [UnitTypeID], [UnitName], [UseDate], [BeginMileage], [EndMileage], [TotalMileage] FROM qryVehicleMileageUser ORDER BY [UseDate] DESC;
I want it to only get the records of the Employee on the form. I want it to look for [EmployeesID] in that query. The form is called, frmVehicleMileage.

Is there a way to write the criteria in the list box instead of the query? That way, I can use that same query for something else also.

[June7]

Don't reference query object. Build the SQL statement directly in the RowSource.

SELECT [MileageID], [UnitTypeID], [UnitName], [UseDate], [BeginMileage], [EndMileage], [TotalMileage] FROM qryVehicleMileageUser WHERE EmployeesID = [EmployeesID] ORDER BY [UseDate] DESC;

[UT227]

Worked perfectly. Thanks.

[MadTom]

You could use

Like "*" & [Enter Name:] & "*" in the criteria in your Query and use the QueryName in the RowSource and call the Query from your form. You can still use the Query for other things.
MT

[June7]

If EmployeesID is just digits, LIKE might not work nice. LIKE "*2*" would return 2, 200, 1200, 702, etc. So if the field is autonumber, a bunch of records would return.

If EmployeesID is text field and value is fixed length such as 021049, 135207, then the LIKE and wildcard would work.

[UT227]

I'm having problems with this again. I have another form that I'm trying to do almost the same thing. I have a list box [lstCTWSubjectInformation]. The SubjectID is column 1. That information comes from a query [qryCTWSubjectInReport]. My second list box is trying to get information from a query [qryCTWTrespassedFrom]. I'm trying to use the information from the first list box [lstCTWSubjectInformation] as the criteria. This is what I came up with:
SELECT [SubjectID], [BldgName] FROM qryCTWTrespassedFrom WHERE SubjectID = forms!frmRptCTWEntry!lstCTWSubjectInformation.colu mn(1) ORDER BY [BldgName];

That gave me this error:


I'm not sure what I'm doing wrong.

[June7]

Query objects cannot resolve the Column(x) reference.

Columns are referenced by index. Index begins with 0. So if data is in first column, its index is 0.

Don't use the Column(x) reference.

[UT227]

This wasn't in the query. It was in the code to the list box.

[June7]

You mean the RowSource property? Same issue. It's still an SQL statement and Access is running a query in the background.

A textbox ControlSource property can resolve the Column(x) reference. So if you need a value from column other than 0, have a textbox pull that value then SQL references the textbox as parameter.

However, if desired value is in first column, there is no need to use Column(0).

[UT227]

I simplified to for myself. I just redid my query and changed the sql so that the subjectID was 0. It was in column 1. It's working. Thanks.