Solved

Type mismatch in VBA code

Posted on 2013-12-02
3
421 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

829 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