Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Unanswered: Dim as date help?

    Hi,

    I have a report that the ON_OPEN event triggers some code to create a SQL statement to query for the report data. I've added some lines to it that I can't seem to nail... I've not used dates before with DIM and I've had no success, so... Here you go!

    The form field [Beginning Date Lookup] has no format associated.

    The [Contract Tracking Table].[DATE RECEIVED] field is a Date/Time field.

    Here is a trimmed down version of the code, limited to the parts that I am experiencing difficulty with:


    Private Sub Report_Open(Cancel As Integer)
    Dim strSQL As String
    Dim strWhere As String
    Dim dtdatestart As Date


    If [Forms]![Reporting - Contract]![Beginning Date Lookup] <> 0 Then
    dtdatestart = [Forms]![Reporting - Contract]![Beginning Date Lookup]
    Else
    dtdatestart =
    End If

    If Not dtdatestart = Null Then ' If the datestart lookup field is NOT blank then
    Select Case strWhere ' Select strWhere case
    Case Is = "" ' If strWhere is blank
    strWhere = "(([Contract Tracking Table].[DATE RECEIVED])>='" & dtdatestart & "')" ' This stands alone
    Case Else
    strWhere = strWhere & " AND (([Contract Tracking Table].[DATE RECEIVED])>='" & dtdatestart & "')"
    End Select
    Else
    End If

    If Not strWhere = "" Then ' If strWhere is NOT blank
    strWhere = "WHERE " & strWhere ' Add "WHERE to prefix
    End If

    strSQL = "SELECT * " & _
    " FROM [Contract Tracking Table] " & _
    strWhere

    Me.RecordSource = strSQL
    End Sub


    As always, any help would be appreciated.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Since the default value of a Date variable type is "12:00:00 AM", it will never be null or "blank". You can't assign null to a date.

    There's other ways to do this, but using the code you have already started,
    I would change it to this:

    Code:
    Dim vardatestart As Variant
    If IsDate([Forms]![Reporting - Contract]![Beginning Date Lookup]) Then
    vardatestart = [Forms]![Reporting - Contract]![Beginning Date Lookup] 
    Else
    vardatestart = ""
    End If
    If Isdate(vardatestart) Then ' If the datestart lookup field is NOT blank then
    Select Case strWhere ' Select strWhere case
    Case Is = "" ' If strWhere is blank
    strWhere = "(([Contract Tracking Table].[DATE RECEIVED])>=#" & CDate(vardatestart) & "#)" ' 
    ...
    Last edited by RedNeckGeek; 03-01-05 at 07:54.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Feb 2005
    Posts
    9
    Jimmyswinger originally posted this one for me and I'm still having problems. I've listed the revised code below. When I enter a beginning date lookup of '1/1/2005' it gives me a data type mismatch. '01012005' gives me a dump of everything regard of recipt date. I've tried playing around with the format of [Beginning Date Lookup] but have had no success. Can anyone provide some help?? Thanks.

    The form field [Beginning Date Lookup] has no format associated.

    The [Contract Tracking Table].[DATE RECEIVED] field is a Date/Time field.
    i.e. 1/1/2004

    Private Sub Report_Open(Cancel As Integer)
    Dim strSQL As String
    Dim strWhere As String
    Dim vardatestart As Variant

    If IsDate([Forms]![Reporting - Contract]![Beginning Date Lookup]) Then
    vardatestart = [Forms]![Reporting - Contract]![Beginning Date Lookup] ' Define DateStart lookup value
    Else
    vardatestart = ""
    End If

    If IsDate(vardatestart) Then ' If the datestart lookup field is date
    Select Case strWhere ' Select strWhere case
    Case Is = "" ' If strWhere is blank
    strWhere = "(([Contract Tracking Table].[DATE RECEIVED])>='" & CDate(vardatestart) & "')" ' This stands alone
    Case Else
    strWhere = strWhere & " AND (([Contract Tracking Table].[DATE RECEIVED])>='" & CDate(vardatestart) & "')"
    End Select
    Else
    End If

    If Not strWhere = "" Then ' If strWhere is NOT blank
    strWhere = "WHERE " & strWhere ' Add "WHERE to prefix
    End If

    strSQL = "SELECT * " & _
    " FROM [Contract Tracking Table] " & _
    strWhere

    Me.RecordSource = strSQL
    End Sub

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    To query an Access date/time field, use this format:
    strWhere = "(([Contract Tracking Table].[DATE RECEIVED])>=#" & CDate(vardatestart) & "#)"
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •