Solved

Type mismatch in VBA code

Posted on 2013-12-02
3
391 Views
Last Modified: 2013-12-02
I get a type mismatch error when I run a report preview using this code:

Me.txtListingsTakenGoal = Nz(DLookup("[ListingsCount]", "tblMemberGoals", "[TeamMember] = Forms!frmSelectDateRangeAndMembers!txtMemberName") And "[GoalMonth] = Forms!frmSelectDateRangeAndMembers!txtMonth", 0)

I get no error when I just have this:

Me.txtListingsTakenGoal = Nz(DLookup("[ListingsCount]", "tblMemberGoals", "[TeamMember] = Forms!frmSelectDateRangeAndMembers!txtMemberName", 0)

Note: The field GoalMonth is a number field in the table.
0
Comment
Question by:SteveL13
  • 2
3 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39689917
If team member is text, try this:

Me.txtListingsTakenGoal = Nz(DLookup("[ListingsCount]", "tblMemberGoals", "[TeamMember] = '" &  Forms!frmSelectDateRangeAndMembers!txtMemberName & "') And "[GoalMonth] = Forms!frmSelectDateRangeAndMembers!txtMonth"), 0)

Open in new window


If it is a numeric ID:

Me.txtListingsTakenGoal = Nz(DLookup("[ListingsCount]", "tblMemberGoals", "[TeamMember] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & ") And "[GoalMonth] = Forms!frmSelectDateRangeAndMembers!txtMonth"), 0)

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39689926
Correction:

Text team member:
Me.txtListingsTakenGoal = Nz(DLookup("[ListingsCount]", "tblMemberGoals", "[TeamMember] = '" &  Forms!frmSelectDateRangeAndMembers!txtMemberName & "' And [GoalMonth] = " &  Forms!frmSelectDateRangeAndMembers!txtMonth ), 0)

Open in new window


Or for a numeric team member ID:

Me.txtListingsTakenGoal = Nz(DLookup("[ListingsCount]", "tblMemberGoals", "[TeamMember] = " &  Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [GoalMonth] = " &  Forms!frmSelectDateRangeAndMembers!txtMonth ), 0)

Open in new window

0
 

Author Comment

by:SteveL13
ID: 39689943
Is numeric and the last solution worked but now I have to add another form criteria.  I'll post another topic.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 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

11 Experts available now in Live!

Get 1:1 Help Now