Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

Format of Expression

This is a question about how to properly create the correct expression in the expression builder to accomplish my intent but first I will give some background.

I am trying to populate the contents of a field 'SubRealTot' on a subform 'subfrm_Related_TTSDetail' based on a query 'subfrm_Related_TTSDetail' and some fields on the mainform '[Forms]![frmTTSHead_Detail_Related]![ReltdPromID]' and '[Forms]![frmTTSHead_Detail_Related]![VendorNumber]'.

I want to select the field 'RealSum' from the query entry that matches the Vendor Number and Related Promotion ID on the main form.  Below I have include the contents of the Expression builder used as the control source for the subform field 'SubRealTot', which is where I want to display the total.  It displays 0. I have never used a compound condition on a Dlookup in the expression builder and suspect that the syntax is not correct.  The expression worked when I had only the first condition
 =DLookUp("[qrySumHstDetail].[RealSum]","qrySumHstDetail","[qrySumHstDetail].[PromID]=" & [Forms]![frmTTSHead_Detail_Related]![ReltdPromID])

Of course the answer I got was incorrect because it always pulled the total for the first vendor it encountered.  I then added the the 'And' condition and now it doesn't match any entries in the query, but it should based on the contents of the respective fields on the screen.

Field 'SubRealTot' Control Source, Expression Builder contents

=DLookUp("[qrySumHstDetail].[RealSum]","qrySumHstDetail","[qrySumHstDetail].[PromID]=" & [Forms]![frmTTSHead_Detail_Related]![ReltdPromID] And "[qrySumHstDetail].[VendorNumber]=" & [Forms]![frmTTSHead_Detail_Related]![VendorNumber])

Any ideas?
0
mlcktmguy
Asked:
mlcktmguy
  • 3
  • 3
1 Solution
 
perkcCommented:
tRY:

=DLookUp("[qrySumHstDetail].[RealSum]","qrySumHstDetail","[qrySumHstDetail].[PromID]=" & [Forms]![frmTTSHead_Detail_Related]![ReltdPromID] & " And [qrySumHstDetail].[VendorNumber]=" & [Forms]![frmTTSHead_Detail_Related]![VendorNumber])

perkc
0
 
rockiroadsCommented:

you need the keyword AND as part of the text, not code

here is your updated whereclause for your DLookup

"[qrySumHstDetail].[PromID]=" & [Forms]![frmTTSHead_Detail_Related]![ReltdPromID] & " And [qrySumHstDetail].[VendorNumber]=" & [Forms]![frmTTSHead_Detail_Related]![VendorNumber])

are they both numeric fields?


0
 
mlcktmguyAuthor Commented:
no, PromID and ReltdPromID are numeric, the two vendor number fields are text.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
mlcktmguyAuthor Commented:
Neither of the suggested formats worked.  Perhaps rockiroads has discovered the pertinent fact, that vendor number is actually a text field.  How would this change the format?
0
 
perkcCommented:
Try:

=DLookUp("[qrySumHstDetail].[RealSum]","qrySumHstDetail","[qrySumHstDetail].[PromID]=" & [Forms]![frmTTSHead_Detail_Related]![ReltdPromID] & " And [qrySumHstDetail].[VendorNumber]='" & [Forms]![frmTTSHead_Detail_Related]![VendorNumber] & "'")

perkc
0
 
perkcCommented:
Text fields need a single quote before and after.  something='text'

Date fields need a number sign before and after. somedate=#date#

perkc
0
 
mlcktmguyAuthor Commented:
Bingo, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now