Excel 2007 Macro: User-specified date input gets data from Access 2007 file

[cactusman]

Morning all!

Looking to create an Excel 2007 macro that takes a user-supplied start and and date and uses that to query an Access 2007 database and return records between such dates. I have been able to create a macro that gets all of the data but I cannot for the life of me figure out how to get it to use the dates.

Say, for example, I have a start date in cell B1 and end date in E1. I need to take those dates and add it to my SQL-type code (included below). The Access database used a date format like "MM/DD/YYYY HH:MM" where HH is 24-hour clock. I would like to remove the times from the mix entierly.


SQL as follows:

SELECT NCR.NCRNum, NCR.AddDate_NCR, NCR.CPIWarehouse, NCR.VendorNum, NCR.VendorName, NCR.PartNum, NCR.PartName, NCRLine.LineNum, NCRLine.Qty, NCRLine.DefectDesc, Initiator.Initiator, NCR.InspectorStamp, NCR.DueDate_CAR
 
FROM `J:\QUALITY\QUALITY ENGINEERING\CAR Development\NCR_CAR.accdb`.Initiator Initiator, `J:\QUALITY\QUALITY ENGINEERING\CAR Development\NCR_CAR.accdb`.NCR NCR, `J:\QUALITY\QUALITY ENGINEERING\CAR Development\NCR_CAR.accdb`.NCRLine NCRLine
 
WHERE NCR.NCR_ID = NCRLine.NCR_ID AND NCRLine.Initiator = Initiator.InitiatorID AND ((NCR.NCRNum>=3000) AND (NCR.Status_NCR<>'VOID'))
 
ORDER BY NCR.NCRNum, NCRLine.LineNum

Thanks in advance!

Matt

 

[ChrisM]

You don't need a macro for this, the built in functions will work.

First you need to create a query to your Access table by going to ribbon Data, From Other Sources, Microsoft Query.

Choose your database, query name, and fields using the wizard. Once you get to the step where the wizard asks you sort order, hit cancel. A popup will ask you to continue in MS Query, choose yes.

In the MS Query window, look for the Criteria row, if it's not visible use the View menu to fix it. Bring your date fields down to the criteria row, just like in Access. Add a placeholder criteria value for each, by typing a word in brackets, like [start date] and [end date]

Close the entire query window (the larger window, not the smaller one). The query will run and prompt you for the start and end dates. Type something simple, like a one or two day range, just to get some data back.

Now you're back in Excel. Follow all the command prompts and let the query run and output to your sheet. Now right click your data and you should see a Parameters menu option. Follow that, and you will see an option to set your [start dates] equal to a range. Point those ranges to your user-selected start/stop date cells and you should be all set.