I want my VBA statment to ask for a date range once only...pleasssee

[mattaus]

Hi i am new to VBA and i am hoping somebody will help be write the following vba code...I need to include a statement so if I enter a date once I don't need to enter further dates as at the moment after each query runs i have to keep enetering date ranges.

I have managed to adapt the code below but im not sure how to insert that close

I am very new to this please find the code below: Please simplify any responses...THANKS

Sub Test_Reporta()

Dim AppExcel As Object
Dim LOCReport As Recordset
Dim LOCReport2 As Recordset

Dim CurrentSheet As Variant
Dim SPos As Integer
Dim rpos As Integer
Dim cpos As Integer
Dim i As Integer
Dim j As Integer
Dim count As Integer
Dim datasheet As Variant
Dim Test As Variant
Dim RepType As Integer
Dim Desc As String

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Dim EndDate As String
Dim StartDate As Date



Dim LocC As String
Dim LocL As String
Dim Par1 As Date
Dim Par2 As Date
Dim TeamNo As String
Dim strSql As String

' Stops warnings from appearing
DoCmd.SetWarnings False

' ********************************************************************************************

Set AppExcel = CreateObject("excel.application")
AppExcel.Visible = True
' Opens Excel template


'Selects Specialty
Select Case [Forms]![Test]![lstSpecialty]
Case "DNU"
AppExcel.Workbooks.Open "C:\Documents and Settings\msundhu\Desktop\Copy of DNU Activity_Revised.xls", , True

Case Else
MsgBox "No Valid Specialty selected"
Exit Sub

End Select

'***********************************************************************************************

'Defines variable to queries records

'Extract all Referrals
AppExcel.StatusBar = "Running Referrals"


Select Case [Forms]![Test]![lstSpecialty]
Case "DNU"


AppExcel.StatusBar = "Running Referrals"

'runs the querie and puts the data into seperate table
strSql = "PARAMETERS Startdate DateTime, EndDate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([RefrlDate],""yyyymm"") AS [Date], Count(dbo_vwReferrals.REFRL_REFNO) AS CountOfREFRL_REFNO INTO tblDNUReferals " & vbCrLf & _
"FROM dbo_vwReferrals " & vbCrLf & _
"WHERE (((dbo_vwReferrals.RefrlDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwReferrals.StatusID) Not In (""R"",""C""))) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([RefrlDate],""yyyymm"");"

DoCmd.RunSQL strSql



'selects all the data from the newly formed table
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblDNUReferals.Service, tblDNUReferals.Date, tblDNUReferals.CountOfREFRL_REFNO FROM tblDNUReferals")

'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run(LOCReport, datasheet, RepType)






AppExcel.StatusBar = "Running Community First Appointments"

