Error in code

[cbende2]

Hello all,

I'm getting this error:

The expression you entered as a query parameter produced this error: 'i'

Can I not use my declared variable "i" in Dcount?

For i = 0 To 51

    O(1, i + 1) = DCount("Field1", "Table1", "DateValue([Field1]) Between DateSerial(Forms!Form3!txtYear-1,Forms!Form3!txtMonth,Forms!Form3!txtDay)+(i*7) And DateSerial(Forms!Form3!txtYear-1,Forms!Form3!txtMonth,Forms!Form3!txtDay)+6+(i*7)")
    Me.Controls("Text" & (i + 1)).Value = O(1, i + 1)


Next i

Thanks

[pbaldy]

Well, you can't use it outside VBA, and the DCount() is passing what you've given it to the database engine. You have to concatenate the values into the string, like:

DCount("Field1", "Table1", "DateValue([Field1]) Between #" & DateSerial(Forms!Form3!txtYear-1,Forms!Form3!txtMonth,Forms!Form3!txtDay)+(i*7) & "# And #" & DateSerial(Forms!Form3!txtYear-1,Forms!Form3!txtMonth,Forms!Form3!txtDay)+6+(i*7) & "#")

[cbende2]

Thanks Paul, that did the trick.

While we're here I have another question.

Field1 is a ShortText field hence the DateSerial(), and Some values in records are "N/A" and some are Null with no value. Is there anyway to skip these records when counting? I'm getting an error saying data mismatch.

[pbaldy]

If the field is text try using ' instead of # as the delimiter, but if I had to bet the error is coming from the DateValue() function. I personally would use the date/time data type. Not sure how to work around having text in there. I suppose one way would be to create a query that only returned date values and run your DCount() against that.

[cbende2]

Ahhh great thinking! I'll try your second suggestion.

[June7]

Otherwise use an IIf in the expression to handle the different possible values.

DateValue(IIf(IsDate([Field1]), [Field1], #1/1/1900#))

CDate() also works. I don't know if it has advantage/disadvantage over DateValue().