Any DCount Experts want to help me out?

[Talismanic]

I am having trouble getting this DCount to work right. Here is the critera that I am trying to check for and how.

Tables name is Rates
Fields inside table are JobNo, Name, Time and Class

In the form I am working with I want to do a Dcount when txtPW = "Y" and the Class is not "Nor". I know there is something wrong with the logic in my code:

If txtPW = "Y" And Me!CLASS <> "Nor" Then
       If DCount("[Class]", "Rates", "[JobNo]='" & Me![CLASS] & "'") = 0 Then
              MsgBox "no match"
       Else
              MsgBox "found match"
       End If
End If

I am getting the "no match" message whether there is one or not. What am I doing wrong?




[This message has been edited by Talismanic (edited 07-11-2001).]

 

[charityg]

Is JobNo numeric? if so you probably don't need the quotes, so
If DCount("[Class]", "Rates", "[JobNo]=" & Me![CLASS]) = 0 Then

OR
I never test for DCount =0, but alway if dcount>0. So I would use something like
If txtPW = "Y" And Me!CLASS <> "Nor" Then
If DCount("[Class]", "Rates", "[JobNo]='" & Me![CLASS] & "'") > 0 Then
MsgBox "found match"
Else
MsgBox "no match"
End If
End If


Anyway, I'm no "DCount" expert, I hardly EVER use it, but this is what I would do if I were in your shoes.

~Charity

[This message has been edited by charityg (edited 07-11-2001).]

 

[Talismanic]

Is JobNo numeric? if so you probably don't need the quotes

Job numbers are actualy alphanumeric like this K5377-

I tried it like

If DCount("[Class]", "Rates", "[JobNo]='" & Me![CLASS] & "'") > 0 Then
MsgBox "Found match"
Else
MsgBox "No match"
End If

Now it is evaluating to false each time because I keep getting "No match".

There is something wrong with this [JobNo]='" & Me![CLASS] part of the code. There is no way for Access to match the JobNumber in the form I am working on with the JobNo in the Rates table. But if I don't use the [Class] how will Access know which Class to check for. I can not get my head around this!

[This message has been edited by Talismanic (edited 07-11-2001).]

 

[Jack Cowley]

If DCount("[Class]", "Rates", "[Class]='" & Me![CLASS] & "'") > 0 Then

If I understand what you want to do then this should do it....

 

[The_Doc_Man]

I am not sure because there is something a bit odd in your explanation, but I think you are asking DCount the wrong question, maybe. Take a very close look at the double-quotes and how I use them. There is no single quote in any of the strings I provide below.

Dim loCount as long
Dim stWhere as string
...
if ( txtPW = "Y" ) and ( Me![Class] <> "Nor" ) then
stWhere = "[JobNo]= """ & Me![Class] & """ "
loCount = DCount( "[Class]", "Rates", stWhere )
if loCount = 0 then
msgbox "No matches"
else
msgbox "Found " & CStr$(loCount) & " matches"
end if 'end - if loCount = 0
end if 'end - if outer qualifiers OK

 

[Talismanic]

Doc Man, I have almost given up on this. I think you are right about not asking the DCount the right question but for the life of me I can't get it to evaluate differently based on the contents of the record. I either get all trues or all falses.

Maybe I am using the DCount completely wrong. Let me ask the question this way. Here is my table:

5259-,KENT CITY ELEMENTARY,ST,AC1,19.77
5259-,KENT CITY ELEMENTARY,ST,AC2,19.77
5260-,THREE RIVERS ELEMENTARY,ST,AC3,19.77
5260-,THREE RIVERS ELEMENTARY,ST,AC4,19.77

The first field is the job number the second is the job name. The field with the AC1, AC2 etc.. is the class field. In the form I need to check this table (Rates) to see if Job Number 5259 has a Class field that equals the one in the form. The class fields in the form are limmited to a list and you can only choose items that are in the Rates table.

Does that help any?

 

[pcs]

tal,

sure not a dcount guru...but it looks like you are trying to do a Dcount based on a single where operation on [JobNo] and [Class].

think you need a big fat 'AND' in your dcount expression.

see below,

DCount("[ShippedDate]", "Orders", "[ShipCountry] = '" _
& Forms!Orders![ShipCountry] & "'AND [ShippedDate] > #1-1-95#")

hth,
al

an afterthought, sometimes when i'm very lazy (most of the time) and don't want to fight getting the criteria syntax of domain aggregates correct, i write a select query that references the fields on my form. then i do a very simple dcount against the query to see if it returned any records.




[This message has been edited by pcs (edited 07-16-2001).]

 

[Jack Cowley]

Since you have two Job Numbers with a different class in each one you do have to look for the JobNo and Class to see if there are any records that meet that requirement. This is probably what you are looking for:

DCount("[Class]", "Rates", "[Class]='" & Me![Class] & "'" And [JobNo] = " & Me.JobNo & ")

 

[Talismanic]

Thanks to everyone who tried to help me out with this but I have officialy given up on it. I can not get it to work the way I want it to.

 

[Rich]

I'll have a go, though you'll have to send some samples in 97.

 

[Jack Cowley]

Don't give up! Let Rich have a go at it. And I am willing to give it a go as well. Can't guarantee that I won't give up too, but no harm in trying. If you want to do this it would be best to email me directly....

[This message has been edited by Jack Cowley (edited 07-17-2001).]