expression problem

[richaluft]

trying to do a DLookup with a concatenated 'where', in which one of
elements is a Date(patepart, to be exact), while the other ([year])is
text.
I've tried all sorts of variations, but without luck:
Line is:
DLookup("[Fee]", "FeeSchedUCR", "[CPTCode] = " & rst![CPTCode] & And
[year] = " & DatePart("yyyy", rst![Date_From))
[Date_From] is a date data type, and [year] is text

Thanks for any help
Richard

 

[richaluft]

Try:
DLookup("[Fee]", "FeeSchedUCR", _
"[CPTCode] = '" & rst![CPTCode] & "' And [year] = '" & DatePart("yyyy", rst!
[Date_From) & "'")

..I added single quotes around your text fields. This assumes [CPTCode] is
text.

trying to do a DLookup  with a concatenated 'where', in which one of
elements is a Date(patepart, to be exact), while the other ([year])is
text.
I've tried all sorts of variations, but without luck:
Line is:
DLookup("[Fee]", "FeeSchedUCR", "[CPTCode] = " & rst![CPTCode] &  And
[year] = " & DatePart("yyyy", rst![Date_From))
[Date_From] is a date data type, and [year] is text

Thanks for any help
Richard

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200806/1

Thanks for quick reply, but still getting errmsg of "type mismatch".
Any other suggestions?
Richard

 

[richaluft]

Is the [CPTCode] field numeric or text? If numeric then remove the single
quotes from around the value.

DLookup("[Fee]", "FeeSchedUCR", _
"[CPTCode] = " & rst![CPTCode] & " AND [year] = '" & DatePart("yyyy", rst!
[Date_From) & "'")

Here's a good link for the syntax of Domain functions:http://www.mvps.org/access/general/gen0018.htm

Try:
DLookup("[Fee]", "FeeSchedUCR", _

[quoted text clipped - 22 lines]

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200806/1

Thanks for quick reply, but still getting errmsg of "type mismatch".
Any other suggestions?
Richard

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200806/1

RG:
No, CPTCode, as you originally suspected, is Text. I guess there's no
reason to try this new suggestion.
Richard

 

[richaluft]

DLookup() returns a value.  What is the complete line of code that contains
the DLookup()?  Are you putting it into a variable and if so what is theDim
line for the variable?

Is the [CPTCode] field numeric or text? If numeric then remove the single
quotes from around the value.

[quoted text clipped - 23 lines]

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200806/1

RG:
No, CPTCode, as you originally suspected, is Text. I guess there's no
reason to try this new suggestion.
Richard

e

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200806/1

RG:
Finally solved problem by starting with DP = DatePart(-------etc.
Then used DLookup(----- with The variable DP as a criterion instaed
of the DatePart function. This worked fine with your original parsing
advise.
MSAccess was apparrently confused by using the Datepart function as a
criterion.

RL