date calculated field

[raskew]

OK, looking at the sample you provided off-line:

The table name is filter_qFiscal, not filter.qFiscal. Note the underline versus the period.

The fields are tbl_code, tbl_Start and tbl_Stop.

Thus, the query SQL is (see below). I've tested this and it works as advertised.

PARAMETERS [Enter TestDate] DateTime;
SELECT
    filter_qFiscal.tbl_Code
  , filter_qFiscal.tbl_Start
  , filter_qFiscal.tbl_Stop
FROM
   filter_qFiscal
WHERE
   (((filter_qFiscal.tbl_Start)<=[Enter TestDate]) 
AND
   ((filter_qFiscal.tbl_Stop)>=[Enter TestDate]));

Bob

 

[Xx_TownDawg_xX]

Yes it does sir. The code below changes the parameter SQL to a SELECT SQL -- and now I in essence have a LOOKUP table for every audit date.

SELECT DISTINCT filter_qFiscal.tbl_Code, filter_qFiscal.tbl_Start, filter_qFiscal.tbl_Stop, tbl_DataEntry.Audit_Date
FROM filter_qFiscal, tbl_DataEntry
WHERE (((filter_qFiscal.tbl_Start)<=[Audit_Date]) AND ((filter_qFiscal.tbl_Stop)>=[Audit_Date]))
ORDER BY filter_qFiscal.tbl_Stop;

Now that you have seen my dbase structure, how would you suggest I get that tbl.code (FiscalMonth) back into where I need it?

And after a little thought.. I got it.

SELECT tbl_DataEntry.SEQ_Nbr, tbl_DataEntry.Auditor_ID, tlkp_Audit.tbl_desc AS tlkp_Audit_tbl_desc, tlkp_Audit.tbl_type AS tlkp_Audit_tbl_type, tbl_DataEntry.Series_ID, tlkp_Product.tbl_desc AS tlkp_Product_tbl_desc, tlkp_Product.tbl_type AS tlkp_Product_tbl_type, tbl_DataEntry.Qty_Made, tbl_DataEntry.Qty_Rejected, tbl_DataEntry.Defect_Code, tlkp_Defect.tbl_desc AS tlkp_Defect_tbl_desc, tlkp_Defect.tbl_type AS tlkp_Defect_tbl_type, tbl_DataEntry.SO_Nbr, tbl_DataEntry.Audit_Date, tbl_DataEntry.Audit_Time, tbl_DataEntry.[Comments/Explanation], tbl_DataEntry.Disposition, tbl_Severity.tbl_desc, tbl_Severity.tbl_severity, tlkp_Product.tbl_type, qry_qFiscal.tbl_Code AS FiscalMonth
FROM qry_qFiscal, tlkp_Product INNER JOIN (tlkp_Defect INNER JOIN (tlkp_Audit INNER JOIN (tbl_Severity INNER JOIN tbl_DataEntry ON tbl_Severity.tbl_code = tbl_DataEntry.Disposition) ON tlkp_Audit.tbl_code = tbl_DataEntry.Auditor_ID) ON tlkp_Defect.tbl_code = tbl_DataEntry.Defect_Code) ON tlkp_Product.tbl_code = tbl_DataEntry.Series_ID;

THANK YOU. (and my apologies for being so slow to understand everything you were telling me.)

 

[raskew]

Hi -

Glad its starting to work for you. However (comma) I return to some earlier comments.

You need to somehow get the perpetrators to define their business rules. From what I can tell (in this situation) they have none! Instead, it appears they are merely throwing darts at the calendar. The only apparent consistency is that FY months begin on a Monday and end on a Sunday. I'd love to be a 'fly on the wall' as they attempt to tap-dance thru this is how we do it.

There's no such thing as "the first Monday prior to the last Friday" (or any other day). They're all over the landscape. To me, this utter lack of consistency creates serious problems when attempting to develop automated solutions.

Bob