 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / June 2008expression problem
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 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 [quoted text clipped - 7 lines] >Thanks for any help >Richard  SignatureRuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. On Jun 29, 12:09 pm, "ruralguy via AccessMonster.com" <u12102@uwe> wrote: > 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 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 >On Jun 29, 12:09 pm, "ruralguy via AccessMonster.com" <u12102@uwe> >wrote: [quoted text clipped - 7 lines] >Any other suggestions? >Richard SignatureRuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. On Jun 29, 12:40 pm, "ruralguy via AccessMonster.com" <u12102@uwe> wrote: > 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 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 the Dim line for the variable? >On Jun 29, 12:40 pm, "ruralguy via AccessMonster.com" <u12102@uwe> >wrote: [quoted text clipped - 8 lines] >reason to try this new suggestion. >Richard e SignatureRuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. On Jun 29, 3:36 pm, "ruralguy via AccessMonster.com" <u12102@uwe> wrote: > 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 the Dim [quoted text clipped - 21 lines] > > 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 Excellent! Thanks for postings back with your success. >On Jun 29, 3:36 pm, "ruralguy via AccessMonster.com" <u12102@uwe> >wrote: [quoted text clipped - 12 lines] > >RL SignatureRuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.