Solved

Format of Expression

Posted on 2004-04-13
7
357 Views
Last Modified: 2008-02-01
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
Comment
Question by:mlcktmguy
  • 3
  • 3
7 Comments
 
LVL 10

Expert Comment

by:perkc
ID: 10814800
tRY:

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

perkc
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10814801

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
 
LVL 1

Author Comment

by:mlcktmguy
ID: 10815007
no, PromID and ReltdPromID are numeric, the two vendor number fields are text.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:mlcktmguy
ID: 10815130
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
 
LVL 10

Accepted Solution

by:
perkc earned 100 total points
ID: 10815205
Try:

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

perkc
0
 
LVL 10

Expert Comment

by:perkc
ID: 10815227
Text fields need a single quote before and after.  something='text'

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

perkc
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 10815447
Bingo, thanks.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now