strSql = "PARAMETERS StartDate DateTime, EndDate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO, IIf([schduledate]=[icntDate],""First"",""F/Up"") AS [Appt Type] INTO tblCommNew " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.Shared) Is Null) AND ((dbo_vwSchedules.SchdlTypeID) Like ""c*"") AND ((dbo_vwSchedules.StatusID) Like ""f"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm""), IIf([schduledate]=[icntDate],""First"",""F/Up"") " & vbCrLf & _
"HAVING (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""First""));"

DoCmd.RunSQL strSql



Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommNew.Service, tblCommNew.Date, tblCommNew.CountOfSCHDL_REFNO FROM tblCommNew")



'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run2(LOCReport, datasheet, RepType)




AppExcel.StatusBar = "Running Community Follow-Up Appointments"

strSql = "PARAMETERS StartDate DateTime, EndDate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO, IIf([schduledate]=[icntDate],""First"",""F/Up"") AS [Appt Type] INTO tblCommFUP " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.Shared) Is Null) AND ((dbo_vwSchedules.SchdlTypeID) Like ""c*"") AND ((dbo_vwSchedules.StatusID) Like ""f"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm""), IIf([schduledate]=[icntDate],""First"",""F/Up"") " & vbCrLf & _
"HAVING (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""F/Up""));"

DoCmd.RunSQL strSql



Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommFUP.Service, tblCommFUP.Date, tblCommFUP.CountOfSCHDL_REFNO FROM tblCommFUP")


'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run3(LOCReport, datasheet, RepType)


AppExcel.StatusBar = "Running Community Shared First Appointments"

strSql = "PARAMETERS startdate DateTime, enddate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblCommSharedFirst " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""First"") AND ((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.Shared) Like ""y"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"");"


DoCmd.RunSQL strSql


Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommSharedFirst.Service, tblCommSharedFirst.Date, tblCommSharedFirst.CountOfSCHDL_REFNO FROM tblCommSharedFirst")

'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run4(LOCReport, datasheet, RepType)



AppExcel.StatusBar = "Running Community Shared Follow-Up Appointments"

strSql = "PARAMETERS startdate DateTime, enddate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblCommSharedFUP " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""F/UP"") AND ((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.Shared) Like ""y"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"");"


DoCmd.RunSQL strSql


Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommSharedFUP.Service, tblCommSharedFUP.Date, tblCommSharedFUP.CountOfSCHDL_REFNO FROM tblCommSharedFUP")

'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run5(LOCReport, datasheet, RepType)




AppExcel.StatusBar = "Running Outpatient First Appointments"

strSql = "SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblOutpatinentNew " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""first"") AND ((dbo_vwSchedules.SchduleDate) Between [STARTDATE] And [ENDDATE]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.SchdlTypeID) Like ""o*"") AND ((dbo_vwSchedules.Shared) Is Null) AND ((dbo_vwSchedules.StatusID) Like ""f"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"");"

DoCmd.RunSQL strSql


Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommNew.Service, tblCommNew.Date, tblCommNew.CountOfSCHDL_REFNO FROM tblCommNew")


'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run6(LOCReport, datasheet, RepType)


AppExcel.StatusBar = "Running Outpatient Follow-Up Appointments"

strSql = "SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblOutpatinentNew " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""F/UP"") AND ((dbo_vwSchedules.SchduleDate) Between [STARTDATE] And [ENDDATE]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.SchdlTypeID) Like ""o*"") AND ((dbo_vwSchedules.Shared) Is Null) AND ((dbo_vwSchedules.StatusID) Like ""f"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"");"

DoCmd.RunSQL strSql


Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommFUP.Service, tblCommFUP.Date, tblCommFUP.CountOfSCHDL_REFNO FROM tblCommFUP")


'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run7(LOCReport, datasheet, RepType)



AppExcel.StatusBar = "Running Community DNA"

strSql = "PARAMETERS Startdate DateTime, EndDate DateTime; " & vbCrLf & _
"SELECT dbo_vwReferrals.Service, Format([RefrlDate],""yyyymm"") AS Datea, Count(dbo_vwReferrals.REFRL_REFNO) AS CountOfREFRL_REFNO INTO tblDNUReferals " & vbCrLf & _
"FROM dbo_vwReferrals " & vbCrLf & _
"WHERE (((dbo_vwReferrals.RefrlDate) Between [StartDate] And [EndDate]) AND ((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwReferrals.StatusID) Not In (""R"",""C""))) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([RefrlDate],""yyyymm"");"

DoCmd.RunSQL strSql


Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommContactsDNA.Service, tblCommContactsDNA.Date, tblCommContactsDNA.CountOfSCHDL_REFNO FROM tblCommContactsDNA")


'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run8(LOCReport, datasheet, RepType)


AppExcel.StatusBar = "Running Average F2f Contact Time"

strSql = "SELECT dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"") AS [Date], Avg(Round([Duration])) AS AverageDuration INTO tblAvgContactTimeF2F " & vbCrLf & _
"FROM dbo_vwSchedules " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwSchedules.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.StatusID) Like ""f*"") AND ((dbo_vwSchedules.SchdlTypeID) Like ""c*"") AND ((dbo_vwSchedules.Shared) Is Null)) " & vbCrLf & _
"GROUP BY dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"");"


DoCmd.RunSQL strSql


Set LOCReport = CurrentDb.OpenRecordset("SELECT tblAvgContactTimeF2F.Service, tblAvgContactTimeF2F.Date, tblAvgContactTimeF2F.AverageDuration FROM tblAvgContactTimeF2F")


'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run9(LOCReport, datasheet, RepType)
''
''
''
AppExcel.StatusBar = "Running Outpatient DNA"


