Using Like in Criteria of a Query

  • Thread starter Thread starter Jared
  • Start date Start date
J

Jared

I have a database that tracks service information for vehicles.

A vehicle can be put into a group, typically by depot.

On the dashboard of the program I have a combo box that lists all of the
vehicle groups ie Depot1, Depot2 etc.

When a user picks a vehicle from a dropdown on the service screen, I only
want them to see the vehicles grouped in the depot that has been selected on
the dashboard.

I can get this to work fine by adding the combobox on the dashboard to the
query criteria of the services vehicle combo box.

However, I'd like the user to be able select "all" or "any". The trouble I'm
having is in the criteria of the query to select all:

SELECT vehicle.vehicle_ID, vehicle.registration, vehicle.vehicle_group
FROM vehicle
WHERE
(((vehicle.vehicle_group)=IIf([Forms]![frmMain]![user_group]="_ALL",(vehicle.vehicle_group) Like "*",[Forms]![frmMain]![user_group])));

I can get Like * to work on its own but not as part of the if statement.

Any help would be greatly appreciated. Thankyou
 
Thanks Mate. Worked perfectly.

I appreciate your quick response, I normally have to wait overnight.

Jared

MGFoster said:
Jared said:
I have a database that tracks service information for vehicles.

A vehicle can be put into a group, typically by depot.

On the dashboard of the program I have a combo box that lists all of the
vehicle groups ie Depot1, Depot2 etc.

When a user picks a vehicle from a dropdown on the service screen, I only
want them to see the vehicles grouped in the depot that has been selected on
the dashboard.

I can get this to work fine by adding the combobox on the dashboard to the
query criteria of the services vehicle combo box.

However, I'd like the user to be able select "all" or "any". The trouble I'm
having is in the criteria of the query to select all:

SELECT vehicle.vehicle_ID, vehicle.registration, vehicle.vehicle_group
FROM vehicle
WHERE
(((vehicle.vehicle_group)=IIf([Forms]![frmMain]![user_group]="_ALL",(vehicle.vehicle_group) Like "*",[Forms]![frmMain]![user_group])));

I can get Like * to work on its own but not as part of the if statement.

Any help would be greatly appreciated. Thankyou
Click to expand...


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Usually the criteria for that is like this:

WHERE (vehicle_group = Forms!frmMain!user_group
OR Forms!frmMain!user_group = "_ALL")

When the user has selected a group the first part of the criteria is
used to select just those rows (records) with the selected group. When
the user_group = "_ALL" the OR part of the criteria is used. This
returns TRUE which essentially means "select all rows (that match the
other criteria, if any)."

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSmVrcIechKqOuFEgEQIiSQCeKV8aOQvoIS3DZfhVc4SJKE/igNoAni/h
S5yegQXznvIYY0Iv5arUqEeg
=sAHE
-----END PGP SIGNATURE-----
Click to expand...
 
Back
Top