Control Source Error ?

[bellevue]

Can anyopne see where I might be going wrong with this please ?

=IIf([Dogs Name] Is Not Null And [Dogs Name 1] Is Null,1,IIf([Dogs Name 1] Is Not Null,2,0))

Where the [Dog Name] field is empty, it's telling me there are 2 dogs

plus

Where the [Dog Name] field has a dogs name in it, it's telling me there's 1 dog and for some records it's saying there are 2 dogs !



Any help would be appreciated.

Thanks

[Missinglinq]

Parsing nested IIFs is always a pain, and in this situation I'd prefer
Creating a Function in a Standard Module
Doing the work using the If...Then...Else construct, then Calling it from the Control Source Property
But the glaring error I see here is

([Dogs Name] Is Not Null And [Dogs Name 1] Is Null

The correct syntax needs to be

(Not IsNull([Dogs Name]) And IsNull([Dogs Name 1]

Linq ;0)>

[alansidman]

Is it possible you have an empty string in some of these records. Null indicates that there is nothing there. An empty string, is not null. An empty string would visually appear to be null as you don't see anything.

[bellevue]

Thanks for that Missinglinq but I'm not sure where to put the correct syntax you suggested into the whole thing, could you re-write the whole thing with the correct syntax please ?

Thanks

[bellevue]

Thanks alansidman

I'm not sure I understand. What's a string ?

I've included a screen print of the form below to show you what I'm meaning:



For each booking there may be a dog, two dogs or no dogs. For each dog, the fields for the dogs details will be entered, what I want to do is write an iif statement in the field "Qty Dogs" that counts the number of dogs (if any) for the booking.

I hope that helps.

[orange]

Please show your tables and relationships adn your table layouts.
It appears that you may have a Normalization (table structure) issue.

You have at least 3 tables
Dog(DogId,DogName, DogOwner, DogBreed, DogGender,....)
Booking(BookingId,BookingDate,.....)
BookedDog(BookedDogId, BookingId_FK,DogId_FK,........)

Underlined field are PK
Green Fields are compound unique Index (no duplicates)

[bellevue]

Thanks orange

I've been this road before where people have asked me to show my tables and relationships and when I do, there doesn't seem to be a solution and I'm being told that my relationships are wrong.

There is one table (ReservationsCBV) "powering" the form (ReservationsCBV); the field labels (and the field names shown in brackets) shown in the screen capture are:

Dogs Name ("Dogs Name 1" and "Dogs Name 2")
Breed ("Breed" and "Breed 1")
Age (Dog Age" and "Dog Age 1")
Gender ("Gender" and "Dog Gender 1")

What does "PK" mean ?
What do you mean by "Green fields" ?

Thanks

[orange]

PK means primary key see http://databases.about.com/cs/admini...primarykey.htm

Green fields simply means the fields I colored green in my post. The two fields are used to make a compound unique index (no duplicates allowed).

Based on your questions, I recommend you do some reading on Normalization.

There is an excellent article attached to the first post at
http://forums.aspfree.com/microsoft-...es-208217.html

You need to research data base design.
There are many aticles at http://www.rogersaccesslibrary.com/forum/topic238.html
see normalization, normal forms and entity relationship diagramming.

[alansidman]

An empty string is one in which there is a space or non-visible character. It is contains no data but contains non-visible ascii characters.