Problem in a query with DLookUp

I have a query that take the Data_Nascita value from table Anagrafica.
In Expr1 i calculate the value in year, no problem.
Then i am tring to get the correct value of Categoria related to age value i
have the problem.
With this code a get always the first value in the table that is not
correlated to the age calculation and obviously is not correct.

SELECT Anagrafica.Data_Nascita, DateDiff("yyyy",[Data_Nascita],Date())-IIf
(Format([Data_Nascita],"mmdd")>Format(Date(),"mmdd"),1,0) AS Expr1, DLookUp
("Categoria","Anni_Categoria_Calcio_LND",[Expr1]) AS Categoria
FROM Anagrafica;

The table Anni_Categoria_Calcio_LND is like 

Anni   Categoria     Descrizione
5        Cat_A        Description A
6        Cat_B        Description B
7        Cat_C        Description C
.......
18      ......         ...............

The value of Categoria in the DLookUp is always the first, Cat_A also if the
age is 6, 7 ecc

Any idea ?
Thank you in advance
Diego

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

0
Diego
5/10/2010 10:16:14 AM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
1458 Views

Similar Articles

[PageSpeed] 49

By design, DLookup will only return a single value, and you really don't 
have any control over which value it will return if there are multiple that 
match the criteria.

However, your DLookup statement doesn't look correct anyhow: [Expr1] is an 
Alias for the Age calculation you've performed: it cannot be used by itself 
as a criteria. If nothing else, you need something like "[Age] = " & 
[Expr1], although to be honest I think you'll need to repeat the calculation 
("[Age] = " & 
DateDiff("yyyy",[Data_Nascita],Date())-IIf(Format([Data_Nascita],"mmdd")>Format(Date(),"mmdd"),1,0))

-- 
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Diego via AccessMonster.com" <u28514@uwe> wrote in message 
news:a7ca407eaee68@uwe...
>I have a query that take the Data_Nascita value from table Anagrafica.
> In Expr1 i calculate the value in year, no problem.
> Then i am tring to get the correct value of Categoria related to age value 
> i
> have the problem.
> With this code a get always the first value in the table that is not
> correlated to the age calculation and obviously is not correct.
>
> SELECT Anagrafica.Data_Nascita, DateDiff("yyyy",[Data_Nascita],Date())-IIf
> (Format([Data_Nascita],"mmdd")>Format(Date(),"mmdd"),1,0) AS Expr1, 
> DLookUp
> ("Categoria","Anni_Categoria_Calcio_LND",[Expr1]) AS Categoria
> FROM Anagrafica;
>
> The table Anni_Categoria_Calcio_LND is like
>
> Anni   Categoria     Descrizione
> 5        Cat_A        Description A
> 6        Cat_B        Description B
> 7        Cat_C        Description C
> ......
> 18      ......         ...............
>
> The value of Categoria in the DLookUp is always the first, Cat_A also if 
> the
> age is 6, 7 ecc
>
> Any idea ?
> Thank you in advance
> Diego
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1
> 


0
Douglas
5/10/2010 11:07:36 AM
Reply:

Similar Artilces:

negative values causing column title problem
How do I get the Column titles x axis lables at he bottom of the chart field when have negative values It s friday and this is killing me. Select the axis Choose Format>Selected axis On the Patterns tab, for Tick Mark Labels, select Low TFrisch wrote: > How do I get the Column titles x axis lables at he bottom > of the chart field when have negative values > > It s friday and this is killing me. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html hey thanks - boy was thaqt annoying >-----Original Message----- >Select th...

Problem: new calendar entries land on wrong date
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange When making a new calendar entry, it will not land on the date I select, but rather on: March 20, 2020!!!! <br><br>The only way in which I can make a calendar entry is to make it in iCal, and then let iCal synch with Entourage calendar. <br><br>Likewise, the Entourage calendar, when printing, will only show/print imported holidays, not the actual entries as displayed. <br><br>What's wrong? <br> I have re-installed Office from scratch - no imp...

Sending Mail problems
client, Outlook 2002, SP2 server, Exchange 5.5, SP4 When I send an email it just sits in the Outbox until I click on either my Inbox, another email, or another folder, then the email will send from the Outbox and appear in the Sent Items. Also, I constantly need to hit Send/Receive to check for new emails. I checked all my settings and Outlook is setup to send/receive Immediately when connected. Am I missing something? Any advice would be appreciated. I'm having the same problem and also posted the same question (about an hour after you did, as it turns out). If someone res...

My laptop & OWA problems...
I am having problems with my laptop and connecting to OWA, I get a logon screen and I can login but I can't open of view an email and I can't click anything, nothing happens. My laptop has Win XP Pro, and I've used the windows update and install all the recommended updates, but nothing works. I know OWA is working fine because my friend can sign on her computer and have access to all the features in my account. So it appears to me just my laptop. please help. "Troubled person" <anonymous@discussions.microsoft.com> wrote in message news:1b3bb01c44fe8$9a9b1...

