Date Ranges

[neeZy]

Hello,
I am very new to VBA and have a problem. I want to be able to pull an Access report only within a certain date range. I have a "DateFrom" and "DateTo" set up as text boxes in my form, but I don't know how to set them up as dates. The report is called REPORT_CARD_DATA, and so far, I've been able to create combo boxes that narrow down the information by TaxID and Names. I want to be able to narrow it down further by giving a date range (DATE_OF_EVENT). Here is my code for the combobox (this is the second one - narrows by name based on TaxID....the first combobox i just made in the form wizard...newbie in the strictest sense), but I'm clueless as far as setting up 2 textboxes that provide a range. Any help will be greatly appreciated.

Private Sub Form_Load()
 
  cboProvName.Enabled = False
  txtDateFrom.Enabled = False
  txtDateTo.Enabled = False
  txtDxFrom.Enabled = False
  txtDxTo.Enabled = False
 
 
End Sub
 
 
Public Sub cboProvName_Select()
  With Me![REPORT_CARD_DATA]
    .RowSource = "SELECT [last name], [first name], [middle name] " & _
                 "FROM REPORT_CARD_DATA " & _
                 "WHERE [TAXID] = cboTaxID.txt"
End Sub

[ADezii]

Hello,
I am very new to VBA and have a problem. I want to be able to pull an Access report only within a certain date range. I have a "DateFrom" and "DateTo" set up as text boxes in my form, but I don't know how to set them up as dates. The report is called REPORT_CARD_DATA, and so far, I've been able to create combo boxes that narrow down the information by TaxID and Names. I want to be able to narrow it down further by giving a date range (DATE_OF_EVENT). Here is my code for the combobox (this is the second one - narrows by name based on TaxID....the first combobox i just made in the form wizard...newbie in the strictest sense), but I'm clueless as far as setting up 2 textboxes that provide a range. Any help will be greatly appreciated.

Private Sub Form_Load()

cboProvName.Enabled = False
txtDateFrom.Enabled = False
txtDateTo.Enabled = False
txtDxFrom.Enabled = False
txtDxTo.Enabled = False


End Sub


Public Sub cboProvName_Select()
With Me![REPORT_CARD_DATA]
.RowSource = "SELECT [last name], [first name], [middle name] " & _
"FROM REPORT_CARD_DATA " & _
"WHERE [TAXID] = cboTaxID.txt"
End Sub

I've written sample code for you to point you in the right direction. Any other questions, feel free to ask.

Dim MySQL As String
 
If IsDate(Me![txtDateFrom]) And IsDate(Me![txtDateTo]) Then
 Me![REPORT_CARD_DATA].ColumnCount = 4
 Me![REPORT_CARD_DATA].BoundColumn = 1
 Me![REPORT_CARD_DATA].ColumnWidths = ";;;"     'Use Default Widths
  MySQL = "Select [last name], [first name], [middle name], [DATE_OF_EVENT] From REPORT_CARD_DATA  Where [DATE_OF_EVENT]"
  MySQL = MySQL " Between #" & Me![txtDateFrom] & "# And #" & Me![txtDateTo] & "#;"
    Me![REPORT_CARD_DATA].RowSource = MySQL
Else
  'do nothing
End If

[neeZy]

thank you very much!

I have a final question:

Once I have the form filled out (TaxID, Name, and Date Range), how do i generate a report based on my selection?

Let me explain further - REPORT_CARD_DATA is a table i have in Access that has information on how several individuals are performing (revenue generated, etc). Some people share the same tax id, while some people do not - that's why i had to add the "name" part to my form. After a tax id and name are selected, I also want to be able to pull up a date range to judge performance on.

Now, I would like to put a button on my form called "generate report" that spits out the information for that one person once pushed.

Any help will be appreciated.

[shiznaw]

I've written sample code for you to point you in the right direction. Any other questions, feel free to ask.

Dim MySQL As String
 
If IsDate(Me![txtDateFrom]) And IsDate(Me![txtDateTo]) Then
 Me![REPORT_CARD_DATA].ColumnCount = 4
 Me![REPORT_CARD_DATA].BoundColumn = 1
 Me![REPORT_CARD_DATA].ColumnWidths = ";;;"     'Use Default Widths
  MySQL = "Select [last name], [first name], [middle name], [DATE_OF_EVENT] From REPORT_CARD_DATA  Where [DATE_OF_EVENT]"
  MySQL = MySQL " Between #" & Me![txtDateFrom] & "# And #" & Me![txtDateTo] & "#;"
    Me![REPORT_CARD_DATA].RowSource = MySQL
Else
  'do nothing
End If

Would it be simplier if he were to simply create a query and set the dates of the query to parameters / fields on the form? or no?

[neeZy]

Would it be simplier if he were to simply create a query and set the dates of the query to parameters / fields on the form? or no?

That would definitely be more user friendly. It's just that I'm heavily reliant on the wizard, so the best way for me was to create a form using it. The last thing I want done is to have a button call up the specifics of a table I have in Access, but alas, no wizard for that x.x Think you can help me out?

Thanks for your time

[NeoPa]

Would it be simpler if he were to simply create a query and set the dates of the query to parameters / fields on the form? or no?

Pros: Less coding.
Cons: Query not very portable.
Query not reusable with other forms.
At the end of the day you make your own choices suited to your own situation.

[ADezii]

Would it be simplier if he were to simply create a query and set the dates of the query to parameters / fields on the form? or no?

Yes it would, and thanks for pointing it out to me. I'm afraid that I have gotten Post Response mixed up - happens as you get older!