Substituting 'hard values' with variables in DCOUNT (1 Viewer)

lioninwinter

New member
Local time
Today, 10:25
Joined
Apr 11, 2020
Messages
21
0
I am relatively new Access vba user and having problems with the synatax for the use of DCOUNT with variables. I am constructing a simple routine to look for duplicates in the [EventShort] field of a table called tblEventsi. The first step is to count the occurrences of each of the strings in EventShort using a loop. In order to be able to use this routine with other tables, I am assigning variables to the table name (myTN) and table field (myTF) and then placing these variables in the DCOUNT function. I have managed to work out the syntax for the first occurrence of TF as variable in the first 'argument' of DCOUNT but not the syntax for inserting the table name as a variable as second argument or TF in the third argument of the function. I suspect my grammar is askew - can anyone help? Many thanks, David

CODE AS FOLLOWS

'Assigns value to TN (Table Name) variable

myTN = "tblEventsi"

'Sets myT to value of myTN

Set myT = CurrentDb.OpenRecordset(myTN)

'Loops through myT

Do Until myT.EOF

With myT

'Assigns value to TF (Table Field) string variable

myTF = "EventShort"

myTFV = myT.Fields(myTF).Value

'Assigns count of myTFV in myTF to variable DC (Duplicate Count)

(1) WORKS: DC = DCount("[EventID]", "tblEventsi", "[EventShort] ='" & myTFV & "'")

(2) WORKS: DC = DCount("[" & myTF & "]", "tblEventsi", "[" & myTF & "] = " & "' & myTFV & '")

(3) DOES NOT WORK: DC = DCount("[" & myTF & "]", "tblEventsi", "'" & myTF & "' = " & "' & myTFV & '")

(4) DOES NOT WORK: DC = DCount("[" & myTF & "]", "tblEventsi", "'" & myTF & "' = " & "' & myTFV & '")

End With

Next EOF

Loop
 

Ranman256

Well-known member
Local time
Today, 05:25
Joined
Apr 9, 2015
Messages
4,339
always use:
string var:
= DCount("[EventID]", "tblEventsi", "[EventShort] ='" & myTFV & "'")

numeric:
= DCount("[EventID]", "tblEventsi", "[EventShort] =" & myTFV )

but you shouldnt have to loop thru records. Thats what update queries are for.
 

lioninwinter

New member
Local time
Today, 10:25
Joined
Apr 11, 2020
Messages
21
Thank you for this but what is the syntax for placing a string variable in arguments 1 and 2 in DCOUNT i.e. in place of "[EventID]" and "tblEvents"? I want to replace these two 'hard' values with variables myTF, myTN whose values would change depending on which field name / table name I assign to the variables.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:25
Joined
Feb 19, 2002
Messages
42,981
Why not just use a totals query to count all the variables at once? Or maybe a crosstab query? You don't need a VBA loop
 

lioninwinter

New member
Local time
Today, 10:25
Joined
Apr 11, 2020
Messages
21
Why not just use a totals query to count all the variables at once? Or maybe a crosstab query? You don't need a VBA loop
The rest of the sub will rename the values 1, 2, 3 etc depending on how many duplicated values there are. This is only step 1
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:25
Joined
Feb 19, 2002
Messages
42,981
Then run another query to sequence them or use a code loop if you can't figure out the sequence.
 

lioninwinter

New member
Local time
Today, 10:25
Joined
Apr 11, 2020
Messages
21
I would still want the code to be driven by values I plug in as variables so I don't need to keep constructing queries. I am a little more comfortable in VBA than SQL. There will be other times when I'll want to replace a hard value of a table or a field with a variable, so all I'm looking for is the syntax
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:25
Joined
Feb 19, 2002
Messages
42,981
Do what ever your heart desires. Write all the unnecessary code you want to write.
 

Ranman256

Well-known member
Local time
Today, 05:25
Joined
Apr 9, 2015
Messages
4,339
use a form, put your values in the textboxes on the form,
run update query that uses them.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:25
Joined
Sep 21, 2011
Messages
14,050
Can't help feeeling you are trying to run a marathon here, before learning to walk? :)
I am all for functions, but.....?

I would try and build a string and then try Dcount(str) where str is all the normal parameters for the function?
As the " is going to cause a lot of grief trying to get the syntax correct, try using chr(34) instead.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:25
Joined
May 7, 2009
Messages
19,171
3. and 4. will not work.
you are making the variable as Literal string:

(3) DOES NOT WORK: DC = DCount("[" & myTF & "]", "tblEventsi", "'" & myTF & "' = " & "' & myTFV & '")

(4) DOES NOT WORK: DC = DCount("[" & myTF & "]", "tblEventsi", "'" & myTF & "' = " & "' & myTFV & '")
 

lioninwinter

New member
Local time
Today, 10:25
Joined
Apr 11, 2020
Messages
21
Can't help feeeling you are trying to run a marathon here, before learning to walk? :)
I am all for functions, but.....?

I would try and build a string and then try Dcount(str) where str is all the normal parameters for the function?
As the " is going to cause a lot of grief trying to get the syntax correct, try using chr(34) instead.
Many thanks for this constructive comment - yes I should do some walking first
 

sonic8

AWF VIP
Local time
Today, 10:25
Joined
Oct 27, 2015
Messages
998
(1) WORKS: DC = DCount("[EventID]", "tblEventsi", "[EventShort] ='" & myTFV & "'")

(2) WORKS: DC = DCount("[" & myTF & "]", "tblEventsi", "[" & myTF & "] = " & "' & myTFV & '")

(3) DOES NOT WORK: DC = DCount("[" & myTF & "]", "tblEventsi", "'" & myTF & "' = " & "' & myTFV & '")

(4) DOES NOT WORK: DC = DCount("[" & myTF & "]", "tblEventsi", "'" & myTF & "' = " & "' & myTFV & '")
I'm not sure whether this is the only problem, but one issue is:
You should enclose table and field names in square brackets, but string criteria values in single quotes. myTF in the where condition is a field name!
 

Users who are viewing this thread

Top Bottom