Setting Date and Count if statements within Macro

[twinkle99]

I have a Macro which imports data from an Access Table (Tbl1mdb) to an Excel Workbook (Book 1 - Sheet1). The Macro runs as expected.

Within the Macro, there are two dates which are hardcoded, an extract from the VBA is below:

"Select 'Tbl1mdb'.compdate, 'Tbl1mdb'.targdate, 'Tbl1mdb.code & "FROM 'C:\mymdb" & "WHERE ('Tbl1mdb.targdate<={ts '2007-05-22 00:00:00'} And 'Tbl1mdb.targdate>={ts '2007-04-29 00:00:00'})"

An Extract of the imported Data in Excel is also shown (columns A - C)

Book1
	A	B	C	D
1	compdate	targdate	code
2	25/05/200700:00	25/05/200700:00	A
3	18/06/200700:00	18/06/200700:00	D
4	15/06/200700:00	15/06/200700:00	A
5	20/06/200700:00	19/06/200700:00	A
6	22/04/200700:00	21/04/200700:00	A
7	21/04/200700:00	20/04/200700:00	A
8	12/04/200700:00	12/04/200700:00	B
9	18/05/200700:00	16/05/200700:00	D
10	22/05/200700:00	19/05/200700:00	D
11	19/05/200700:00	20/05/200700:00	D
12	12/05/200700:00	13/05/200700:00	E
13	13/05/200700:00	13/05/200700:00	E
14	16/05/200700:00	17/05/200700:00	A
15	28/05/200700:00	28/05/200700:00	A
Sheet1

At the front of this Macro I would like to have one input box (or Two if one isnt possible) which would ask the user for a 'Startdate' and 'Enddate'.

These dates would then replace the dates in the code above. I am not sure how to set these dates in the code and would welcome any ideas or alternatives.

Also, at the end of the Macro I would like to run 'CountIf' Statements on the imported data in Excel. For example, if Column C (Code) = 'A' then I want the Macro to return a count of rows which meet the Statement to Sheet 2 (A1). I will be running lots of 'Countif' statements but this is an example. I would prefer this to be done in the Macro as the data in Sheet 1 will always change by number of rows depending on the dates. I also want to run a 'Countif' Statement in the Macro which compares compdate (column A) and targdate (column B) and counts if compdate > Targdate. I am really not sure how to write this in the Macro but am sure it can be done.

Many Thanks for listening

 

[twinkle99]

Ok

I have added a few lines of code to the front of the Macro to see if I could set the start and end date as follows:

dim mydate1 As Date, mydate2 As Date
mydate1 = inputbox("enter start date")
mydate2 = inputbox("enter end date")

I then tried to substitute the start and end date into the macro as follows:

"Select 'Tbl1mdb'.compdate, 'Tbl1mdb'.targdate, 'Tbl1mdb.code & "FROM 'C:\mymdb" & "WHERE ('Tbl1mdb.targdate<={ts 'mydate2'} And 'Tbl1mdb.targdate>={ts 'mydate1'})"

So basically I want the data from Access which falls between Start date and end Data.

When I run the Macro it didnt import any data at all!. Is it because the start and end date must be in the same date format as the Access dates in the Macro i.e. 'yyyy-mm-dd hh:mm:ss'?, Or is it more complicated that this?....please, please help, maybe there is an alternative way of doing this but ideally I really only want one piece of code that will work for any period (as entered by the user).

I have managed to get the Countif function to work for column C

[A1] = Application.WorksheetFunction.Countif(Range("C:C"), "A"). This returned the correct result of 7.

I am still having trouble with the Countif compdate > targdate in columns A and B. I tried:

[A2] = Application.WorksheetFunction.Countif(Range("A:A") > Range("B:B")) but this is obviously wrong, any ideas anyone??

Please, please help

Thanks