Weird DCount Runtime Error 2471

[somar]

I am trying to run a loop that will enable me to change the values of multiple text boxes. When I do a DCount/DLookup, I am getting a really weird problem. The part of the code I am having an issue with is below:

If IsDate(Me.Contr ols(DateName)) Then
DateL = D6Date
CritStr = "[Dt] = D6Date"
CritStr1 = "[Dt] = DateL"
MsgBox (DCount("*", "ManHoursQ" , CritStr))
MsgBox (DCount("*", "ManHoursQ" , CritStr1))
End If

----

So the first DCount gives me the correct answer. But the second DCount gives me the runtime error:

Run-time error '2471':

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

-----

[Dt] is a Date field. Can anyone help me out? Thanks.

[twinnyfo]

somar,

I am at a loss how the first DCount works.

If your code is exactly as you posted, then CritStr = "[Dt] = D6Date", which is a string. More correctly, your Criteria expressions should be:

CritStr = "[Dt] = #" & D6Date & "#"
CritStr1 = "[Dt] = #" & DateL & "#"

Let me know if this helps.

[somar]

So, D6Date is a textbox that contains the date "8/1/2014". Yeah, that worked. Thanks a lot.

[twinnyfo]

is the name of the Text Box "D6Date"?

CritStr = "[Dt] = #" & Me.D6Date & "#"
CritStr1 = "[Dt] = #" & DateL & "#"

Have you declared DateL as a date variable?

[somar]

Yes, the Name of the textbox is D6Date and Yeah I declared as a date.

[twinnyfo]

How did the code changes affect things?

Never mind--I just saw in post #3 that it worked!

Great!

[somar]

So I actually rewrote the code like this and it worked as well:

If IsDate(Me.Contr ols(DateName)) Then
CritStr = "[Dt] = " + DateName + " AND [Shift] = " + ShiftName
MsgBox (DCount("*", "ManHoursQ" , CritStr))
End If

[somar]

Thanks a lot for your help twinnyfo.