By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,641 Members | 2,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,641 IT Pros & Developers. It's quick & easy.

Count IF on MS Access

P: 7
How do you create a field on a form that counts the selected records, only if a certain word is in the field.

Records are selected via a combo box on a form header.
I then have tabs below that display different subforms.(One of the tabs is orders)

Basically i have combo box on the orders form that lists all posible locations for a particular item e.g.
"location 1 - sub location 1"
"location 1 - sub location 2"
"location 2 - sub location 1"
"location 2 - sub location 2"

i want to count all the records that have "location 1" at the start and display the quantity in a field on the orders form
Feb 11 '08 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,634
How do you create a field on a form that counts the selected records, only if a certain word is in the field.

Records are selected via a combo box on a form header.
I then have tabs below that display different subforms.(One of the tabs is orders)

Basically i have combo box on the orders form that lists all posible locations for a particular item e.g.
"location 1 - sub location 1"
"location 1 - sub location 2"
"location 2 - sub location 1"
"location 2 - sub location 2"

i want to count all the records that have "location 1" at the start and display the quantity in a field on the orders form
This would be the general idea:
Expand|Select|Wrap|Line Numbers
  1. Me![txtQuantity] = DCount("*", "<Your Table Name>", "Left([<Location Field>], 10) = 'Location 1'")
Feb 12 '08 #2

P: 7
This would be the general idea:
Expand|Select|Wrap|Line Numbers
  1. Me![txtQuantity] = DCount("*", "<Your Table Name>", "Left([<Location Field>], 10) = 'Location 1'")
Cheers for the quick response ADezii.

I've been trying to get that code to work but unfortunatly i'm not very good when it comes to visual basic and i haven't been successful yet.

Can you give me step by step instructions, or is their another way of doing it e.g. expression builder or a query

P.S. forgive me if i sound stupid, but i am!
Feb 12 '08 #3

ADezii
Expert 5K+
P: 8,634
Cheers for the quick response ADezii.

I've been trying to get that code to work but unfortunatly i'm not very good when it comes to visual basic and i haven't been successful yet.

Can you give me step by step instructions, or is their another way of doing it e.g. expression builder or a query

P.S. forgive me if i sound stupid, but i am!
I'll need your Table Name, Location Field Name in the Table, and the Location Field Name on the Form, and the Quantity Field Name.
Feb 12 '08 #4

P: 7
I'll need your Table Name, Location Field Name in the Table, and the Location Field Name on the Form, and the Quantity Field Name.
Table name is "Scaling", field name in the table is "Location Name", field name on the form is "Location Name" and the quantity field name is "Quantity"

Cheers for the help ADezii
Feb 13 '08 #5

ADezii
Expert 5K+
P: 8,634
Table name is "Scaling", field name in the table is "Location Name", field name on the form is "Location Name" and the quantity field name is "Quantity"

Cheers for the help ADezii
Try:
Expand|Select|Wrap|Line Numbers
  1. Me![Quantity] = DCount("*", "Scaling", "Left([Location Name], 10) = '" & Me![Location Name] & "'")
Feb 13 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.