UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Syntax, Access 2016    
 
   
mike60smart
post Jan 21 2020, 03:12 PM
Post#1


UtterAccess VIP
Posts: 13,649
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I am getting the following error when I try to run a Report:-

Attached File  error.PNG ( 4.64K )Number of downloads: 3


Click OK and the following error displayed:-

Attached File  error2.PNG ( 5.97K )Number of downloads: 4


The Report is based on the following query:-

CODE
SELECT tblMembers.EmployeeNumber, [LAST NAME] & ", " & [First name] AS [Employee Name], tblMembers.PersonnelAreaText, tblMembersPayments.PaymentDate, Right([PaymentDate],4) AS [Year], Format([PaymentDate],"mmm- yyyy") AS [Pmt Month], tblMembersPayments.DuesPaid, tblMembersPayments.Adjustment, Nz([DuesPaid],0)+Nz([Adjustment],0) AS TotalPaid, Val(DLookUp("YearlyRate","tblUnionRates","[District] = '" & [PersonnelAreaText] & "' And [RateYear] = " & [Year] & "")) AS DuesRate, tblMembers.Status
FROM tblMembers INNER JOIN tblMembersPayments ON tblMembers.MemberID = tblMembersPayments.MemberID
WHERE (((Right([PaymentDate],4))=[Enter Year Required]) AND ((tblMembers.Status)="Active"));


With this particular field in the query causing the problem:-

CODE
DuesRate: Val(DLookUp("YearlyRate","tblUnionRates","[District] = '" & [PersonnelAreaText] & "' And [RateYear] = " & [Year] & ""))


Would the error occur if there are Null values in the District or RateYear Fields in tblUnionRates?

Any help appreciated.

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
theDBguy
post Jan 21 2020, 03:20 PM
Post#2


UA Moderator
Posts: 77,481
Joined: 19-June 07
From: SunnySandyEggo


Hi Mike. Try wrapping the DLookup() expression with the Nz() function.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
mike60smart
post Jan 21 2020, 03:42 PM
Post#3


UtterAccess VIP
Posts: 13,649
Joined: 6-June 05
From: Dunbar,Scotland


Hi Guy

No didn't solve it.

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
theDBguy
post Jan 21 2020, 03:48 PM
Post#4


UA Moderator
Posts: 77,481
Joined: 19-June 07
From: SunnySandyEggo


You only did the DLookup() and not the Val(), right?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
mike60smart
post Jan 21 2020, 03:57 PM
Post#5


UtterAccess VIP
Posts: 13,649
Joined: 6-June 05
From: Dunbar,Scotland


HI Guy

Yes it is now:-

CODE
DuesRate: Val(Nz(DLookUp("YearlyRate","tblUnionRates","[District] = '" & [PersonnelAreaText] & "' And [RateYear] = " & [Year] & ""),0))


Now when I run the Report I get this error:-

Attached File  error.PNG ( 5.72K )Number of downloads: 0

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
RJD
post Jan 21 2020, 04:03 PM
Post#6


UtterAccess VIP
Posts: 10,205
Joined: 25-October 10
From: Gulf South USA


Hi Mike: Assuming that PaymentDate is a true date, I would have tried this instead. Note the change in three places for dealing with the payment year. You were using an alias in the DLookup, which usually causes me problems...

SELECT tblMembers.EmployeeNumber, [LAST NAME] & ", " & [First name] AS [Employee Name], tblMembers.PersonnelAreaText, tblMembersPayments.PaymentDate, Year([PaymentDate]) AS [PaymentYear], Format([PaymentDate],"mmm- yyyy") AS [Pmt Month], tblMembersPayments.DuesPaid, tblMembersPayments.Adjustment, Nz([DuesPaid],0)+Nz([Adjustment],0) AS TotalPaid, NZ(DLookUp("YearlyRate","tblUnionRates","[District] = '" & [PersonnelAreaText] & "' And [RateYear] = " & Year([PaymentDYear])),0) AS DuesRate, tblMembers.Status
FROM tblMembers INNER JOIN tblMembersPayments ON tblMembers.MemberID = tblMembersPayments.MemberID
WHERE Year([PaymentDate])=[Enter Year Required] AND tblMembers.Status="Active";

Be careful about the parens - I'm not sure I got them all balanced correctly.

If PaymentYear is actually text, then we have to alter the SQL to accommodate that.

See if this gets you further along.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
mike60smart
post Jan 21 2020, 04:23 PM
Post#7


UtterAccess VIP
Posts: 13,649
Joined: 6-June 05
From: Dunbar,Scotland


Hi Joe

Now it is prompting me for the following:-
Attached File  error.PNG ( 2.17K )Number of downloads: 0


Then it prompts for Year - Ener the year and then the same error message



--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
WildBird
post Jan 21 2020, 04:42 PM
Post#8


UtterAccess VIP
Posts: 3,724
Joined: 19-August 03
From: Auckland, Little Australia


I personally hate using things lik NZ, and Val etc, especially directly in a query. I prefer to build each individual part in code, validate each etc, and then put together a SQL statement at the end and use that. Doing it that way you can copy the SQL string into a query and check it there.

Anyhoo, the last message is to do with
Year([PaymentDYear])

You have Year as a field name, which is a reserved word, and Joe has put in Year("NameOfFieldWithPaymentData"), I think if you change PaymentDYear to the name of the field with the payment, it should help.




--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
RJD
post Jan 21 2020, 05:01 PM
Post#9


UtterAccess VIP
Posts: 10,205
Joined: 25-October 10
From: Gulf South USA


Hi Mike: Sorry, I typed that incorrectly. Try changing this ...

NZ(DLookUp("YearlyRate","tblUnionRates","[District] = '" & [PersonnelAreaText] & "' And [RateYear] = " & Year([PaymentDYear])),0) AS DuesRate,

to ...

NZ(DLookUp("YearlyRate","tblUnionRates","[District] = '" & [PersonnelAreaText] & "' And [RateYear] = " & Year([PaymentDate])),0) AS DuesRate,

I'll pay better attention when I copy/paste... and thanks, Wildbird, you caught the problem.


HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
WildBird
post Jan 21 2020, 06:26 PM
Post#10


UtterAccess VIP
Posts: 3,724
Joined: 19-August 03
From: Auckland, Little Australia


QUOTE
I'll pay better attention when I copy/paste... and thanks, Wildbird, you caught the problem.


Amazing how much easier it is to spot mistakes in other peoples code than when it is your own!

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
RJD
post Jan 21 2020, 08:33 PM
Post#11


UtterAccess VIP
Posts: 10,205
Joined: 25-October 10
From: Gulf South USA


thumbup.gif

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
mike60smart
post Jan 22 2020, 10:34 AM
Post#12


UtterAccess VIP
Posts: 13,649
Joined: 6-June 05
From: Dunbar,Scotland


Hi Joe & Wildbird

Many thanks that fixed it

Thanks yet again
cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
RJD
post Jan 22 2020, 12:57 PM
Post#13


UtterAccess VIP
Posts: 10,205
Joined: 25-October 10
From: Gulf South USA


You are very welcome. We are always happy to assist.

Continued success ...

Regards
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th February 2020 - 02:46 PM