Query help please

[brtny82]

P.S. I am so sorry for being a pain and thank you so much for your help

 

[plog]

No, its because the VBA function expects Level of Care to be numeric and you are passing it text. In the example data you gave me on which I built the function it was numeric.

 

[brtny82]

Sorry, I didn't realize it mattered. Can I change something so that it assigns it a number or the VBA recognizes it as text?

 

[plog]

Change this portion of code:

"[Level of Care]=" & in_Level & " AND

To this:

"[Level of Care]='" & in_Level & "' AND

That code is in 2 spots, so fix both. If you will look close the correct code has single quotes in it to handle in_Level like text now.

 

[brtny82]

Thank you! Now I get a compile error:
Compile error. in query expression 'get_ConsecutiveMonths([Discharging Provider Name],[Level of Care],[Type],[TrendingDate]'

I assume this is in my SQL? Is there something I need to change?

 

[brtny82]

Okay, so I fixed the compile error by unchecking "Microsoft Office 14.0 Access database engine object library" in my VBA:Tools:References but now all of the column executive months is returning a 1

 

[brtny82]

For example, in the attached picture, shouldn't the 2 I identified return a 2 for 2 consecutive months?

 

[plog]

Yes it should. I would really need to see the database to help with this though. Can you strip it down to the bare amount of information I need to have to diagnose this?

 

[brtny82]

Here you go

 

[plog]

You said your date field was called TrendingDate or something. It is not, its MM/YYYY you need to make the appropriate changes in the VBA for that.

 

[brtny82]

Here, I changed it all to TrendingDate but now I get a circular reference error

 

[brtny82]

Also, in an attempt to get it to read right, I have the VBA pulling from the query but that still doesn't work

 

[plog]

You can't have 2 fields in a query called the same thing. The table has a TrendingDate and then you tried to create a calculated field called TrendingDate.

 

[brtny82]

Okay, I fixed that but now it's back to giving me the wrong number

 

[plog]

I see that, let me work on this overnight.

 

[brtny82]

Many thanks. You are wonderful!

 

[plog]

I have no idea why that wasn't working. I think I have it. Paste this into a module:

Public Function get_MonthsConsecutive(in_Provider, in_Level, in_Type, in_Date) As Integer

ret = 1
    ' return value, default of 1 for itself
    
counter_i = 0
    ' counter variable to search through next/previous months
    
tmp_Found = 1
    ' trigger to stop searching for consecutive months

While tmp_Found > 0
    dt_Check = DateAdd("m", counter_i + 1, in_Date)
    str_Criteria = "[Level of Care]='" & in_Level & "' AND  [TrendingDate]=#" & dt_Check & "# AND [Type]='" & in_Type & "' AND [Discharging Provider Name]='" & in_Provider & "'"
    tmp_Found = DCount("[Discharging Provider Name]", "[Aftercare Report data]", str_Criteria)
    If tmp_Found > 0 Then counter_i = counter_i + 1
    Wend
    
ret = ret + counter_i

tmp_Found = 1
counter_i = 0

While tmp_Found > 0
    dt_Check = DateAdd("m", counter_i - 1, in_Date)
    str_Criteria = "[Level of Care]='" & in_Level & "' AND  [TrendingDate]=#" & dt_Check & "# AND [Type]='" & in_Type & "' AND [Discharging Provider Name]='" & in_Provider & "'"
    tmp_Found = DCount("[Discharging Provider Name]", "[Aftercare Report data]", str_Criteria)
    If tmp_Found > 0 Then counter_i = counter_i - 1
    Wend

ret = ret - counter_i


get_MonthsConsecutive = ret
End Function

Note that I changed the function name to 'get_MonthsConsecutive'. You will have to change your query to reflect this. Be sure to test it and let me know of any issues.

 

[brtny82]

It's perfect!! Thank you so much!