Substituting 'hard values' with variables in DCOUNT

[lioninwinter]

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]

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]

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]

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]

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]

Then run another query to sequence them or use a code loop if you can't figure out the sequence.

 

[lioninwinter]

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]

Do what ever your heart desires. Write all the unnecessary code you want to write.

 

[lioninwinter]

Can someone else help here

 

[Ranman256]

use a form, put your values in the textboxes on the form,
run update query that uses them.

 

[Gasman]

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]

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]

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]

(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!