Option Group VBA setting date range for query

[BR549]

Hello, I have created a form with Option buttons
Name: Capture.JPG  Views: 13  Size: 30.3 KB  ID: 18330" class="thumbnail" style="float:CONFIG" /> and have written the initial code for the buttons and tested with a msgbox to ensure all the buttons worked. Now i want to set up the each button to run a query which would pull the date range from the vba to use in the query. The query is setup to pull certain suppliers based a data range like 1/1/14 to 1/31/14. So the query for button one would pull the data based on January. The VBA sets up the year (so it is dependent on the current year so the code does not have to be updated each year) and puts it into 2 values. Now I need to be able to call the query and use the date range from the VBA.

Code:
Private Sub JanMissed()


Dim JanDate As Date
Dim DDate As Date
Dim CYear As Date
Dim MMonth As String
Dim sfile As String
Dim UserNameWindows As String
Dim BMonth As Date
Dim EMonth As Date
Dim objXLApp As Object
Dim XLApp As Object
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef


Set dbs = CurrentDb
Set dbf = dbs.QueryDefs("agry CT Missed ATT")


UserNameWindows = VBA.Environ("USERNAME")
DDate = Date
CYear = Year(DDate)
MMonth = "01 Jan"
BMonth = "1/1/" & CYear
EMonth = "1/31" & CYear
sfile = "C:\Users\" & UserNameWindows & "\Desktop\Export\" & MMonth & " Missed CT's.xlsx"


MsgBox ("I will now look for Missed Cycle Times's for January" & vbNewLine & vbNewLine & Time)
qdf.Parameters("Cx End Date").Value = BMonth


DoCmd.OpenQuery "aqry CT Missed ATT"
'DoCmd.OpenQuery "aqry CT Missed Sprint"
'DoCmd.OpenQuery "aqry CT Missed Sprint 65MHz"
'DoCmd.OpenQuery "aqry CT Missed TMO"
'DoCmd.OpenQuery "aqry CT Missed VZW"


' If DCount("*", "tbl CT Missed") <> 0 Then
' DoCmd.TransferSpreadsheet acExport, 10, "tbl CT Missed", sfile, True, "Missed CTs"
'End If


MsgBox (MMonth & " Missed CT's.xlsx is in the Export folder on your desktop." & _
vbNewLine & sfile & vbNewLine & Time)


Set XLApp = CreateObject("Excel.Application")
With XLApp
.Application.Visible = True
.UserControl = True
.Workbooks.Open "C:\Users\" & UserNameFunction & "\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL .xlsb", True
.Workbooks.Open sfile, False
.Run "PERSONAL.xlsb!Missed_CTs"
End With


Set XLApp = Nothing






End Sub

[June7]

Are these toggle buttons within an Option Group frame? Or are they really just individual command buttons?

The query has dynamic parameters? I don't use dynamic parameters in query. I pass filter criteria to form or report when it opens. But I don't need to regularly export to Excel.

I think options are:

1. set value of textbox on form with the date parameters and query refers to those textboxes for input

2. set value of TempVars and query refers to those for input

3. the query calls a custom function that passes the parameters into query

[BR549]

These are toggle buttons setup using the add option group wizard. The references to dynamic parameters is something I was trying. Didn't work, junk code at the moment. The query currently uses another form where the user inputs the dates. We have some people who use this who for some reason have trouble with it. So, I am creating a kiss (keep it simple silly) form.

[June7]

What does "didn't work" mean - error message, wrong results, nothing?

If you want to open a dynamically filtered query object and export the query (as opposed to exporting report) then only options I see are:

1. dynamic parameters

2. modify the query with VBA using QueryDefs

[BR549]

The previous form worked flawlessly, but the users put words, dates backwards, just bad input on their part. It only worked when they used the calendar option. But people can be silly, so I am redesigning the input. I have been reading and searching for days, and I just cannot come across the right combination to make it work. All the other code works. The query needs the date input to run the update query(s). I have another button to export the the completed table to an email, and send to contacts in the table.

[June7]

What do you not understand about the options in post 2, especially number 1?

[BR549]

Sorry to say, not enough to make it work on my own. I tried a couple of times after reading post after post, but just could not make it work. I think my blood pressure is suffering because of this.

[June7]

The query parameter would refer to textboxes on form:

BETWEEN Nz(Forms!formname!tbxStart, #1/1/1900#) AND Nz(Forms!formname!tbxEnd, #12/31/2900#)

Then user input or code needs to set the values into the textboxes.

If you want to provide db for analysis, follow instructions at bottom of my post.

[BR549]

I have that same Between statement in the old form. But, I cannot figure out how to take the input from the vba, the bbmonth I have for example, and place that into the query where the between statement is.

[June7]

User has to type/select date into textbox and can validate user input with calendar picker or input mask or validation rule property - not possible with popup prompt by query.

Or code sets value into textbox:

Me.tbxStart = some date value constructed in code

Me.tbxEnd = some date value constructed in code