Greetings from the Grim North

[Johnny C]

Hi
Just found this forum and expect to make good use of it.
I've some experience of Access, I've had fun with vba subforms and suchlike but in an old version of Access (2000) so if I need to use vba I'll be dipping in here for tips.

I've more experience with Excel, have coded vba extensively there (last project was 800 lines of code) so I've some understanding of vba generally.

What's giving me grief at the moment is trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.

Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:

Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 CohortQ([InputDate]))

In the VBA, InputDate is defined as a date

Function CohortQ(InputDate As Date) As Integer
If InputDate = 0 Then
    CohortQ = 0
    Exit Function
End If
If Year(InputDate) < 1990 Or Year(InputDate) > 2020 Then
    CohortQ = 0
    Exit Function
End If
On Error Resume Next
CohortQ = Year(InputDate) * 10 + DatePart("q", InputDate)
If Err.Number <> 0 Then
    CohortQ = 0
    Exit Function
End If
End Function

But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows.
If I put the function into the query,

Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 Year([InputDate])*10+DatePart("q",[InputDate]))

it works.

Any ideas why?
Anyway. Hi!

The Grim North is the Grim North of the UK, where on the map there's no towns or anything, just a note saying 'Here Be Dragons'

 

[pr2-eugin]

Hello Johnny C, Welcome to AWF

This section is dedicated for introductions only. I have asked one of the Mod's to move this for you.

In this mean time, why do you have a preceding 0?

Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020,[COLOR=Red][B] 0 [/B][/COLOR]CohortQ([InputDate]))

Unless you have missed a comma. Data type mismatch occurs when you are trying to use incompatible types. In this case my guess id Null values with Dates. Try either using Nz() or Changing the function to get Variant Dates.

Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0, CohortQ(Nz([InputDate],0)))

Or

Function CohortQ(InputDate As [COLOR=Red][B]Variant[/B][/COLOR]) As Integer
    If [COLOR=Red][B]IsNull(InputDate) Or[/B][/COLOR] InputDate = 0 Then
        CohortQ = 0
        Exit Function
    End If
    
    If Year(InputDate) < 1990 Or Year(InputDate) > 2020 Then
        CohortQ = 0
        Exit Function
    End If
    
On Error Resume Next
    CohortQ = Year(InputDate) * 10 + DatePart("q", InputDate)
    If Err.Number <> 0 Then
        CohortQ = 0
        Exit Function
    End If
End Function

 

[Johnny C]

Cheers, could be nulls... I had to restrict the dates because they're dates students attended IT courses, some were down as taking place in 1753 and some as far in the future as 5271AD. So it wouldnt surprise me if some nulls are in there.

 

[pbaldy]

FYI, moved to queries forum.