John Viescas <[EMAIL PROTECTED]> wrote:
Yes, it's probably a data type / precision problem. What is the data type
of PctSlsTarAchd1? What are the discrete values in that field? (In other
words, are they rounded to the nearest .1%?) You might need to do an
explicit convert/rounding of the calculated value in the query to get it to
match what's in the table.
John Viescas, author
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
Running Microsoft Access 2000
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
For the inside scoop on Access 2007, see:
http://blogs.msdn.com/access/
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf
Of DD & RL
Sent: Friday, April 21, 2006 9:32 AM
To: [email protected]
Subject: RE: [ms_access] Dlookup function help
I'm trying to do what you say I'm asking here...the problem may be in the
format of the fields between the tables and query(?). The pctTotSales value
is the result of a calculation and even when I conform the field format (to
standard with 4 decimal places), it still gives me a null answer.
I'm basically trying to do exactly what a Vlookup does in Excel.
I've tried this several different ways and one query result gives me only
the first value from the table for all records for the PctPayout1 field.
John Viescas <[EMAIL PROTECTED]> wrote:
You're asking DLookup to find the value of the PctPayout1 field in the
matrixTOtSales1 table where PctSlsTarAchd1 matches pctTotSales in the
current record. What's in the matrixTotSales1 table? If there's no record
that matches the pctTotSales value, you'll get a Null. What is it you're
really trying to do?
John Viescas, author
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
Running Microsoft Access 2000
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
For the inside scoop on Access 2007, see:
http://blogs.msdn.com/access/
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf
Of DD & RL
Sent: Friday, April 21, 2006 8:04 AM
To: [email protected]
Subject: RE: [ms_access] Dlookup function help
I tried this and the results are zero for "pctTotSales" less than .98 with
blank fields (null) for anything over .98.
John Viescas <[EMAIL PROTECTED]> wrote: Doreen-
Try this:
SELECT [Current Mo Pace PO wPct 2-24-06].pctTotSales, IIf([pctTotSales]
<0.98,0,DLookUp("[PctPayout1]","matrixTotSales1","[PctSlsTarAchd1] = " &
[pctTotSales])) AS value4
FROM [Current Mo Pace PO wPct 2-24-06]
GROUP BY [Current Mo Pace PO wPct 2-24-06].pctTotSales, IIf
([pctTotSales]<0.98,0,DLookUp
("[PctPayout1]","matrixTotSales1","[PctSlsTarAchd1] = " & [pctTotSales]));
John Viescas, author
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
Running Microsoft Access 2000
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
For the inside scoop on Access 2007, see:
http://blogs.msdn.com/access/
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf
Of rokkeee
Sent: Thursday, April 20, 2006 11:42 AM
To: [email protected]
Subject: [ms_access] Dlookup function help
This should be so easy but I can't seem to get the results I'm looking
for while using the Dlookup function. I want to bring in a field from
another table that is not in my query.
Here's the SQL...can you see what I'm doing wrong?
SELECT [Current Mo Pace PO wPct 2-24-06].pctTotSales, IIf([pctTotSales]
<0.98,0,DLookUp("[PctPayout1]","matrixTotSales1","[PctSlsTarAchd1] =
[pctTotSales]")) AS value4
FROM [Current Mo Pace PO wPct 2-24-06]
GROUP BY [Current Mo Pace PO wPct 2-24-06].pctTotSales, IIf
([pctTotSales]<0.98,0,DLookUp
("[PctPayout1]","matrixTotSales1","[PctSlsTarAchd1] = [pctTotSales]"));
Thanks for your impending help!
Doreen
Yahoo! Groups Links
---------------------------------
YAHOO! GROUPS LINKS
Visit your group "ms_access" on the web.
To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
---------------------------------
---------------------------------
New Yahoo! Messenger with Voice. Call regular phones from your PC and save
big.
[Non-text portions of this message have been removed]
Yahoo! Groups Links
---------------------------------
YAHOO! GROUPS LINKS
Visit your group "ms_access" on the web.
To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
---------------------------------
---------------------------------
Celebrate Earth Day everyday! Discover 10 things you can do to help slow
climate change. Yahoo! Earth Day
[Non-text portions of this message have been removed]
Yahoo! Groups Links
---------------------------------
YAHOO! GROUPS LINKS
Visit your group "ms_access" on the web.
To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
---------------------------------
---------------------------------
Blab-away for as little as 1ยข/min. Make PC-to-Phone Calls using Yahoo! Messenger with Voice.
[Non-text portions of this message have been removed]
YAHOO! GROUPS LINKS
- Visit your group "ms_access" on the web.
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
