DCount issues.... again

[zeusspandex]

Hello everyone

I have been useing Access for a while now and have attempted useing DCount a number of times but with varying results.

I am currently trying to have a field in a form that will show how many times a particular address has been entered already and has a record in a table. So i was hoping that when the user enters an address the DCount field will show how many times it has already been entered into the table. The following code was used to to no avail

=DCount("Contamination2!Enq","Contamination2","Con tamination2!JoinedAddress=Forms![Contaminated Inpu]![joinedaddress]")

Contamination2 = Table
Contamination2!Enq = field to count (although i beleive this can change to any field in the table?)
Forms![Contaminated Inpu]![joinedaddress] = the filed on the form i want to use as the criteria.

Thanks in eager anticipation.....

[ADezii]

=DCount("*", "Contamination2", "[JoinedAddress] = '" & Forms![Contaminated Inpu]![joinedaddress] & "'")

NOTE: Be advised that it is nearly impossible to return all matches for addresses in the manner you specify, since there are many variations, e.g. there would not be a single match on any of the following addresses:

1313 Mockingbird Lane
1313 Mockingbird Ln
1313 Mockingbird Ln.
1313 Mockingbird La
1313 Mockingbird La.
1313 Mockingbrd Lane
1313 Mockngbird La.
atc.
atc.
etc.
...

[zeusspandex]

Thanks for that

I have already realised this and have used various functions to ensure as much conformity as possible. all capital letters for example.

[ADezii]

Thanks for that

I have already realised this and have used various functions to ensure as much conformity as possible. all capital letters for example.

Capitalization is not the consideration, since all 4 Expressions below will yield the same results in the Northwind Database, namely 5.

'all yield 5

? DCount("*", "Employees", "[Region] = 'WA'")
? DCount("*", "Employees", "[Region] = 'wa'")   
? DCount("*", "Employees", "[Region] = 'Wa'")
? DCount("*", "Employees", "[Region] = 'wA'")

[zeusspandex]

I see. i suppose a little encouragement to the users to input uniform data may be my best hope?

I need to develop the DCount function a little more by counting how many times a letter has been sent for the address in the text box. So useing the DSCount solution from above the addition maybe somthing like this:

=DCount("[Repeatoffletter] = Yes","Contamination2","[JoinedAddress] = '" & [Forms]![Contaminated Inpu]![joinedaddress] & "'")

Any ideas??

[ADezii]

I see. i suppose a little encouragement to the users to input uniform data may be my best hope?

I need to develop the DCount function a little more by counting how many times a letter has been sent for the address in the text box. So useing the DSCount solution from above the addition maybe somthing like this:

=DCount("[Repeatoffletter] = Yes","Contamination2","[JoinedAddress] = '" & [Forms]![Contaminated Inpu]![joinedaddress] & "'")

Any ideas??

An Address should 'never' be listed more than once for a given User unless he/she has multiple residences, which would then be in a related Table. The number of Letters and related info such as: Date Sent, Content, Confirmation, etc. can be contained in a related Table in a MANY ==> 1 Relationship with the User info Table. Make sense?

[zeusspandex]

hmmm, i think i get you. The only problem is, i will be adding 60 or so new or already recorded addresses everyday. So im not sure how to work it all.

each day i will be given a list of address that have a contaminated waste bin. I want to be able to each day input the address and as i do this a text box will show how many times the address has already been entered and how many times a specific letter has been sent to that address.

Sorry if im sounding super novice by the way.

[ADezii]

hmmm, i think i get you. The only problem is, i will be adding 60 or so new or already recorded addresses everyday. So im not sure how to work it all.

each day i will be given a list of address that have a contaminated waste bin. I want to be able to each day input the address and as i do this a text box will show how many times the address has already been entered and how many times a specific letter has been sent to that address.

Sorry if im sounding super novice by the way.

I still feel as though checking for duplicate Addresses in this manner is totally inefficient, and will also lead to erroneous results. Is there some Unique Identifier that specifically points to a User/Organization?

If you still insist on this approach, you will probably be better off querying the Table for Partial Matches in the [Address] Field, as in:

SELECT *
FROM Employees
WHERE Employees.Address Like "*" & [Enter Partial Address] & "*";

Positive: If you would plug in the partial criteria of 1313 when prompted, all entries in Post #2 would be returned. You would have a visual indication of Addresses meeting your Partial Search Criteria.

Negative: also returned would be the following Addresses:

1313 Mulberry Drive
496 River Road, Apt. 1313
13134 Southhampton Road
etc...

