IFF statement

[OZmc]

I have a query that totals the number of records related to each person.

In my form I would like to have a unbound text box display the number of records associated to that person.

so i need a IF statement to do the following

if the query field holding names is equal to the form name field, then the target text box equals the value held in querys record_count field.

so something like:

If [query]![person_name] = [form]![person_name] Then
[textbox?] = [query]![record_count]

End If

but Im not sure how to write the complete code in VBA

 

[CJ_London]

Rather than using vba why don't you set the control source of your unbound text box to:
=DCount("*","QueryName","[QueryNames] = " & [FormNames])

 

[OZmc]

great thanks for the help!

I unfortunatly get a #error. Whats odd is if chage the form field name (record/name) to another field on my form I get a correct calculation of 0 in my text box.

here is what I have:

=DCount("*","record_count","[Name] = " & [Record/Name])

 

[CJ_London]

A few things:

Name is a reserved word and although it probably won't affect this I recommend you change it to something else going forward - e.g. QName

Similarly it is better not to include forward and back slashes in field names

but to answer your question, I forgot to include quotation marks so it should be like this:

=DCount("*","record_count","[Name] = '" & [Record/Name] & "'")

 

[CJ_London]

Had a response in my email, but can't see it on the forum so ignore the following if not relevant.

From the way your Dlookup is structured, you have a query called 'Record_Count' which contains as a minimum a field called 'Name' and there are a number of records - say 10 for Name1 and 20 for Name2. You also have a form which has a recordsource a table or query which has a field called 'Record/Name'

If this is correct, just to eliminate it as a possible error I would change the name field in your query to something else - e.g. QName since Name is a reserved word

Next I would check your form control for Record/Name - is that what it is really called? Check both the ControlSource and Name properties.

One other thing to try is to take away the square brackets around Record/Name in the Dlookup

 

[OZmc]

I cant tell you how much I appreciate this. I also cant imagine how difficult it is to debug something you cant see yourslef.

I made the changes you suggested. Changed the names so that they arent conflicting with reserved words and such.

It turns out that one of my tables was recording the "names" as numbers and not text. its fixed now.

Anyhow it looks like the expression is working now, but instead of returning 3 (when you run the query there are 3 results with the same name) it returns 0

Do you think its a problem with my expression or the structure of my database?

 

[CJ_London]

I just like solving problems!

Syntactically(?) it is correct so a couple of checks

If it is returning 0 then that implies it isn't finding a record which means the Record/Name value is not what you expected. If record/name is now a number then you do need to change the criteria part to "[Name] = " & [Record/Name] i.e. drop the quotation marks which are only required if it is a string value. If it is a string value then change "[Name] = '" to "[Name] like '". i.e. change the = sign to like.

Let me know how you get on, but signing off for today

 

[OZmc]

you were correct. One of the fields was named something else. I fixed it but im still only getting 0 as a result.

I worked with the coding and have this currently:

=DCount("*","Qcount","'Qname' like '" & [name_id] & "'")

Qcount = Query
Qname= Query field (Text)
name_id=Form Field (text)


Qname and name_id contain the same information

=Dcount("*","Qcount") and =Dcount("*","Qcount","Qname") work

its when i add the conditional argument at the end that it fails and returns 0

Were so close I can smell it.

 

[Brianwarnock]

I think it should be

=DCount("*","Qcount","Qname ='" & [name_id] & "'")

I assume that name_id is on the same form as the control that the dcount is the source for.

Brian

 

[OZmc]

I think I have discovered the source of all my problems. I neglected to mention that the information contained in Qname (the query field) is a dropdown list.

Doing some trouble shooting if I redirect the DCount function to another field that contains a string of text it works.

is this possible with info that is selected from a dropdown menu?

 

[CJ_London]

I agree with Brianwarnock re the Dcount code but your referencing doesn't sound right. I'm going to make up some names which you can either adopt or translate to whatever you have at the moment.

The dropdown list (called a ComboBox) is called CBQuery. I'm assuming it has only one column which is your list of query names.

The control containing the Dcount controlsource is called QResult

The query which contains the rows you want to count is called QCount

The name of the field in QCount which will match to the name selected in QResult is called QName

Given the above your QResult controlsource is =Dcount("*","QCount","[QName] = '" & CBQuery & "'")

It is possible that your CBQuery contains more than one column (e.g. an ID field and a name field). If this is the case, you can do 1 of 2 things:

1. Set the bound column (under the data tab) to the column number of the name field (column1 is the first column) and the above DCount will work or

2. If the bound column needs to be set to 1 and the name column is in column 2 then you need to change the DCount to the following:

=Dcount("*","QCount","[QName] = '" & CBQuery.column(1) & "'")

Note that perversely the Access GUI refers to the first column as 1, whilst VBA refers to the first column as 0. Also check that the column count column under the format tab is set to at least the column number of the name field in the combobox i.e. 2 in the above example.

You may need to requery the control which contains the dlookup. You do this in the afterupdate event of the combo box with the code:

QResult.Requery

Hopefully this should resolve the problem. If not you may need to post the form and sample of the query as a table for me to look at

 

[OZmc]

Sadly I think I will need someone to look at it. I was able to get the exact same DCount coding to work on a different form (looking at different fields) but when I apply the same coding to my target form I get an error. :banghead:

I attached my database and color coded things a bit so you can see what im working on.

The two fields im having troubles with in my Daily Records form are in red.

The two fields that i just tested the coding on and worked are in blue.


As you can see the names I have been giving you were just examples. I work at a vet clinic so I try not to disclose any information. The patients in the database are fake ones i just made up.

1) The form I am working on is Daily Records
2) The tables I am working with are Herp_logs and room_id
3) I was originally working with a query, but then I realized that I could just use the source table instead.

 

[CJ_London]

Hi OZmc,

Finally figured it. There are two problems.

First with your DCount you have =DCount("[Record_num]","Herp_logs","[Record_num] = '" & [cage_id] & "'")

At first glance, Dcount should actually be =DCount("[Record_num]","Herp_logs","[Enclosure] = '" & [cage_id] & "'")

However the more serious 'crime':banghead: is that you are using a lookup in your Herp_logs table to display 'A1' whereas the actual value is 18 so it is returning 0 because it can't find any records where A1=18 (not surprising really). This is a really bad practice because it can cause issues such as this.

Haven't checked to see if you have done this elsewhere, but if you have - remove these lookups!

In the meantime, the corrected Dcount is =DCount("[Enclosure]","Herp_logs","[Enclosure]= '" & [ID] & "'")

Ideally, the Enclosure field type should now be changed to a number (long) since it will make linking much simpler in the future and easier to decode otherwise you will have constant issues trying to match numbers to strings.

Once you have done this change (you'll need to rebuild your relationships), you will then need to change the Dcount to =DCount("[Enclosure]","Herp_logs","[Enclosure]= " & [ID]) i.e. remove the quotes since Enclosure it is no longer a string.

I've made the changes in the attached db but not converted from text to number.

Hopefully you can now move on in peace!

CJ

PS - one of my friends has an animal training centre for film etc. Lions, tigers, crocs, the lot. Like a small zoo!

 

[Brianwarnock]

Ah! The dreaded table lookup. I recently helped on another thread 3 or 4 of us could not figure out what was going on until the use of table lookups came to light, although the field displays the value expected it actually contains the primary key from the lookup, as shown by CJ_LONDON.

The experts on here say Table Lookups should never be used, they only exist in Access and cause problems as you have discovered.

Brian