Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2005
    Posts
    121

    Unanswered: Need help with DCount and Dates

    All,
    I have a need to count number of records in a table from a form to check to make sure that date does not exist before it imports that dates records from another table. I was going to set it up -

    If DCount("[TRAILER NUMBER]","History1","[DATEADD]>=forms![History Download Criteria]!StartDate And [DATEADD]<=forms![History Download Criteria]!EndDate") =0 then
    STOP
    else
    GO
    End if

    I can see that the date fields in the criteria need to be boxed somehow with the # sign, however I have tried it several ways to no avail. Any suggestions?

    Thanks,
    Bob

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    First you should not use DATEADD as the name of an object, a column or a variable as it is a reserved word and the name of a built-in function of Access. This is looking for troubles.

    Second, as you wrote, you have to enclose every date value between pound (or sharp: #) characters, so it should be something like "#" & DateAdded & "# >= #" & Forms!... & "#"

    Third the only date format that SQL Access recognises is: dd/dd/yyyy (month/day/year), whatever the regional setting can be in Windows.

    Fourth keep in mind that a date is stored as a Date/Time type, so there is a time part that goes along even if you don't see it. If a date was stored using the Now function it was store with a Date part and a Time part. This can cause problems when you try to perform a comparison between that stored date and another in which that Date part is identical but the Time part is not. If you do not provide the Time part it is implicitly set to: 0:00:00.

    Example:
    Code:
    Function TestDateTime()
    
        Dim Mydate1 As Date
        Dim Mydate2 As Date
        
        Mydate1 = Now
        Debug.Print Mydate1             ' yields: 29/03/2010 23:25:24 (European settings)
        Debug.Print CSng(Mydate1)       ' yields: 40266,98
        Mydate2 = #3/29/2010#
        Debug.Print Mydate2             ' yields: 29/03/2010 (European settings)
        Debug.Print CSng(Mydate2)       ' yields: 40266 (same as 40266,00)
        Debug.Print Mydate1 = Mydate2   ' yields: False
        
    End Function
    Does this help?
    Have a nice day!

  3. #3
    Join Date
    Dec 2005
    Posts
    121
    I took your suggestion and changed the date field to DATE.

    Yes, the data stored in the date field does have a time factor. I have a form that has 2 fields, StartDate and EndDate. StartDate once a date is entered displays example (03/29/2010 12:00:01 AM) and EndDate once a date is entered displays example (03/29/2010 11:59:59 PM).

    So, I tried to set up the DCount line as follows -

    If DCount("[TRAILER NUMBER]", "History1", "#" & Date & "# >= #" & Forms![History Download Criteria]!StartDate & "#" And "#" & Date & "# <= #" & Forms![History Download Criteria]!EndDate & "#") Then

    I get no error message after typeing the code, but when I try to run it, it is telling me there is a Run type error 13, type mismatch. Date in the History1 table and StartDate and EndDate are all set as General Date under format. Any idea why I am getting that error?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The # should only be around the value:

    General: DLookup Usage Samples

    and Date is also a bad name for a field, as there's a Date() function:

    List of reserved words in Access 2000
    Paul

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you want to compare the Date part of two Date/Time type elements, you can use a formating or casting function and perform the comparison on the result. In my example:
    Code:
    Debug.print Format(MyDate1, "dd/mm/yyyy") = Format(MyDate2, "dd/mm/yyyy") ' --> True
    or:
    Code:
    Debug.print Clng(MyDate1) = Clng(MyDate2) ' --> True
    When looking for a precise day, replace:
    Code:
    If MyDate = #12/30/2009# Then ...
    with:
    Code:
    If MyDate >= #12/30/2009# And MyDate < #12/31/2009# Then ...
    Have a nice day!

  6. #6
    Join Date
    Jun 2014
    Posts
    3

    Please help me too

    Please help Me too,
    error said mismatch

    my Table contain:
    TanggalID with autonumber
    DoNumber with a formula to generate rendom number
    Tanggal_Material with date
    SupplierID with combobox that source to a quarry for shorting
    Code:
    Private Sub DOnumber_GotFocus()
    Dim Cancel As Integer
    Dim Answer As Variant
    Dim Tnggl As String
    Dim Spplr As Integer
    Dim vllue As Integer
    
    
    vllue = Me.SupplierID.Value
    Tnggl = "[Tanggal_Material] = '" & Format(Me.Tanggal_Material.Value, "mm/dd/yyyy") & "'"
    Spplr = "[SupllierID] ='" & vllue & "'"
    
    
     Answer = DLookup("*", "Tmaterial_date", Spplr And Tnggl)
      If Not IsNull(Answer) Then
      MsgBox "Data Duplikat" & vbCrLf & "Pastikan Data tidak ganda.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
      Cancel = True
     Me.Undo
    Else:
    -->here is my random DoNumber generate
    End If
    I don't know what went wrong please help Me...

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As [Tanggal_Material] is a date, try:
    Code:
    Tnggl = "[Tanggal_Material] = #" & Format(Me.Tanggal_Material.Value, "mm/dd/yyyy") & "#"
    Have a nice day!

  8. #8
    Join Date
    Jun 2014
    Posts
    3
    Thank you for fast respond Sinndho,

    i change Tnggl to As Date, and change the code as you have told.
    but still the same, error still said mismatch and point to
    Code:
    Tnggl = "[Tanggal_Material] = #" & Format(Me.Tanggal_Material.Value, "mm/dd/yyyy") & "#"
    i change the code too look like this, and no mismatch, but popup warning always show without any duplicate i create in the data

    Code:
    Private Sub DOnumber_GotFocus()
    Dim Cancel As Integer
    Dim Answer As Variant
    Dim Tnggl As Date
    Dim Tnggl2 As Date
    Dim Tnggl3 As Date
    Dim Spplr As Integer
    Dim Spplr2 As Integer
    Dim Spplr3 As Integer
    
    Tnggl2 = Format([Tanggal_Material], " mm / dd / yyyy")
    Debug.Print Tnggl2
    Tnggl3 = Format(Me.Tanggal_Material.Value, "mm / dd / yyyy")
    
    Tnggl = Tnggl2 = Tnggl3
    Spplr2 = Me.SupplierID.Value
    Spplr3 = [SupplierID]
    
    Spplr = Spplr3 = Spplr3
    
    Answer = DLookup("'*'", "Tmaterial_date", Tnggl And Spplr)
    If Not IsNull(Answer) Then
    MsgBox "Data Duplikat" & vbCrLf & "Pastikan Data tidak ganda.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
    Cancel = True
    Me.Undo
    Else:
        Me.DOnumber = "SRMI" & Me.Tanggal_Material & "LOG" & "-" & GenRandomStr(5, True, True, False) & "-" & Format(Me.TanggalID, "00000000") & Me.SupplierID
    End If
    End Sub

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you dont say what tanngal_material is
    Access is pretty smart at sorting out dates
    but it does need tobe one of 3 formats
    ISO yyyy/mm/dd
    US mm/dd/yyyy
    RoW dd/mm/yyyy


    so your date format of " mm / dd / yyyy" is invalid
    instead Tnggl2 = Format([Tanggal_Material], "mm/dd/yyyy")
    ..however format always returns a string so ideally you should explciitly coerce the string into a date before assigning it to a date variable

    Tnggl2 = cdate(Format([Tanggal_Material], "mm/dd/yyyy"))

    but if [Tanggal_Material] is a control containing a column or is a column in its own right of type datetime then you don't need to convert it, Access already knows its a date and there is no need to convert
    FWIW to avoid any complications on dates in an Access application I always use ISO date yyyy/mm/dd... it avoids complications when code is used /red anywhere in the world... and the fact its an ISO standard has benefits too
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jun 2014
    Posts
    3
    Sorry not to list it all to well,

    I have a table named Tmaterial_date
    in it have
    TanggalID (primary key) with autonumber
    DoNumber Text
    Tanggal_Material Date/Time in short date
    MaterialId number
    SupplierID number

    and in the form i have
    a text box named Tanggal_Material for the date
    and a combo box named SupplierID link to a query for shorting the Suppliers list

    i want to create a warning if someone inserting new duplicate data, if the Tanggal_Material and the SupplierID are in a same date.

    please help me

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I would use (aircode):
    Code:
    Const c_SQL As String = "SELECT Count(*) FROM Tmaterial_date WHERE @C;"
    
    Dim rst As DAO.Recordset
    Dim strCriteria As String
    Dim strSQL As String
    
    strCriteria = "TanggalID = " & Me.SupplierID.Value & " AND Tanggal_Material = #" & Format(Me.Tanggal_Material.Value, "yyyy-mm-dd") & "#"
    strSQL = Replace(c_SQL, "@C", strCriteria)
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    If rst.Fields(0).Value > 0 Then
        ' Found: A row with the same SupplierID and the same Tanggal_Material already exists.
    Else
        ' No record found with the same SupplierID and the same Tanggal_Material.
    End If
    Have a nice day!

Posting Permissions

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