strSql = "SELECT dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblOutpatientsDNA " & vbCrLf & _
"FROM dbo_vwSchedules " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwSchedules.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.SchdlTypeID) Like ""o*"") AND ((dbo_vwSchedules.StatusID) Like ""d"")) " & vbCrLf & _
"GROUP BY dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"");"


DoCmd.RunSQL strSql


Set LOCReport = CurrentDb.OpenRecordset("SELECT tblOutpatientsDNA.Service, tblOutpatientsDNA.Date, tblOutpatientsDNA.CountOfSCHDL_REFNO FROM tblOutpatientsDNA")


'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run10(LOCReport, datasheet, RepType)


AppExcel.StatusBar = "Running Clinic Utilisation Part 1"

strSql = "SELECT 'Total Capacity' AS [Total Capacity], Format(dbo_tblServicePointTimeslots.START_DTTM,""yyyymm"") AS [Date], Count(dbo_tblServicePointTimeslots.TSTAT_REFNO) AS CountOfTSTAT_REFNO INTO tblCapacityTotal " & vbCrLf & _
"FROM (dbo_tblServicePointTimeslots LEFT JOIN dbo_tblServicePointSessions ON dbo_tblServicePointTimeslots.SPSSN_REFNO = dbo_tblServicePointSessions.SPSSN_REFNO) INNER JOIN dbo_tblReferenceValues ON dbo_tblServicePointTimeslots.TSTAT_REFNO = dbo_tblReferenceValues.RFVAL_REFNO " & vbCrLf & _
"WHERE (((dbo_tblServicePointTimeslots.START_DTTM) Between [StartDate] And [EndDate]) AND ((dbo_tblServicePointSessions.CODE) Like ""f2fdn*"") AND ((dbo_tblReferenceValues.DESCRIPTION) Not In (""No Longer Available"",""Reserved""))) " & vbCrLf & _
"GROUP BY 'Total Capacity', Format(dbo_tblServicePointTimeslots.START_DTTM,""yyyymm"");"


DoCmd.RunSQL strSql


Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCapacityTotal.Date, tblCapacityTotal.CountOfTSTAT_REFNO FROM tblCapacityTotal")


'selects named excel worksheet
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run11(LOCReport, datasheet, RepType)




AppExcel.StatusBar = "Running Clinic Utilisation Part 2"

strSql = "SELECT Format(dbo_tblServicePointTimeslots.START_DTTM,""yyyymm"") AS [Date], Count(dbo_tblServicePointTimeslots.TSTAT_REFNO) AS CountOfTSTAT_REFNO INTO tblCapacityBooked " & vbCrLf & _
"FROM (dbo_tblServicePointTimeslots INNER JOIN dbo_tblServicePointSessions ON dbo_tblServicePointTimeslots.SPSSN_REFNO = dbo_tblServicePointSessions.SPSSN_REFNO) INNER JOIN dbo_tblReferenceValues ON dbo_tblServicePointTimeslots.TSTAT_REFNO = dbo_tblReferenceValues.RFVAL_REFNO " & vbCrLf & _
"WHERE (((dbo_tblServicePointTimeslots.START_DTTM) Between [StartDate] And [EndDate]) AND ((dbo_tblServicePointSessions.CODE) Like ""f2fdn*"") AND ((dbo_tblReferenceValues.DESCRIPTION) Like ""booked"")) " & vbCrLf & _
"GROUP BY Format(dbo_tblServicePointTimeslots.START_DTTM,""yyyymm"");"


DoCmd.RunSQL strSql


Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCapacityBooked.Date, tblCapacityBooked.CountOfTSTAT_REFNO FROM tblCapacityBooked")


'selects named excel worksheet
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run12(LOCReport, datasheet, RepType)




AppExcel.StatusBar = "Indirect Contacts"
strSql = "SELECT dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblIndirectContacts " & vbCrLf & _
"FROM dbo_vwSchedules " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwSchedules.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.StatusID) Like ""i"") AND ((dbo_vwSchedules.SchdlTypeID) Like ""c*"") AND ((dbo_vwSchedules.Shared) Is Null)) " & vbCrLf & _
"GROUP BY dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"");"


DoCmd.RunSQL strSql


Set LOCReport = CurrentDb.OpenRecordset("SELECT tblIndirectContacts.Service, tblIndirectContacts.Date, tblIndirectContacts.CountOfSCHDL_REFNO FROM tblIndirectContacts")


