dcount or dsum or d?

[swarv]

I have the following code which works fine:

LTotal = DCount("Name", "Absent", "Name = combo136.value")

and also the following code which works fine:

LTotal2 = DSum("Days", "Absent", "Name = combo136.value")

both give me the answers I want but,

in the absent table there is a column called Day (not days) and I want access (vba) to find all the occurances where combo136.value appears in the name field and 1 appears in the day column and where 1 does appear then show the value in the Day field.

I hope this makes sense.

Thanks

Martin

 

[RuralGuy]

I don't see how either of the two lines of code will work correctly. Aside from the fact that Name is a reserved word in Access, you've included the local control combo136.value within the quotes. In order to work correctly, it should be outside of the quotes:

LTotal = DCount("Name", "Absent", "[Name] = '" & Me.combo136 & "'")

 

[swarv]

many thanks for your reply. I'll change that.

But the main bit i am strugaling with is:

in the absent table there is a column called Day (not days) and I want access (vba) to find all the occurances where combo136.value appears in the name field and 1 appears in the day column and where 1 does appear then show the value in the Day field.
Is this possible?

Thanks

Martin

 

[namliam]

Day is a reserved word, like name... adviced not to use them.

If you do... like Rural already said enclose them in []

Also if you are doing multiple DSum or DCount-s on the same table, you are much better of running a query!

 

[swarv]

ok many thanks - i'll change the day and name words. Just changing them now.

but also any ideas on?
in the absent table there is a column called Dayofweek and I want access to find all the occurances where combo136.value appears in the nameofuser field and 1 also appears in the daysoff column and where 1 does appear then show the value in the Dayofweek field.

Is this possible?

thanks

 

[namliam]

Dayofweek sounds like a calculated field.. if you have the raw date someplace you really should nto have this.

I am not sure I understand your question though

 

[swarv]

cheers.

I know the question isn't a simple one.

I'll try to explain more clearly.

I have an absent table with columns: Dayfoweek, nameofuser and daysoff.

I would like either a query or command button to do this:
when somebody chooses a name from the combo136 box they press the command button under it and then access looks through the absent table and finds all the fields that have that value listed in the nameofuser column, then when it finds a match it looks in the daysoff column and if that has a 1 in it then access returns the value in the dayofweek column.

But for example it could find 4 Mondays, 3 tuesdays etc... so I would like it place all of what it finds somewhere, maybe text box or list somehow.

Is this possible?

Thanks

 

[gemma-the-husky]

have a query based on the table showing all the fields

look at help, and play around with this query to filter out the day/username you want etc - make it a totals query to get counts - loads you can do with this. put things in the criteria rows (eg the nameofuser, and/or the dayofweek) to see how the query changes

you are not restricted to just 1 value in the criterias - you can add several
eg name1 or name2
eg like "Fred*"
eg between 1 and 4
etc

just play with the query so you can see the different effects - this exercise will be far more useful for you than a fix without appreciating what is going on.

now, in the criteria for the nameofuser, instead of entering a name directly, put a reference to the combo box on your form

forms!formname!comboboxname

(this may not be exact syntax)

now instead of all the names being analysed, you will just get the selected one, and you can reuse the query without having to edit it each time.

etc

 

[swarv]

many thanks - thats helped a lot - think i have it sussed. cheers

 

[RuralGuy]

Here's a good link for checking if a word is reserved:
http://www.allenbrowne.com/AppIssueBadWord.html