JustJerry
02-03-2006, 11:44 AM
I am trying to build a query based on a specific Year and one other field.
I have this query which will show records based on a specific month, AND a specific TruckID or ALL TruckID's should this be left Null. This works great:
SELECT tblFinalMile.EventDate, tblFinalMile.TruckID, tblFinalMile.StatePr, tblFinalMile.Elapsed
FROM tblFinalMile
WHERE (((tblFinalMile.TruckID)=[Forms]![frmStart]![cboTruckID]) AND
((Format([EventDate],"mmm yyyy"))=Format([Forms]![frmStart]![SearchDate],"mmm yyyy"))) OR
(((Format([EventDate],"mmm yyyy"))=Format([Forms]![frmStart]![SearchDate],"mmm yyyy")) AND
(([Forms]![frmStart]![cboTruckID]) Is Null))
ORDER BY tblFinalMile.TruckID, tblFinalMile.StatePr;
Now I am trying to create a second query to basically do the same thing, except to show records for a specific year and have so far come up with this:
SELECT tblFinalMile.EventDate, tblFinalMile.TruckID, tblFinalMile.StatePr, tblFinalMile.Elapsed
FROM tblFinalMile
WHERE ((DatePart("yyyy",[EventDate])=DatePart("yyyy",[Forms]![frmStart]![SearchDate])));
If I enter in the same criteria for cboTruckID as I did in the above query for the month, it keeps showing me ALL records no matter if cboTruckID is Null or set to a specific ID and ignores the criteria for the 'Year'.
I've tried several combinations, but they when running the query, I get NO records to show.
Any Help is much appreciated.
I have this query which will show records based on a specific month, AND a specific TruckID or ALL TruckID's should this be left Null. This works great:
SELECT tblFinalMile.EventDate, tblFinalMile.TruckID, tblFinalMile.StatePr, tblFinalMile.Elapsed
FROM tblFinalMile
WHERE (((tblFinalMile.TruckID)=[Forms]![frmStart]![cboTruckID]) AND
((Format([EventDate],"mmm yyyy"))=Format([Forms]![frmStart]![SearchDate],"mmm yyyy"))) OR
(((Format([EventDate],"mmm yyyy"))=Format([Forms]![frmStart]![SearchDate],"mmm yyyy")) AND
(([Forms]![frmStart]![cboTruckID]) Is Null))
ORDER BY tblFinalMile.TruckID, tblFinalMile.StatePr;
Now I am trying to create a second query to basically do the same thing, except to show records for a specific year and have so far come up with this:
SELECT tblFinalMile.EventDate, tblFinalMile.TruckID, tblFinalMile.StatePr, tblFinalMile.Elapsed
FROM tblFinalMile
WHERE ((DatePart("yyyy",[EventDate])=DatePart("yyyy",[Forms]![frmStart]![SearchDate])));
If I enter in the same criteria for cboTruckID as I did in the above query for the month, it keeps showing me ALL records no matter if cboTruckID is Null or set to a specific ID and ignores the criteria for the 'Year'.
I've tried several combinations, but they when running the query, I get NO records to show.
Any Help is much appreciated.