'selects named excel worksheet
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run14(LOCReport, datasheet, RepType)




AppExcel.StatusBar = "Group Contacts"

strSql = "SELECT dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblGroupContacts " & vbCrLf & _
"FROM dbo_vwSchedules INNER JOIN dbo_tblSchedules ON dbo_vwSchedules.PARNT_REFNO = dbo_tblSchedules.SCHDL_REFNO " & vbCrLf & _
"WHERE (((dbo_vwSchedules.SchduleDate) Between [StartDate] And [EndDate]) AND ((dbo_vwSchedules.ServiceID) Like ""dnu"") AND ((dbo_tblSchedules.SATYP_REFNO) Like ""1452"")) " & vbCrLf & _
"GROUP BY dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"");"

DoCmd.RunSQL strSql


Set LOCReport = CurrentDb.OpenRecordset("SELECT tblGroupContacts.Service, tblGroupContacts.Date, tblGroupContacts.CountOfSCHDL_REFNO FROM tblGroupContacts")


'selects named excel worksheet
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run13(LOCReport, datasheet, RepType)




End Select



DoCmd.SetWarnings True

AppExcel.StatusBar = "Run has finished"
MsgBox "Run has finished"
AppExcel.StatusBar = False
End Sub

 

[ByteMyzer]

What was the question, again?

 

[Banana]

Few things.

You're doing several things that are quite unnecessary. For example, you are dynamically creating a parameter query then executing them.

Normally, it's either dynamic SQL *OR* parameter query, but not dynamic parameter because there is no point in doing so.

You'll need to decide which you prefer dynamic SQL:

Dim sDate as Date
Dim eDAte as Date
Dim sSQL as String
Dim r As DAO.Recordset

sDate = #1/1/2009#
eDate = #1/2/2009#

sSQL = "SELECT * FROM foo WHERE MyDate BETWEEN #" & sDate & "# AND #" & eDate & "#;"

Set r = CurrentDb.OpenRecordset sSQL

Or with Parameter Query:

SQL for the query:

PARAMETERS sDate DATE, eDate DATE;
SELECT * FROM foo WHERE MyDate BETWEEN [sDate] AND [eDate];

Code to execute parameter query without prompting:

Dim sDate As Date
Dim eDate As Date
Dim r As DAO.Recordset

sDate = #1/1/2009#
eDate = #1/2/2009#

With CurrentDb.QueryDefs("MyQueryName")
    .Parameters("sDate") = sDate
    .Parameters("eDate") = eDate
    Set r = .OpenRecordset
End With

To avoid re-prompting, you'd pass your input into a local variable ( like sDate and eDate for example) then have all of your SQLs reference the same variables in same manner as I demonstrated.

HTH.

 

[wiklendt]

first of all, it would be good if you could wrap your code in the appropriate tags (use the HASH symbol in the massage toolbar when you post).

second, how are you curently inputting your start and end dates? and how are you calling this report(?).

why not make an unbound form, have a start date control and an end date control, and then have each SQL refer to those controls in the form when the report starts up?

(edit: i mean, in the part where you declare the strings for the start and end dates, then put in an extra line for each giving them a value, i.e.,

strDateStart = Forms!frmChoseDates.cmbStartDate.Value)