[zeusspandex]

The database is designed to cater for all addresses in a city wide area. So to have a unique identity for all 140,000 odd addresses in the city artea would require alot of data entry effort on my part. Also, not all address will have to appear in the database. All that i record at the moment is a record of a contamination. so each contamination is unique. This means i do not have to have a seperate table with each address in the city. I understand that searching for matching addresses this was may cause odd results but i feel there are a few things you can do to reduce that chance. mainly ensuring uniformity when entering the data but obviously this is not alwasy possible.

Either way, although i greatly appreciate the help you are giving me. i really would appreciate an answer to the original question stated above with regards to DCount.

so to count the number of times a letter has been send for the address which is displayed in the text box on the form.

thanks (humble)

[zeusspandex]

i have a further perhaps more complicated question for you........

Do you know of a way tp set the value of a check box depending on the date?

to explain. where i work our vehicles are set to work areas of a city in a sequence over two weeks. This means that one week they will work in certina areas and this is called A week. Next week they will work in different areas and this is called B week.

I want to be able to do this so that from the date a check box on the switchboard will change each week to represent A week or B week.

I dont want to have to create a huge table for dates going on far into the future. i simply want to set say this week as A and then access will automatically know that next week it will be B.

This may be a long shot and i realise it has nothing to do with the forum thread, but any input would be lovely.

[sierra7]

You can use Format(Date(),"ww") to return the Week Number.

You could then test whether it was Odd or Even (divide by two and see if there is a remainder), then perhaps proceed on that basis.

S7

[sierra7]

....... i really would appreciate an answer to the original question stated above with regards to DCount.

Your original DCount statement did not work because of your syntax in the statement. You need to concatonate the criteria to the parameter(s) you are matching, using ampersand (&) as in ADezii's first reply. You cant just use an equals sign unless you are literally specifying the parameter e.g. 'New York'. Note, he has also introduced single quotes because you are trying to match on a string not a number.

I have only briefly reviewed your task (ADezii has more than covered the reasons for not to proceed as you are doing) Can you use Post Code (Zip Code) to achieve your 'count'? I don't know the nature of your task but it seems to me that if your incidents were grouped by post code you could then browse them to see if they were the same address or not.

Clusters of incidents within an area might have more significance than individual addresses, maybe??

S7

[ADezii]

Your original DCount statement did not work because of your syntax in the statement. You need to concatonate the criteria to the parameter(s) you are matching, using ampersand (&) as in ADezii's first reply. You cant just use an equals sign unless you are literally specifying the parameter e.g. 'New York'. Note, he has also introduced single quotes because you are trying to match on a string not a number.

I have only briefly reviewed your task (ADezii has more than covered the reasons for not to proceed as you are doing) Can you use Post Code (Zip Code) to achieve your 'count'? I don't know the nature of your task but it seems to me that if your incidents were grouped by post code you could then browse them to see if they were the same address or not.

Clusters of incidents within an area might have more significance than individual addresses, maybe??

S7

The syntax in Post #2 would be correct, just make the necessary Field substitutions.

[zeusspandex]

Thanks for all that but im still having issues with the latest DCount problem. namely

=DCount("[Repeatoffletter] = Yes","Contamination2","[JoinedAddress] = '" & [Forms]![Contaminated Inpu]![joinedaddress] & "'")

i want to count how many times the address shown has the repeatofletter check box ticked. The above code does not work. it returns the number of times that address has been entered not how many times the check box has been ticked.

[sierra7]

Thanks for all that but im still having issues with the latest DCount problem. namely

=DCount("[Repeatoffletter] = Yes","Contamination2","[JoinedAddress] = '" & [Forms]![Contaminated Inpu]![joinedaddress] & "'")

i want to count how many times the address shown has the repeatofletter check box ticked. The above code does not work. it returns the number of times that address has been entered not how many times the check box has been ticked.

Try;
=DCount("[Repeatoffletter]","Contamination2","[JoinedAddress] = '" & [Forms]![Contaminated Inpu]![joinedaddress] & "' and [Repeatoffletter] = True")


S7

[zeusspandex]

Try;
=DCount("[Repeatoffletter]","Contamination2","[JoinedAddress] = '" & [Forms]![Contaminated Inpu]![joinedaddress] & "' and [Repeatoffletter] = True")


S7

hmmm, this now returns "-1" any ideas?

[zeusspandex]

woops, as you may have guessed, im prone in making errors. the code you gave me works great as long as you dont paste it in twice. thanks...