Problem with Excel Links
I have a number of Excel workbooks and I wish to link them to a single summary worksheet. Linking them isn't a problem, however I would like to be able to 'switch off' the links to particular workbooks/worksheets so that I could select which were included on the summary sheet at any one time. Put another way, I want to have all the linked workbooks populated with data and then to be able to select which worksheets 'feed' through to the summary sheet to explore different scenarios. Any help will be very much appreciated. John You can use Edit, Links and change your sour...

EXCEL 2003 MACRO Problem
Hi all gurus please help. I have a windows schelduler to open at a certain time and when the cells have been updated the file closes. The problems i'm having it the file stays open and i have to manually close the open file on a weekly basis, the cells are not updated either? Please see my code below: Private Changed As Boolean Private Sub Workbook_Open() 'start with the workbook showing unchanged Changed = False ' create our shutdown timer Application.OnTime Now + TimeValue("00:00:10"), procedure:="ThisWorkbook.Auto_Close" ...

DateAdd Working Day Query
Hi, I wonder if someone could help me please. I'm trying to write an expression (see code below) within a query where I calculate 15 working days from a a given date, in this case the 'Date a report was sent'. DateAdd(“ww”,15,[Date Report Sent]-DateAdd([Date Report Sent],1)*2-IIf (Weekday([Date Report Sent],1)=7,IIf(Weekday([Date Report Sent],1)=7,0,1),IIf (Weekday([Date Report Sent],1)=7,-1,0))) But when I add it to my query I keep getting this message: 'The expression you entered has a function containing the wrong number of arguments.' I'm assuming, maybe wrongl...

Money 2005 Small Business Problem
I cannot get my estimates to say estimate at the top, all of them say "invoice" this is troubling me because I know it used to print them as estimate. Somebody please help me before I have to abandon MS Money Small usiness for a more functional program. This is a simple problem, but it aggravates the hell out of me!! In microsoft.public.money, Chris Jones wrote: >I cannot get my estimates to say estimate at the top, all of them say >"invoice" this is troubling me because I know it used to print them as >estimate. Somebody please help me before I have to ...

RMS SO 2.0 Manager
On one of my RMS SO 2.0 Manager's computer I'm having a huge delay (about 5 minutes) before the Item list shows up (Database - Items). This only happens on this one machine that's "directly" connected to the RMS server (no VPN etc), while it's trying to bring up the Items List the SOMANAGER.EXE process CPU Utilization stays at 99% and there is no network utilization/activity. When I bring up an (all) Item List/Value report it doesn't take very long at all. Has anybody else encountered this problem and knows of a potential solution? I've already tried r...

Update Query and record lock violations
I have a local table constructed from two remote sqltables. I add records successfully via an append query in Access 2003. I am attempting to modify date records that have changed in the remote table to the local table (they change occasionally in the sqltable) via update query. I have the query set to pull date and time from the sqltable (there is a common key to both tables that acts as the master key in the local table) and update the date and time in the local table where the date and time are not equal in both tables. It seems to work - it returns exactly the number of records ...

Exchange 2003 problem after sp1 installation
After the sp1 installation users unable to access recent emails with both OWA and Outlook 2003! Somebody can help me please! Many thanks, Giampiero First, is this Exchange 2003 SP1 or Windows 2003 SP1? Can they access any of their messages? Or only recent (meaning what? the last few weeks? days?). This could be related to your antivirus software. Try stopping your antivirus software temporarily and see if that helps. I have seen E2K3 servers with Symantec Mail Security do this before. -- Jim McBee Web: http://www.somorita.com Blog: http://mostlyexchange.blogspot.com Stuff: htt...

Toolbar problem causes screen to freeze
xp pc about a year old has been working fine. Suddenly when I open M Excel 2000 it will load s/s and allow manipulation of data but as soo as I try to use the tool bar the software freezes and cannot be used. Virus definitions up to date and no virus showing. Anybody got any thoughts -- Message posted from http://www.ExcelForum.com Maybe you have a corrupt toolbar file, look for a file with the extension *.xlb Rename the extension to *.bak or move it from the folder, then excel will create a new file. Of course if you have configured it a lot you have to do that again but if it stops exc...

Using an Object from a Current form for Criteria in a Query
I am using Access 2000. I have a Command Button on a form that is designed to preview or print that customer's invoice. I use a Pop-Up Dialog Box. The end user will supply 2 pieces of data in this Dialog Box. This data, together with the Customer ID from the current form. will provide the 3 necessary pieces of info to pass on to the query, and eventually the report, that creates the invoice. I suspect that the "Me." or "Me!" "command" or "action" must be used, but am not clear about how to use it. Perhaps someone can give me so...

Exchange 2000 problem
More and more people have started using email adress like this: Firstname..Lastname@domain.com and this is an invalid format i exchange 2000.. Any way to work around this with exchange 2000? I dont see any option to alow it. Thank - Cabby3 ----------------------------------------------------------------------- Posted via http://www.webservertalk.co ----------------------------------------------------------------------- View this thread: http://www.webservertalk.com/message913884.htm ...

