Unanswered: Problems letting Access 2000SP3 know that new field in qbGrid is Literal Date
I have a field (from a table I did not define) in my query called P_INVOICE_DTE and it is defined as a long integer.
I added my own new field to the QBEGrid called Date: with the following code:
This converts the long integer back into a relevant (accurate) date for me-all is good.
Now I want to setup a parameter query on this new field called Date
Between [Enter Beginning Date] And [Enter Ending Date]
....but this does not return the correct recordset (it returns all records)
when I put in a literal date like #11/16/2004# it returns correctly.
The SQL Expression looks like this:
SELECT HINVDB_S_ORD_HDR.P_SLSID, HINVDB_S_ORD_HDR.P_INVOICE_DTE, DateAdd("d",[P_Invoice_Dte],"12/31/1971") AS [Date], HINVDB_S_ORD_HDR.P_INVOICE, HINVDB_S_ORD_DTL.P_ORDNO, HINVDB_S_ORD_DTL.P_INVOICE, HINVDB_S_ORD_DTL.P_ITEM, HINVDB_S_ORD_DTL.P_DESCRIPTION__1, HINVDB_S_ORD_DTL.P_DESCRIPTION__2
FROM HINVDB_S_ORD_DTL INNER JOIN HINVDB_S_ORD_HDR ON HINVDB_S_ORD_DTL.ROWID = HINVDB_S_ORD_HDR.ROWID
WHERE (((HINVDB_S_ORD_HDR.P_SLSID)=[enter rep#]) AND ((DateAdd("d",[P_Invoice_Dte],"12/31/1971")) Between [Enter Beginning Date] And [Enter Ending Date]));
I believe I have to somehow indicate to Access 2000 SP3 that this new field I created is a Date by using the # symbols, but I do not
know the proper syntax to reference this.
Can anyone offer some guidance?