Dcount / dateadd problems

[indesisiv]

Hi all,

I am trying to get my database to do an archive ... the actual archive bit of the database works fine so no probs there. The problems begins when i am trying to tell the user what is happening.

i have this code.

Dim DateForArchive As Date
          Dim NumberForArchive As Integer
    
            
        DateForArchive = DateAdd("ww", -10, Date)
        NumberForArchive = DCount("[VenueID]", "[tblVenue]", "[Venuedate] < #" & DateForArchive & "#")
        If NumberForArchive > 0 Then
            If MsgBox("There are " & NumberForArchive & " records to be archived!" & vbCrLf & vbCrLf & "Would you like to archive them now?", vbYesNo) = vbYes Then
'Archive

the date add function seems to work fine and bring up the correct date. But when i use that date in the dcount it brings up the wrong number of records.
Does anyone have any idea if there is something obviously wrong with this.

Thanks in advance
Steve

 

[Rich]

Try wrapping the CDate function around the date or use the Format function to change to the US standard

 

[Pat Hartman]

You don't need the #'s when the variable is defined as a date. You only need them when the variable is a string.

If you have records where VenueID is null, they won't be counted by the DCount(). You would need to replace VenueID with * to also count null value records.

 

[indesisiv]

Thanks

Thanks Rich & Pat. Works fine now.

Also VenueID is the PK so it will always have data in it.

Cheers
Steve