Issue with using a variable with DCount 1

[Sydney1]

Good morning,

I am attempting to filter a form on the open event using the DCount of the underlying query when it matches a certain criteria. I 've created a variable "Days" (integer) in the code that needs to equal a field in the query. I'm adding 1 to "Days" within a loop until a a certain criteria is met.

My issue is that since the "Days" is not in the query, Access does not recognize it. Also, I believe I do not have the proper quotations for "Days", as I either get a data mismatch or a runtime error 2001. The problem is in the second part of the code where "[daystoreferral] = days "

Any help would be greatly appreciated

Sydney

[code/]Dim Days As Integer
Dim strRecordsource As String
Days = "1"
Do Until DCount("*", "qryForeclosureReferralMain", "SignOn = currentuser() and [type] = 'review' and [Datecompleted] is null ") = 0


If DCount("*", "qryForeclosureReferralMain", "SignOn = currentuser() and [daystoreferral] = days and [type] = 'review' and [Datecompleted] is null and ([InvestorID] like 'E*' or [InvestorID] like 'F*')") > 0 Then


strRecordsource = "Select * from qryForeclosureReferralMain WHERE ([SignOn]=currentuser() and [type] = 'review' and [Datecompleted] is null and [daystoreferral] = days and ([InvestorID] like 'E*' or [InvestorID] like 'E*')) "
Me.RecordSource = strRecordsource
end if

Days = Days + 1
Loop

[/code]

 

[mp9]

A couple of things. You dim Days as an integer but then initialise it as "1" - a string! Also, you need to use concatenation to get it into your strings. Here's some revised code to try.

Dim Days As Integer
Dim strRecordsource As String
Days = 1 
Do Until DCount("*", "qryForeclosureReferralMain", "SignOn = currentuser() and [type] = 'review' and [Datecompleted] is null  ") = 0


If DCount("*", "qryForeclosureReferralMain", "SignOn = currentuser() and [daystoreferral] =" & days & " and [type] = 'review' and [Datecompleted] is null and  ([InvestorID] like 'E*' or [InvestorID] like 'F*')") > 0 Then


strRecordsource = "Select * from qryForeclosureReferralMain WHERE ([SignOn]=currentuser() and [type] = 'review' and [Datecompleted] is null and [daystoreferral] = " & days & " and ([InvestorID] like 'E*' or [InvestorID] like 'E*')) "
  Me.RecordSource = strRecordsource
end if

Days = Days + 1
Loop

 

[Sydney1]

MP9,

That worked great. Thanks so much for your help.

Sydney