Finding all records before todays date - 5 Days

[Calvin Dent]

Hey Guys,

Currently I'm using a code to find all records before a certain date.

I'm using
If LogDate < Date -5 Then

The problem with this is, Its only filtering by Day not month or year.

For example, It'll show all records like this
Shows 01/02/2011 - 07/02/2011
But not 08/02/2011 - 28/02/2011
Shows 01/03/2011 - 07/03/2011
But not 08/03/2011 - 31/03/2011

Any way I can set it to include the month/year in the date calculation so it excludes records from only the last five days?

Thanks in Advance.

[TheSmileyCoder]

It sounds a bit odd to me. Is logDate a date/time variable? Can you include any code where you dim and/or manipulate it?

[Calvin Dent]

The code I'm using is this.

Public Function JobsDateFive(LogDate As String, ComDate As String, StatBox As String, TypeBox As String) As String
If LogDate < Date - 5 Then
If StatBox <> "Completed" Then
If StatBox <> "Cancelled" Then
If StatBox <> "Invoiced" Then
If StatBox <> "Resolved" Then
If StatBox <> "Attended" Then
If ((TypeBox = "CSTM01") Or (TypeBox = "CSTM02")) Then
JobsDateFive = "1"
Else
JobsDateFive = "0"
End If
End If
End If
End If
End If
End If
End If
End Function
 

[TheSmileyCoder]

Why are you passing your LogDate as a string? Its like comparing apples to elephants. You might get lucky and Access will guess the correct conversion from date->string->Date but it is bound to give you grief, and is most likely the cause of your current dilemma.

And you could simplify your code by including them in the same if statements, instead of nesting them.:

If StatBox <> "Completed" AND StatBox <> "Cancelled" AND StatBox <> "Invoiced" AND StatBox <> "Resolved" AND StatBox <> "Attended" Then

[Calvin Dent]

Hey Smiley,

Thanks for that... Was having a hard time but that totally fixed it. I've ordered a book on VBA as my new job seems to be requiring a lot of VBA coding.

Once again, Thanks

[TheSmileyCoder]

You are most welcome. Access can be quite forgiving in alot of cases, when it comes to using the correct variable type, but in my experience it will almost always cause you grief not to declare the variable type and think about using the correct variable type.

[Calvin Dent]

Okay, Ill keep that in mind!
Is there any Books/Documentation you recommend reading? I studied VBA at college but this was quite a long time ago!

[TheSmileyCoder]

The only VBA book I've read was "VBA for dummies", and then I've had alot of help from the guys and girls on this forum, as well as painstaking amounts of trial and error.

[Calvin Dent]

Well, I guess i'm on the right tracks.
I've just ordered Access VBA 2007 for Dummies, Think That'll help?

[TheSmileyCoder]

I found it to be a very good start. The rest you can learn as you go along. Ill be honest and admit though, that looking back at my first coding projects 2½ year, I would do alot differently now, but thats experience, which you can't read your way into.