Type mismatch (1 Viewer)

[Jimcb8]

I really messed up and am looking for your thoughts to solve problem

I designed a table called FAMILIES
ClientID text This field is the ID for the entire family composition
Lname text
Fname text
Gender Integer (Which should have been text)
(This field is linked to a drop down box table where the only permissible values are Male or Female) the field in the drop down table was correctly defined as text lets call it field M/F


If I listed the table FAMILIES
The Gender field show either Male or Female correctly

Months later I was asked to sum all the males and females in a family.
I used Dcount and entered the criteria of the ClientID AND "Male".
Trying to get the number of males in a particular family.
After 3 days of using every possible syntax for the Dcount function I could not get it to work.
I then deleted all the relations ships to the table FAMILIES and changed the Gender field to Text as it should have been from the beginning.
I am thinking that since I defined it incorrectly to begin with, it is stored in the database as an integer representation of the Male value.
This is the last attempt which returns a value of zero males, which is wrong.


Private Sub Form_AfterUpdate()
Dim malesx As String
male = male
MsgBox "[ClientId]= '" & Forms!HomePage!NavigationSubform.Form!ClientID & "'"
malesx = Nz(DCount("[Gender]", "Household Information", "[ClientId]= '" & Forms!HomePage!NavigationSubform.Form!ClientID & "' AND [Gender]= 'male'"))
MsgBox malex
Forms!HomePage!NavigationSubform.Form!.[#MalesHousehold] = malesx
End Sub

Really at a loss not sure what to do. If you can help I really would appreciate it very much.

Jim

 

[jdraw]

What is this line for??

male = male

Use code tags; indent your code

If you don't have a copy of the free utility MZTools, you should get it.

 

[Galaxiom]

Looks to me like you main problems are typographical mistakes that would be picked up if you used Option Explicit.

BTW Sex is usually stored as an integer.
http://en.wikipedia.org/wiki/ISO/IEC_5218

 

[spikepl]

Storing sex sounds nerdy?!

 

[Galaxiom]

If ClientID is a number it should not have the quotes around it. That will be the type mismatch.

 

[Galaxiom]

Storing sex sounds nerdy?!

But you never know when you might need some later?

 

[Jimcb8]

jdraw
Male=male was an attempt to force a conversion from text to integer and visa versa.
the type mismatch occurs in the comparison of "[Gender]='Male'"
also tried "[Gender]=Male"
both did not work.
Galaxiom
[ClientId] is defined text see original problem definition
I did not know about the standard. I am a novice to access 2010
(This application was written for my church which runs a Food Pantry to feed the hungry. I never wrote applications in access before.)

malesx = Nz(DCount("[Gender]", "Household Information", "[ClientId]= '" & Forms!HomePage!NavigationSubform.Form!ClientID & "' AND "[Gender]= 'Male'""))

I will try Option Explicit

Thank you all for your help. I will keep trying.
Jim

 

[Jimcb8]

This is my latest attempt which results in a compile error
Private Sub Form_AfterUpdate()
Dim malesx As Integer
MsgBox "[ClientId]= '" & Forms!HomePage!NavigationSubform.Form!ClientID & "'"
malesx = Nz(DCount("[Gender]", "Household Information", "[ClientId]= '" & Forms!HomePage!NavigationSubform.Form!ClientID & "'" AND [Gender]= '"Male"'))
MsgBox malex
Forms!HomePage!NavigationSubform.Form!.[#MalesHousehold] = malesx
End Sub

The compile error occurs on the 1st quote after the [Gender]= '
expected list separator or )

Thank you all so much for your help
JIm

 

[Jimcb8]

I am attempting to have two string criteria connected with and for example

[ClientId]=clientid on form AND [Gender]=Male
when the syntax is correct the answer is wrong
???????

 

[jdraw]

See this ASAP

You'll save yourself a lot of frustration.

 

[Galaxiom]

MsgBox malex will throw an undefined variable which would prevent compilation if Option Explicit is on.

It is a bad practice to use a special character such as # in an object name.

 

[spikepl]

Further to JAck's remarks, you are making the normal rookie mistake by attempting too much in one go. If a complex expression does not work then make bit of it work one at a time. Once that works then add a bit.

For a Dlookup:

Dim myCriteria as String

myCriteria="SomeOtherField=" & SomeValue

Debug.Print myCriteria 'this lets you inspect what the criteria string looks like,  in the Immediate window (below the code window)

SomeValue=Dlookup("SomeField","SomeTable", MyCriteria)

 

[Jimcb8]

Thank you all for your thoughts.
I guess I need to think about problem some more.

I appreciate your input will eventually get it to work

Jim