similar to this example/suggestion i posted for someone else (see attachments in post #6).

also have a look at this thread, which shows the code for the above pictures (and boblarson has also posted a sample) - it's the code running FILTERING the queries already in place for the Report. i.e., i can run the report from the asset pane, and it will show ALL the relevant records OR i can run the report from my form, and it will filter for date(s) and/or user.

 

[wiklendt]

Dim EndDate As String
Dim StartDate As Date

why is one a string and the other a date? shouldn't they both be dates?

 

[mattaus]

Thank you so so much you all have been very helpful

 

[mattaus]

I belive having the the start date and end date in an unbound form as the best bet..howver I have been trying all day but i just cant seem to figuer out how to implemnt it even with your help...

Previously I was running it with parameters as each start date and end date howver with around 14 sepearte quieries it makes the task luborious for my manager

you an tell i have no real sql or VBA experience-my manager had just asked me to adapt the VBA code- i managed it eventualy howver I still can't mange to get the date criteria in somehow...

some of my 'Dims' may seem abit strange for i.e. 'enddate' as a string..this was used by my previous to calculates dates i have no use for it however..

I have tried following the instructions but to no avail ( !!!

please find my full code below..please help im already past my deadline date...

I have thus far made the cmbstartdate and cmbenddate in the form but i still dont how to attach the vba code to those start dates and end dates so each of my 'strsql' looks at those dates only...



#############################################################
Option Compare Database
Option Explicit
Sub Test_Reporta()
Dim AppExcel As Object
Dim LOCReport As Recordset
Dim LOCReport2 As Recordset

Dim CurrentSheet As Variant
Dim SPos As Integer
Dim rpos As Integer
Dim cpos As Integer
Dim i As Integer
Dim j As Integer
Dim count As Integer
Dim datasheet As Variant
Dim Test As Variant
Dim RepType As Integer
Dim Desc As String
Dim StartDate1 As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Dim EndDate As Date
Dim StartDate As Date

Dim LocC As String
Dim LocL As String
Dim Par1 As Date
Dim Par2 As Date
Dim TeamNo As String
Dim strSql As String
Dim strDateStart As Date
Dim strEndStart As Date

' Stops warnings from appearing
DoCmd.SetWarnings False

strDateStart = [Forms]![Test]![cmbStartDate]
'
####################################################################################################################################
Set AppExcel = CreateObject("excel.application")
AppExcel.Visible = True
' Opens Excel template

'Selects Specialty
Select Case [Forms]![Test]![lstSpecialty]
Case "DNU"
AppExcel.Workbooks.Open "C:\Documents and Settings\msundhu\Desktop\Copy of DNU Activity_Revised.xls", , True
Case Else
MsgBox "No Valid Specialty selected"
Exit Sub
End Select
################################################################################################################
'Defines variable to queries records
'Extract all Referrals
AppExcel.StatusBar = "Running Referrals"

Select Case [Forms]![Test]![lstSpecialty]
Case "DNU"

AppExcel.StatusBar = "Running Referrals"

'runs the querie and puts the data into seperate table
strSql = "SELECT dbo_vwReferrals.Service, Format([RefrlDate],""yyyymm"") AS [Date], Count(dbo_vwReferrals.REFRL_REFNO) AS CountOfREFRL_REFNO INTO tblDNUReferals " & vbCrLf & _
"FROM dbo_vwReferrals " & vbCrLf & _
"WHERE (((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwReferrals.StatusID) Not In (""R"",""C""))) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([RefrlDate],""yyyymm"");"
DoCmd.RunSQL strSql

'selects all the data from the newly formed table
Set LOCReport = CurrentDb.OpenRecordset("SELECT tblDNUReferals.Service, tblDNUReferals.Date, tblDNUReferals.CountOfREFRL_REFNO FROM tblDNUReferals")

'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run(LOCReport, datasheet, RepType)


AppExcel.StatusBar = "Running Community First Appointments"
strSql = "SELECT dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO, IIf([schduledate]=[icntDate],""First"",""F/Up"") AS [Appt Type] INTO tblCommNew " & vbCrLf & _
"FROM dbo_vwReferrals INNER JOIN dbo_vwSchedules ON dbo_vwReferrals.REFRL_REFNO = dbo_vwSchedules.REFRL_REFNO " & vbCrLf & _
"WHERE (((dbo_vwReferrals.ServiceID) Like ""dnu"") AND ((dbo_vwSchedules.Shared) Is Null) AND ((dbo_vwSchedules.SchdlTypeID) Like ""c*"") AND ((dbo_vwSchedules.StatusID) Like ""f"")) " & vbCrLf & _
"GROUP BY dbo_vwReferrals.Service, Format([SchduleDate],""yyyymm""), IIf([schduledate]=[icntDate],""First"",""F/Up"") " & vbCrLf & _
"HAVING (((IIf([schduledate]=[icntDate],""First"",""F/Up""))=""First""));"
DoCmd.RunSQL strSql

Set LOCReport = CurrentDb.OpenRecordset("SELECT tblCommNew.Service, tblCommNew.Date, tblCommNew.CountOfSCHDL_REFNO FROM tblCommNew")

'selects named excel worksheett
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run2(LOCReport, datasheet, RepType)

End Select


DoCmd.SetWarnings True

AppExcel.StatusBar = "Run has finished"
MsgBox "Run has finished"
AppExcel.StatusBar = False
End Sub
####################################################################################################################################
Private Sub RunAQuery(strQueryName As String)
' Input : strQueryName Name of saved query to run
Dim db As Database
Dim qry As QueryDef
Set db = CurrentDb()
Set qry = db.OpenQuery(strQueryName)
DoCmd.SetWarnings True
qry.Execute
DoCmd.SetWarnings True
qry.Close
db.Close
DoEvents
DBEngine.Idle
End Sub
####################################################################################################################################
Public Sub Report_Run(LOCReport As Recordset, datasheet As Variant, RepType As Integer)
Dim AppExcel As Object
Dim CurrentPG As String
Dim CurrentSheet As Variant
Dim SPos As Integer
Dim rpos As Integer
Dim cpos As Integer
Dim overeight As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim y As Integer
Dim count As Integer
Dim Test As Variant
Dim Test2 As Variant
Dim Test3 As Variant

Dim StartDate As Date
Dim EndDate As Date
Dim NewDate As Date
Dim SumTotal As Single
Dim PG As String

' Start position of report data
rpos = 7
cpos = 2


' For 12 month reports
If RepType = 1 Then

End If

' Sets read start to begining of record
LOCReport.MoveFirst

' Counts number of fields in record
j = LOCReport.Fields.count


Select Case [Forms]![Test]![lstSpecialty]
Case "DNU"
k = 37

End Select

Test2 = LOCReport.Fields(1).Name
Test3 = LOCReport.Fields(2).Name

' Reads data from query and transfers it to spreadsheet
While Not LOCReport.EOF
' allocating reference cells for month and count
For y = 3 To 14

'field0=service, field1=date, field2=countofrefno from dnureferals table
'allocated refernce cells for values..y=coulmn and '7' & '8'= rows

datasheet.Cells(1, y).Value = LOCReport.Fields(1)
datasheet.Cells(2, y).Value = LOCReport.Fields(2)
'datasheet.Cells(8, y).Interior.ColorIndex = k
LOCReport.MoveNext
Next y

Wend
LOCReport.Close
End Sub
####################################################################################################################################
Public Sub Report_Run2(LOCReport As Recordset, datasheet As Variant, RepType As Integer)
Dim AppExcel As Object
Dim CurrentPG As String
Dim CurrentSheet As Variant
Dim SPos As Integer
Dim rpos As Integer
Dim cpos As Integer
Dim overeight As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim y As Integer
Dim count As Integer
Dim Test As Variant
Dim Test2 As Variant
Dim Test3 As Variant
Dim StartDate As Date
Dim EndDate As Date
Dim NewDate As Date
Dim SumTotal As Single
Dim PG As String

' Start position of report data
rpos = 7
cpos = 2
' For 12 month reports
If RepType = 2 Then

End If

' Sets read start to begining of record
LOCReport.MoveFirst

' Counts number of fields in record
j = LOCReport.Fields.count

Select Case [Forms]![Test]![lstSpecialty]
Case "DNU"
k = 37

End Select

Test2 = LOCReport.Fields(1).Name
Test3 = LOCReport.Fields(2).Name

' Reads data from query and transfers it to spreadsheet
While Not LOCReport.EOF
' allocating reference cells for month and count
For y = 3 To 14

'field0=service, field1=date, field2=countofrefno from dnureferals table
'allocated refernce cells for values..y=coulmn and '7' & '8'= rows
datasheet.Cells(3, y).Value = LOCReport.Fields(1)
datasheet.Cells(4, y).Value = LOCReport.Fields(2)
'datasheet.Cells(8, y).Interior.ColorIndex = k
'
LOCReport.MoveNext
Next y

Wend
LOCReport.Close

End Sub

 

[mattaus]

Hi theres no need to post a solution..i simply inserted txt fields in teh form as start and end dates..i then refernce dthe queries to look at the form before each query runs

i.e. Between [forms]![Test]![cmbStartDate] And [forms]![Test]![cmbendDate] under the field of 'referldate'.

thank you for your time-im very grateful

 

[wiklendt]

glad you sorted it out. don't forget to post back if you have any more questions.

 

[mattaus]

Thanks i will do )