DCount issues.... again | Newbie | | Join Date: Jun 2008
Posts: 22
| | |
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.....
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: DCount issues.... again
- =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.
-
...
| | Newbie | | Join Date: Jun 2008
Posts: 22
| | | re: DCount issues.... again
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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: DCount issues.... again
Quote:
Originally Posted by 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. 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'")
| | Newbie | | Join Date: Jun 2008
Posts: 22
| | | re: DCount issues.... again
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??
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: DCount issues.... again
Quote:
Originally Posted by 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?? 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?
| | Newbie | | Join Date: Jun 2008
Posts: 22
| | | re: DCount issues.... again
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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: DCount issues.... again
Quote:
Originally Posted by 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. 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...
| | Newbie | | Join Date: Jun 2008
Posts: 22
| | | re: DCount issues.... again
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)
| | Newbie | | Join Date: Jun 2008
Posts: 22
| | | re: DCount issues.... again
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.
| | Expert | | Join Date: Sep 2007
Posts: 256
| | | re: DCount issues.... again
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
| | Expert | | Join Date: Sep 2007
Posts: 256
| | | re: DCount issues.... again
Quote:
Originally Posted by zeusspandex
....... 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
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: DCount issues.... again
Quote:
Originally Posted by sierra7 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.
| | Newbie | | Join Date: Jun 2008
Posts: 22
| | | re: DCount issues.... again
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.
| | Expert | | Join Date: Sep 2007
Posts: 256
| | | re: DCount issues.... again
Quote:
Originally Posted by 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. Try;
=DCount("[Repeatoffletter]","Contamination2","[JoinedAddress] = '" & [Forms]![Contaminated Inpu]![joinedaddress] & "' and [Repeatoffletter] = True")
S7
| | Newbie | | Join Date: Jun 2008
Posts: 22
| | | re: DCount issues.... again
Quote:
Originally Posted by sierra7 Try;
=DCount("[Repeatoffletter]","Contamination2","[JoinedAddress] = '" & [Forms]![Contaminated Inpu]![joinedaddress] & "' and [Repeatoffletter] = True")
S7
hmmm, this now returns "-1" any ideas?
| | Newbie | | Join Date: Jun 2008
Posts: 22
| | | re: DCount issues.... again
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...
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
