retrieving data between 2 dates 1

[Falroc]

First of all let me tell you I am a complete noob when it comes to VBA in access 2007. I have attached a copy of the module for part of a report selector form I created. Everything works until I try to get all records between two dates thru user inputs. I made two unbound blanks in the report selector form named txtstartdate1 and txtenddate1. I want to use the referal date field to get the between dates. So far no luck I know it should be a simple thing. below is the module I am tr'Public Sub inbetween()
' Dim strIntakeBilling As String 'Name of report to open.
' Dim strPurchaseDate As String 'Name of your dateField.
' Dim strWhere As String 'Where condition for OpenReport.
' Const conDateFormat = "\#mm\/dd\/yyyy\#"

' strReport = "rptSales"
' strField = "SaleDate"

' If IsNull(Me.txtStartDate) Then
' If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
' strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
' End If
' Else
' If IsNull(Me.txtEndDate) Then 'Start date, but no End.
' strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
' Else 'Both start and end dates.
' strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
' & " And " & Format(Me.txtEndDate, conDateFormat)
' End If
' End If


Below is the module I want to modify. Any help getting it to work would be greatly appreciated.
Private Sub cmdIntake_Click()
Dim strCountyName As String
Dim strAgencyStatus As String
Dim strReferalSource As String
Dim StrServices As String
Dim StrPOStatus As String
Dim strFilter As String
Dim strStaff As String

' HAVE the report auto open for user
If SysCmd(acSysCmdGetObjectState, acReport, "rptIntakeBilling") <> acObjStateOpen Then
DoCmd.OpenReport "rptIntakeBilling", acViewPreview
End If
' Build criteria string for CountyName field
If IsNull(Me.cboCountyName.Value) Then
strCountyName = "Like '*'"
Else
strCountyName = "='" & Me.cboCountyName.Value & "'"
End If
' Build criteria string for AgencyStatus field
If IsNull(Me.cboAgencyStatus.Value) Then
strAgencyStatus = "Like '*'"
Else
strAgencyStatus = "='" & Me.cboAgencyStatus.Value & "'"
End If
' Build criteria string for ReferalSource field
If IsNull(Me.cboReferalSource.Value) Then
strReferalSource = "Like '*'"
Else
strReferalSource = "='" & Me.cboReferalSource.Value & "'"
End If
' Build criteria string for Services field
If IsNull(Me.cboServices.Value) Then
StrServices = "Like '*'"
Else
StrServices = "='" & Me.cboServices.Value & "'"
End If
' Build criteria string for POStatus field
If IsNull(Me.cboPOStatus.Value) Then
StrPOStatus = "Like '*'"
Else
StrPOStatus = "='" & Me.cboPOStatus.Value & "'"
End If
' Build criteria string for POStaff field
If IsNull(Me.cboStaff.Value) Then
strStaff = "Like '*'"
Else
strStaff = "='" & Me.cboStaff.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[CountyName] " & strCountyName & " AND [AgencyStatus] " & strAgencyStatus & " AND [RefCode] " & strReferalSource & " AND [Service] " & StrServices & " AND [POStatus] " & StrPOStatus & " AND [Staff Initials] " & strStaff
' Apply the filter and switch it on
With Reports![rptIntakeBilling]
.Filter = strFilter
.FilterOn = True
'.txtIntakeReportHeader.Value = _
'"INTAKE REPORT: " & vbCrLf & "Referal Source: " & Me.cboReferalSource.Value _
'& vbCrLf & "County: " & Me.cboCountyName.Value _
'& vbCrLf & "Staff: " & Me.cboStaff.Value
End With
End Sub

 

[SkipVought]

Hi,

"VBA in access 2007"

Forum705

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

First of all DATES are NUMBERS, like today is 39731.59414 which is oct 10 2008 at 14:15.

Stuff you enter in a text box is...

TEXT.

Even if it looks like a date, it's just a string of characters.

strWhere = strField & " <= #" & Me.txtEndDate & "#"   'txtEndDate is already a STRING

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Falroc]

so should it look more like this? but how does it know what field to use.

If Me.txtStartDate <> "" Then
sWhere = sWhere & "([Date Used]) BETWEEN (#" & Me.txtStartDate & "#) AND " & _
"(#" & Me.txtEndDate & "#) AND

 

[SkipVought]

Frankly I would advise against comparing date STRINGS.

Try comparing the STRINGS...

Dim str1 As String, str2 As String
str1 = "10/10/2008"
str2 = "10/20/2007"
If str2 < str1 Then
  MsgBox str2 & " is less than " & str1
Else
  MsgBox "guess what! 10/10 comes before 10/20!"
End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Sorry, I just realized that you are converting strings to dates.

If Me.txtStartDate <> "" AND Me.txtEndDate <> ""  Then
  sWhere = sWhere & "([Date Used]) BETWEEN (#" & Me.txtStartDate & "#) AND " & _
    "(#" & Me.txtEndDate & "#) AND ..."

should work if [Date Used] is one of your date fields.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Falroc]

Skip,

I guess I need to put in the table where the Purchase Date Field is. Can you show me the syntax to tell what table and field to use? The problem is that someone else wrote all the VBA and I am trying to reverse engingeer the code.

By the way thank you for all your help. I was feeling lost. and at least I have some direction to go now.


Falroc

 

[SkipVought]

"I guess I need to put in the table where the Purchase Date Field is."

put WHAT in the table?

"Can you show me the syntax to tell what table and field to use?"

How would anyone know, unless they knew the database & table structure AND the business process and business requirements?

"The problem is that someone else wrote all the VBA and I am trying to reverse engingeer the code."
You probably need to hire a professional.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Falroc]

That is what I would like to do. Hire a professional that is but that isn't one of my choices. Thanks for all your help. I will try and figure it out on my own.

 

[SkipVought]

If you submit specific questions in one of the MS Access forums, you'll get lots of help for MS Access.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}