Excel 2007 : Using Microsoft Query to extract data from a database based on todays date

I am currently using Act! as a CRM. I am able to create reports based on product sales in Excel using the Actreader. I am using MS Query to structure these reports based on fields I have created in the Act database. We run a publishing companny and have sales per magazine per month. I am trying to return all the sales for a certain magazine issue (say 2010 October edition) but only return the values where the closed date = todays date (we do daily sales reports).

Basically in MS query I have a field ACTUALCLOSEDATE which is a date field that automatically updates when you change another field from OPEN to CLOSED. I want to display all the values where ACTUALCLOSEDATE = todays date but cannot structure it correctly. You can only currently choose it in the format: #2010/10/13 10:00:00 PM# for example. '

The query runs fine if you choose a date as above that is the actual date and time in the system but only want the sales for date = today.

Can anyone help please?

Thanks a lot.

Sandra

Hi,

Ok, I can see the principle behind it and have run it like you said where you have to enter the parameter in and point it to the cell with =today(). It is now returning no values. When I put the ACTUAL CLOSE DATE criteria equals 2010/10/16 it returns all sales for the 16th but not if you put =today() so weird.
Here is the code like you said with the last line being the date parameter:
SELECT TBL_OPPORTUNITY.CUST_Magazine_050816166, TBL_PRODUCTSERVICE.NAME, TBL_PRODUCTSERVICE.CUST_IssueYear_013717519, TBL_PRODUCTSERVICE.CUST_1Month_120207366, TBL_PRODUCTSERVICE.QUANTITY, TBL_PRODUCTSERVICE.ITEMTYPE, TBL_CONTACT.COMPANYNAME, TBL_CONTACT.FULLNAME, TBL_PRODUCTSERVICE.UNITPRICE, TBL_OPPORTUNITY.ACTUALCLOSEDATE
FROM TEST3SSALES.dbo.CONTACT_OPPORTUNITY CONTACT_OPPORTUNITY, TEST3SSALES.dbo.OPPORTUNITY_PRODUCTSERVICE OPPORTUNITY_PRODUCTSERVICE, TEST3SSALES.dbo.TBL_CONTACT TBL_CONTACT, TEST3SSALES.dbo.TBL_OPPORTUNITY TBL_OPPORTUNITY, TEST3SSALES.dbo.TBL_PRODUCTSERVICE TBL_PRODUCTSERVICE
WHERE CONTACT_OPPORTUNITY.CONTACTID = TBL_CONTACT.CONTACTID AND CONTACT_OPPORTUNITY.OPPORTUNITYID = TBL_OPPORTUNITY.OPPORTUNITYID AND OPPORTUNITY_PRODUCTSERVICE.OPPORTUNITYID = TBL_OPPORTUNITY.OPPORTUNITYID AND TBL_PRODUCTSERVICE.PRODUCTSERVICEID = OPPORTUNITY_PRODUCTSERVICE.PRODUCTSERVICEID AND ((TBL_OPPORTUNITY.CUST_Magazine_050816166='IMIESA') AND (TBL_PRODUCTSERVICE.CUST_IssueYear_013717519='2011') AND (TBL_PRODUCTSERVICE.CUST_1Month_120207366='01/January') AND (TBL_OPPORTUNITY.STATUSNUM=1) AND (TBL_OPPORTUNITY.ACTUALCLOSEDATE=?))

And then with the one that works how I want it to but you have to go in and select it every time:
SELECT TBL_OPPORTUNITY.CUST_Magazine_050816166, TBL_PRODUCTSERVICE.NAME, TBL_PRODUCTSERVICE.CUST_IssueYear_013717519, TBL_PRODUCTSERVICE.CUST_1Month_120207366, TBL_PRODUCTSERVICE.QUANTITY, TBL_PRODUCTSERVICE.ITEMTYPE, TBL_CONTACT.COMPANYNAME, TBL_CONTACT.FULLNAME, TBL_PRODUCTSERVICE.UNITPRICE, TBL_OPPORTUNITY.ACTUALCLOSEDATE
FROM TEST3SSALES.dbo.CONTACT_OPPORTUNITY CONTACT_OPPORTUNITY, TEST3SSALES.dbo.OPPORTUNITY_PRODUCTSERVICE OPPORTUNITY_PRODUCTSERVICE, TEST3SSALES.dbo.TBL_CONTACT TBL_CONTACT, TEST3SSALES.dbo.TBL_OPPORTUNITY TBL_OPPORTUNITY, TEST3SSALES.dbo.TBL_PRODUCTSERVICE TBL_PRODUCTSERVICE
WHERE CONTACT_OPPORTUNITY.CONTACTID = TBL_CONTACT.CONTACTID AND CONTACT_OPPORTUNITY.OPPORTUNITYID = TBL_OPPORTUNITY.OPPORTUNITYID AND OPPORTUNITY_PRODUCTSERVICE.OPPORTUNITYID = TBL_OPPORTUNITY.OPPORTUNITYID AND TBL_PRODUCTSERVICE.PRODUCTSERVICEID = OPPORTUNITY_PRODUCTSERVICE.PRODUCTSERVICEID AND ((TBL_OPPORTUNITY.CUST_Magazine_050816166='IMIESA') AND (TBL_PRODUCTSERVICE.CUST_IssueYear_013717519='2011') AND (TBL_PRODUCTSERVICE.CUST_1Month_120207366='01/January') AND (TBL_OPPORTUNITY.STATUSNUM=1) AND (TBL_OPPORTUNITY.ACTUALCLOSEDATE={ts '2010-11-16 22:00:00'}))

There are 5 tables: Contact, Opportunity and Product with 2 Unique ID tables linking the three together. As I said before, I am using Actreader to pull the information out of Sage Act! CRM system. Act! stamps the ACTUALCLOSEDATE with the current time when you change an opportunity from open to closed.

So I don't know if I have to put in the parameters like Act understands them or Excel??

Thank very much for your prompt reply.

Kind regards,
Sandra