Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi, The below is not working as expected. I am trying to isolate all entries with todays date, for later parsing for specific details.

    Dim RS as Recordset
    Dim SQLDate As Date
    Dim SQL1 as String

    SQLDate = Format(Now, "dd/mm/yyyy")

    SQL1 = "Select * from [DataStore] where Format([DataStore].[Entered]," & Chr(34) & "dd/mm/yyyy" & Chr(34) & ") = #" & SQLDate & "#"

    Set RS = CurrentDb.OpenRecordset(SQL1)

    Any assistance would be appreciated.

  2. #2
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Ooops sorry forgot to mention, it only retrieves 1 Entry (seemingly at random, However it stays with that entry until the data is updated then changes to another.)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    When you open a recordset, it will be positioned at the first record that meets the criteria.
    To inspect the other records, you have to loop through the records:

    Code:
    ...
    Set RS = CurrentDb.OpenRecordset(SQL1)
    Do While Not RS.EOF
     ' do stuff with the current record here
     ...
     ...
     ' move to the next record
     RS.MoveNext
    Loop

  4. #4
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks Hans

    Umm, I'm trying to create a "virtual table" to parse later. I've got it working in other projects but it seems to not be working here as it always returns "1" Entries, when I reference the RS.recordcount property.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts
    OK thanks. I'll revert to that method, I was out smarting myself as usual

    Graliv

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The RecordCount property will usually return 1 initially. To get the actual record count, you have to jump to the end of the recordset:

    RS.MoveLast
    MsgBox "The recordset returned " & RS.RecordCount & " records"


  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    IF you are using dates in SQL they should always be in US Notation of mm/dd/yyyy

    So you query really ought to be

    Code:
    SQLDate = Format(Now, "mm/dd/yyyy")
    SQL1 = "Select * from [DataStore] where [Entered] = #" & SQLDate & "#"
    Set RS = CurrentDb.OpenRecordset(SQL1)
    
    OR You Could Use The Date Function in The SQL
    
    SQL1 = "Select * from [DataStore] where [Entered] = Date()"
    Set RS = CurrentDb.OpenRecordset(SQL1)

    However, you can only process these records by looping through the one at a time
    as Hans said.

    You can then find the number of Record by going to the last

    Alternatively you could find this Number in VBA script by using DCount Function

    Code:
    lngRecs=DCOUNT("*","DataStore","[Entered]=#" & SQLDate & "#")


    But for real help I think you'd need to explain what you are after doing in more detail


    Andrew

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Why are you making the SQL statement so convoluted? It can be this:

    SQL1 = "Select * from [DataStore] where [Entered]=Date()"
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by MarkLiquorman View Post
    Why are you making the SQL statement so convoluted? It can be this:

    SQL1 = "Select * from [DataStore] where [Entered]=Date()"
    Yes that's what I put in the previous post Mark.
    Andrew

Posting Permissions

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