Pass two Excel date field variables to an Access query.

[twoplustwo]

Hi all, hope everyone is well.

I have just finished an Access db.

I'd like this to be the back end to a simple Excel programme.

I need to set a DateFrom and DateTo field in the query from two excel fields. It'll then read the query into a data sheet in Excel which a chart/summary table will be based on (which I think I can do). Just a little stuck with passing these variables.

I have had a look around the webs and couldn't find owt. Thanks for any help suggested.

 

[twoplustwo]

Could anybody point me in the right direction?

 

[DCrake]

Simple Software Solutions

I have some doumentation on passing public variables in a similar way as you request. The only difference if the identification of the two dates. My suggestion would be to create a function that opens the Excel Workbook and references the two cells in the worksheet and passes them to Access.

What you have not stated is which app will have the focus at the point of control will it be Access or Excel. Ie will you be in Excel and wanting to change the date range criteria of the query in Access base don what you type into the two cell in Excel or not?

CodeMaster:

 

[twoplustwo]

Hi DC,

Ya, you have it spot on, I'd like to enter the dates in Excel and pass them to the queryin access in a kind of "between X and Y dates" string then pull the data into the excel data sheet.

Thanks for the paper, interesting stuff.

 

[DCrake]

Simple Software Solutions

So having read the documentation, what you need to do is to assign the dates to the StrDateLower and StrDateUpper variables.

Just thought of another way to do it.

When you enter ythe date range in Excel have a button on the spreadhseet that when clicked re write the SQL for the query using the QueryDef.Sql = NewSQL methodology.

David

 

[twoplustwo]

Hi David,

Thanks for the response.

That sounds ideal.

I'll take a look at it today/tomo and might post back with the results