Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    recordsets -find first (Access 97 )

    When I run the code below it will not find a date that I know exists in the recordset.
    The field DateConfirmed contains null when watched in debug. after stopping the code half way through.
    However if I put the code in to print.debug afield of a certain record then it sees the date.
    What is wrong with the code?

    Private Sub PencilDate_BeforeUpdate(Cancel As Integer)
    >
    > Dim checkdateavail As Date
    > Dim dbs As Database
    > Dim rstWedDates As Recordset
    > Dim strcriteria As String
    >
    >
    >
    >
    > If IsNull(Me![PencilDate]) Then
    > Exit Sub
    > End If
    >
    > checkdateavail = Me![PencilDate] 'MakeUSDate(Me![PencilDate])
    >
    > Set dbs = CurrentDb
    > strcriteria = "[DateConfirmed] = #" & checkdateavail & "#"
    > Set rstWedDates = dbs.OpenRecordset("First Enquiry Table",
    > dbOpenSnapshot)
    > With rstWedDates
    > ' Populate recordset.
    > '.MoveLast
    > '.MoveFirst
    > ' .FindFirst "[WeddingID] = 3"
    > ' strNumber = InputBox("Please enter a number less
    > than " & rstWedDates.RecordCount & ".")
    > ' Move specified number of records.
    >
    > 'For intI = 1 To strNumber
    > ' rstWedDates.MoveNext
    > ' Next intI
    > ' Debug.Print rstWedDates!DateConfirmed
    > 'Debug.Print rstWedDates!WeddingID
    >
    > .FindFirst strcriteria
    > If .NoMatch Then
    > MsgBox "No records found with the date " & checkdateavail & "."
    > Else
    > MsgBox "This date has gone to the following wedding " &
    > rstWedDates!WeddingID
    > End If
    > End With
    > rstWedDates.Close
    > dbs.Close
    > End Sub


    All this is supposed to do is search the database for a date that has been input in one field but occurs in another. It occurs on the before update event.

    Pllllleeeeeaaaassseee help - it makes no sense why this does not work to me as it finds other info in other fields.

    Maybe I am just thick!!

    Thanks for your help
    David

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: recordsets -find first (Access 97 )

    Have you tried this:


    strcriteria = "format([DateConfirmed], "short date") = " & checkdateavail


    If you have true date/time fields, you may be getting a mismatch unless you limit the field to the date only.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Feb 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordsets -find first (Access 97 )

    Have tried the code but the compiler complained at "short" of the "Short Date" syntax so I will try ddmmyy instead. Will post the result if successful
    Thanks
    kind regards
    David

Posting Permissions

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