Retrieve a record count in VBA

[Killer42]

I'm just starting to read the SQL, and I'm uncertain about something. Keeping in mind I'm somehat rusty on this and was no SQL expter to begin with. But I thought the following looked wrong...

AND employee.sex = (SELECT employee.sex FROM

I thought this sort of things was done with the IN operator. In other words, something like:

AND employee.sex IN (SELECT employee.sex FROM

[crmc07]

I'm just starting to read the SQL, and I'm uncertain about something. Keeping in mind I'm somehat rusty on this and was no SQL expter to begin with. But I thought the following looked wrong...

AND employee.sex = (SELECT employee.sex FROM

I thought this sort of things was done with the IN operator. In other words, something like:

AND employee.sex IN (SELECT employee.sex FROM

That's where part of my problem was. I just noticed and fixed it about an hour ago. Now the new problem I'm facing is that the iCount is being passed a null which is interpreted as 0 so the ELSE statement will never run.

Dim iTotal As Integer
iTotal = DCount("[roommate]", "mainTable", "[mainTable.name] = '" & Me!name.Value & "'")
MsgBox (iCount)

I printed out a msgbox to see what was passed and it is blank. Any idea on how to fix this?

[crmc07]

I just wanted to thank everyone for thier help. I FINALLY got it to work (for now). It does exactly what i need it to do...Thanks again!!!

[Killer42]

I just wanted to thank everyone for thier help. I FINALLY got it to work (for now). It does exactly what i need it to do...Thanks again!!!

Glad to see you've got it sorted out.

One question, though. I notice that you appear to have made up variable name iCount in the MsgBox statement, where it should have been iTotal. Presumably you've now fixed this. But it highlights a bigger potential problem. You should configure VB set to require explicit variable declaration. That way, when you mistype a variable name, or make one up on the fly like this, VB will spot the error and refuse to compile until you fix it. Do you have it configured this way?

[crmc07]

Glad to see you've got it sorted out.

One question, though. I notice that you appear to have made up variable name iCount in the MsgBox statement, where it should have been iTotal. Presumably you've now fixed this. But it highlights a bigger potential problem. You should configure VB set to require explicit variable declaration. That way, when you mistype a variable name, or make one up on the fly like this, VB will spot the error and refuse to compile until you fix it. Do you have it configured this way?

Yeah i did figure that out. After looking at code for so long it all tends to run together after a while.