Search results

Okay, I didn't know if there was a module I could do or an Iif expression. Thanks

Thanks that worked! Unfortunately I didn't think that through. Is there a way to return the date entered on the form and if there is a record, then also return the previous two months? But if there isn't a record for the date on the form, then do not return records on the previous two months...

Hello, I have a Query called [Trending Report Query] that is pulling from the table [Monthly Trending data]. I have a form called [Monthly Trending] that I'm using to specify what trending date I'm pulling from (it is in format mm/dd/yyyy). In the field criteria of the Query, I have...

It's perfect!! Thank you so much!

Many thanks. You are wonderful!

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

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

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

Here you go :)

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

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

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?

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?

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

In the VBA above, I changed this part to "Discharging Provider Name" tmp_Found = DCount("[Discharging Provider]", "[Aftercare Report data]", "[Level of Care]=" & in_Level & " AND [Type]='" & in_Type & "' AND [Trending Date]=#" & DateAdd("m", counter_i - 1, in_Date) & "#") And I changed my query...

This is the VBA Code I put in the module, can you tell me exactly what I change? Public Function get_ConsecutiveMonths(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...

Thanks! When you say VBA code, do you mean the code I put in for the module?

Here is the SQL, instead of me entering the parameters, I want it to show me all that it applies to or give be a count of how many consecutive months of data that record has so I can then do 3 or more SELECT [Aftercare Report data].[Discharging Provider Name] AS DischargingProvider, [Aftercare...

Okay, I got the module and I input ConsecutiveMonths: get_ConsecutiveMonths([Discharging Provider],[Level of Care],[Type],[Trending Date]) in the query but it keeps asking me to Enter Parameter Value for Discharging Provider

And here is a sample database