dLookup Syntax in Access for form checkbox control source

[sanderson]

Hello:

Well here we go, and I'm sure I am missing the obvious. Why won't this work?

=DLookUp("[approved]","[tblocalApprovalLog]", “[dayDate] =‘” & [Forms]![frmTimeCard].[txtDate] & “’ And "[weekDate] =‘" & [Forms]![frmTimeCard].[txtPeriodStart] & " - " & [Forms]![frmTimeCard].[txtPeriodEnd] & "’ And [empid]='" & [txtEmployeeNo] & "'")

In contract, this does, and I've just added one more field to the mix. The error is invalid syntax, operand without an operator...

=DLookUp("[approved]","[tblocalApprovalLog]","[weekDate] between #" & [Forms]![frmTimeCard].[txtPeriodStart] & "# And #" & [Forms]![frmTimeCard].[txtPeriodEnd] & "# And [empid]='" & [txtEmployeeNo] & "'")

[ItsMe]

What is the Text Format property of your control that has the expression? In the Property Sheet and under the Data tab is the Text Format property. It should be Plain Text for expressions. You do not want to use Rich Text as an option for the Text Format Property.

[orange]

In addition to ItsMe's comment, you have some strange quote symbols in your code

=DLookUp("[approved]","[tblocalApprovalLog]", “[dayDate] =‘” & [Forms]![frmTimeCard]......

From my experience Access uses ' and ".

[June7]

Your first statement shows odd " and ' marks (slanted, italics). How did those get in there? Access won't like them.

[sanderson]

I removed all the wierd quotation characters. Because I wrote these in Microsoft Word, which likes to interchange the quotation characters, this was the result. Regardless, it still is not happy with me and when I paste it in I get the same issue...

=DLookUp("[approved]","[tblocalApprovalLog]", "[dayDate] ='" & [Forms]![frmTimeCard].[txtDate] & "' And "[weekDate] ='" & [Forms]![frmTimeCard].[txtPeriodStart] & " - " & [Forms]![frmTimeCard].[txtPeriodEnd] & "' And [empid]= '" & [txtEmployeeNo] & "' ")

[sanderson]

Perhaps the confusion is that there are too many quotes to begin with. I am entering this directly into the Control Source for the checkbox, not through code. When I try to use the expression builder, it adds all kinds of stuff. Perhaps I do not need all the quotes, but do I need them for the constants?

=DLookUp("[approved]","[tblocalApprovalLog]", "[dayDate] ='" & [Forms]![frmTimeCard].[txtDate] & "' And "[weekDate] ='" & [Forms]![frmTimeCard].[txtPeriodStart] & " - " & [Forms]![frmTimeCard].[txtPeriodEnd] & "' And [empid]= '" & [txtEmployeeNo] & "' ")

[orange]

Dates should be enclosed with octothorpes (hash marks #),
Strings should be enclosed in quotes '
Numbers do not require special enclosing punctuation.

[sanderson]

While this information does represent a date, the data type is a string in the table. The question is, what is the difference between coding a dLookup in Access VBA vs placing (hardcoding) it directly in the control?

[orange]

???Not sure I'm following??

You would use a DLookup with some criteria to make the process generic/reusable.
If you hard code anything, you have removed reusability.

[sanderson]

What I mean by hardcoding is entering it directly into the control source property on the form, rather than building the string in VBA.

[June7]

A date represented by a string will not work for filtering by a range. The " - " in criteria is meaningless. There is also an extra " mark in front of [weekDate].

=DLookUp("[approved]","[tblocalApprovalLog]", "[dayDate] ='" & [Forms]![frmTimeCard].[txtDate] & "' And CDate([weekDate]) BETWEEN #" & [Forms]![frmTimeCard].[txtPeriodStart] & "# AND #" & [Forms]![frmTimeCard].[txtPeriodEnd] & "# And [empid]= '" & [txtEmployeeNo] & "' ")

[sanderson]

The reason why the dash is necessary is that it is looking at a string with the dash incorporated into it.

Perhaps this changes my dLookup??

=DLookUp("[approved]","[tblocalApprovalLog]","[dayDate] ='" & [Forms]![frmTimeCard].[txtDate] & "' And [weekDate] = '" & [Forms]![frmTimeCard].[txtPeriodStart] & " - " & [Forms]![frmTimeCard].[txtPeriodEnd] & "' And [empid]= '" & [txtEmployeeNo] & "' ")

But what I still do not get, is why the form displays the square dots instead of the actual value. There is code for when approve is clicked and the Submit totals button is pressed to check the approved column.

I also tried just setting the control source to the field, and got the same result.

Thoughts? And much thanks!

[June7]

Okay, got it. Your original expression should work. But I can't test because TimeCards table is not available. Can't open frmTimeCard. Would have to do too much modifying of db to get this to run.

What 'square dots' - an oxymoron, can dots be square?

[sanderson]

The square dots in the checkbox column in the Approved column...

[June7]

That does happen with UNBOUND checkboxes and all records will show the same value.

This issue is closely related to your other thread. https://www.accessforums.net/forms/b...orm-54498.html