having problems with a multiple conditions nesting formula
DATA (in colum A cells 1-5): 14 11 12 16 8 ISSUE: How can I automatically populate YES or NO into column B beside each data point in column A based on the following scenario. I need to know if the data point in column A is >= 20% higher than all of the prior data points would also work. So B1 would be N/A because there is no prior data point for A1, B2 would be yes if the data point in A2 is >= 20% higher than the data point A1, B3 would be yes if the data point in A3 is 20% higher than either A1 OR A2, B4 would be yes if the data point in A4 is 20% higher than eith...

Pie chart problem #2
I have a spreadsheet, transferred from access database of previous children's party bookings. This s/sheet contains many columns containing info as below. Title, First Name, Last Name, Address, City, County, Postcode, Home Phone, Mobile Phone, Email Address, Date, Child's name, Age, Gender, Party Theme, Duration, Enquiry Source. For the purposes of business analysis/future marketing/advertising I am trying to create pie charts to analyse by %. For example I wish to create a pie chart of the column titled Enquiry source which contains customer responses as categorised below- I...

Problems with Window in Dialog Owner Draw? #4
I Skin the CDialog based windows,in the window,the Minbox and MaxBox and CloseBox are exist,the title bar are Owner drawed of cause and the three "button",They show well,but the button in the task bar that stand for the Dialog ,does not work well, when the dialog is active,you click the button in task bar the dialog can not hide(Or as we say the dialog in min state.). thanks, Tylor ...

Problems with array formula
Hi i have a spread sheet which has data about different sectors. I want to find out the average of each sector. {=AVERAGE(IF($E$7:$E$307=IU85,J7:J307))} This is teh formula i am using. If column e which has details about the sector of each company matches the sector name in IU85 then give me the average of correspondingdata in column j. Now the issue is that some companies data is not present and they were blank. It is giving error while computing the average. Secondly i tried to change the blanks and added text in place of blanks. But it is still giving me those errors. Can anyone please...

Self Destructing Queries #2
Doesn't anyone else have this problem?? -- baracuda ----------------------------------------------------------------------- baracuda9's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1626 View this thread: http://www.excelforum.com/showthread.php?threadid=27675 ...

using Query Based distribution groups between domains
I am trying to use query based DG's for passing world wide notificatons across domains. I have a global QBDG containing QBDG from our 3 child domains. When I am sendin an email to this DG the email only reach the recipients in the domain where the global dg is placed, none of the the recipients in the other child domains gets the email. When I do a preview on the global DG the child domain qbdg is visible so it looks as if it would work. Any ideas appreciated. Sounds like an issue with the expansion server. Have you considered setting the expansion server to try and work through t...

Symbol Scanner Hands-Free Problem
We have a Symbol 2208 scanner set up with RMS, which we like to use in hands-free mode. Sometimes, it won't scan in that mode - light just stays red and you have to pick up the scanner and use manually. Then, it will just start working again. Anybody know what the trick is to get it to work properly in hands-free mode? I checked the documentation, all it lists is how to set up the stand and how the mode works, but it doesn't say anything about troubleshooting that part. I also tried rebooting to no avail. Thanks for any input, Miranda in Madison, WI This is a multi-part message i...

Preventing Errors on calculations where query returns no results
Hi everyone I have a query which depending on the users selection may or may-not return any results. The expression below works fine when results are returned but when there are no records it returns '#Error': =IIf([expirydays]<1,"expired " & Abs([expirydays]) & " days ago") Is there a way to modify it to gracefully fall over (ie be blank) when there are no records? I have tried 'IIf([expirydays] is null,null' and 'IIf(len([expirydays])<1,null' but neither worked. Thanks in advance, John Apologies, the below refers to a c...

FROM and JOIN Problems
Probably basics to you guys but still having loads of trouble and would appriciate any help. I have a table "tblNames" consisting of [ID] [firstname] [surname] [email] I have another table "Master" [ID] [Name] [Email] [Notes] - there is a form built from this table "Issues" Field Master.Name combines tblNames Firstname & Surname fields This displays in the master table as the full name. I now want to complete the Master.Email field using the Master.Name field. I have managed to do this in the "Issues" form by using Master.Name as th...

Fix for problem opening/saving files
A recent Windows update has been causing many people problems opening and saving files. See the following for possible solutions: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.word.application.errors&mid=ab8ee928-cf0d-416c-b961-b4c9eab1d1d7&sloc=en-us http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.powerpoint&mid=d2e4e27f-7e22-43ad-9626-be04808e221d&sloc=en-us http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.excel.misc&mid=1fe358b1-c8eb-4...

DLookup and 0
I am using DLookup on a report. If the value of DLookup is 0, how can I display 0 on the report? Can I combine DLookup with Iif? If so, what would be the syntax? Thanks! Do you m,ean Null instead of 0? If DLookUp returns a zero, you should be able to display it just fine. If DLookUp returns Null, you will need to use the Nz function to replace the Null with something else: Nz(DLookUp("","",""),0) Carl Rapson "Danu" <Danu@discussions.microsoft.com> wrote in message news:7B029D12-6D44-4879-BE47-54F247031A29@microsoft.com... >I am usi...