Date range problem Report only

[KLewisBPM]

I have a report that runs based on a query with the parameters "Between [start date] and [End Date]" dd/mm/yy format.

When I run it for 01/01/04 to 31/01/04 it works fine, When I run it for 01/02/04 to 22/02/04 it works fine but any date after 22/02/04 it doesn't work.

E.g. 01/02/04 to 23/02/04 fails, HOWEVER! if I run the Query on its own, all date ranges work!! The error message is:-

"The expression is typed incorrectly, or it is to complex to be evaluated........................" Std Access error msg.

Any Ideas?

Kind Regards

Kelley Lewis

 

[Frink]

Hallo,

I have had a problem with sorting by dates, which I think is due to Access sometimes treating dates as American, where I'm set up for UK dates.
ie. UK is DD/MM/YYYY, USA = MM/DD/YYYY
The way I get around it is to explicitly convert all dates iwhenever I need to sort or filter by them. It's a bit of a pain, but it seems to work.

Public Function strFormatDatestamp(ByRef varDate As Variant) As String
On Error GoTo lblErr
  strFormatDatestamp = "00000000"
  Select Case varType(varDate)
    Case vbEmpty
      Exit Function
    Case vbNull
      Exit Function
    Case vbInteger
    Case vbLong
    Case vbSingle
    Case vbDouble
    Case vbCurrency
    Case vbDate
    Case vbString
    Case vbObject
      Exit Function
    Case vbError
      Exit Function
    Case vbBoolean
      Exit Function
    Case vbVariant
    Case vbDataObject
      Exit Function
    Case vbDecimal
    Case vbByte
    Case vbArray
      Exit Function
  End Select
  If IsDate(varDate) Then strFormatDatestamp = Format$(CVDate(varDate), "yyyymmdd")
lblExit:
  Exit Function
lblErr:
  LogErr Err.Description, "mdlLibrary.strFormatDatestamp"
  strFormatDatestamp = "00000000"
  Resume lblExit
End Function

The code is still a bit work-in-progress, but will convert a date into something sortable.
Use it both sides of an expression:

"SELECT * FROM tblData WHERE strFormatDatestamp([datDate]) > """ & strFormatDatestamp(Me!txtFromDate) & """"

There's probably much better ways of doing this, but this works for me!

- Frink

 

[KLewisBPM]

Its seems strange that the query runs ok on its own and outputs the results i'd expect. But the report pointing to the same Query falls over? I have 20+ reports all running on date filters and they all work fine! Must be a glitch.

Kind Regards

Kelley Lewis

 

[KLewisBPM]

Frink,

No-one else seems to have any ideas why this error is occuring.

I wonder if you could explain to me where you enter the above code?

I am still confused why the Query runs and gives the correct output, but the report doesn't run?



Kind Regards

Kelley Lewis

 

[KLewisBPM]

I have just took the date range out of the report so it returns all records. however I now get a different error message!

'Divide by Zero'

I am hoping this it what is causing the original problem!

Kind Regards

Kelley Lewis

 

[Frink]

Hallo,

One thought about why the query runs and the report doesn't: the report uses it's own sorting and grouping, not the queries (as far as I can remember). Are you sure that the report is ok?
Also, sometimes Access likes #'s around dates. You could try that.

- Frink

PS. The code was just an axample of the theory. Assuming your date field is called datDate, in your query create a new hidden column:
OrderedDate: strFormatDatestamp([datDate])
Between strFormatDatestamp([start date]) And strFormatDatestamp([end date])
and remove the criteria from the existing [datDate] field

 

[KLewisBPM]

Frink,

I have discovered that a record entered into one of the tables that is queried had a 0 value in a field that would never ever be 0.

Once i deleted the record everything was fine!!!

It would have been better if the report with date range came up as a divide by zero error! then it would have been more meaningful.

Thanks for taking the time to respond!

Kind Regards

Kelley Lewis