DCount with variables - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

DCount with variables

Kim Mittet

3 years ago

In VBA i want to use DCount using variables for the field and for the condition, but i need help with the syntax.
I have not been able to find any examples for the use of variables in the fieldname.

Field in form : NumberOfPost
Variable for the fieldname : Fieldname1
Tablename: XXXXXX
In condition i want to filter on Fieldname1 : Fieldname1 <> 0
AND a field in the table containing the month number :
Monthnumber = Month(NOW))

Something like this but without the prober syntax:
NumberOfPost = DCount(Fieldname1, XXXXXX, MonthNumber = Month(NOW)) AND Fieldname1 <> 0

Can someone set up the DCount sentence to put in VBA with the right syntax ?

	Scott Axton @Reply 3 years ago
Concatenation NZ Function Search Form 2.0 Access Search Seminar

	Stefan Pinne @Reply 3 years ago
Try DCount("*", "XXXXXX", "MonthNumber = Month(NOW) AND Not IsNull( & Fieldname1 & )")

	Kevin Robertson @Reply 3 years ago
Kim, Can you explain what you are actually trying to do and why you need a variable in place of the field name. Would also be useful to know what kind of data the variable will hold (String, Number, Date). Stefan, Your syntax is slightly off. `DCount("*", "XXXXXX", "MonthNumber=" & Month(NOW()) & " AND " & Not IsNull(Fieldname1))`

Kevin Yip @Reply

3 years ago

Hi Kim, just to be clear, if Fieldname1 is a string variable whose value is a field name in a table, and "Monthnumber" is the actual name of a field in the table, then Fieldname1 needs to be used via string concatenation, but "Monthnumber" doesn't:

NumberOfPost = DCount("*", "XXXXXX", "MonthNumber = Month(Now) And [" & FieldName1 & "] <> 0")

"[" and "]" is used above in case the field name contains spaces.

	Kim Mittet OP @Reply 3 years ago
Sorry for the late reply. @Kevin - your proposal did the trick - working as expected :-) Thank you everyone for your replies.

Kim Mittet OP @Reply

3 years ago

I have found that i need to be able to go back one month in another situation.
For that I want to use a variable, holding the result: Month(now())-1.
The value is stored in the variable PreviusMonth
Please do not suggest to make the calculation in the DCount code - like something : Month(NOW())-1
I need it to be the variable which I put in the line :-)
Something like:
NumberOfPost = DCount("*", "XXXXXX", "MonthNumber = PreviusMonth And [" & FieldName1 & "] <> 0")
Once again I am not sure about the syntax

	Kevin Robertson @Reply 3 years ago
Is it just the syntax you are looking for? `Dim previousMonth As Long, numberOfPosts As Long previousMonth = Month(Now()) - 1 numberOfPosts = DCount("*", "XXXXXX", "MonthNumber=" & previousMonth & " And [" & FieldName1 & "] <> 0")`

Kim Mittet OP @Reply

3 years ago

My believe was that i could use the syntax i the above for the below, but i guess i was wrong :-)
I get the error message "Datatypes is not correct in the criteria expression"
I hope you can ignore the language is not english in some of the expression :-)

Me.AntalBudgetPosterMdr = DCount("*", "AntalBudgetPosterQ", "Budget'r=" & 'rstal & " AND [" & previousMonth & "]<>0 AND [BudgetkontoRelateret] = TRUE")

Me.AntalBudgetPosterMdr is a unbound formfield
"Budget'r" is a field of format String
"'rstal" is a variable of format String
"previousMonth" is a variable holding a fieldname
"BudgetkontoRelateret" is a field holding a boolean value

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View