Solved DCount using a Dim reference (1 Viewer)

[ECEK]

I am looking to create a check to see if a concatenated field is already in a table. It works if my criteria is text but it doesn't work when I'm using a Dim concatenation. Here is my Code:

Private Sub Command10_Click()

Me.UniqCONCAT = (Me.CQRWeekNumber) & (Me.UserNametxt) & (Me.CQRSite)

Dim KONKAT As String
KONKAT = Me.UniqCONCAT

Dim CountNumber As Integer
CountNumber = DCount("UniqCONCAT", "tblDataTable", "UniqCONCAT = 'KONKAT'")

If CountNumber > 0 Then
   MsgBox "This record is already in the system"
   Exit Sub
Else
   MsgBox "Go Ahead"

End Sub

Could you advise why this is not working.

Your advice and patience is (as always) greatly appreciated in advance.

 

[Ranman256]

you don't put quotes around variables or constants:
…, "UniqCONCAT =" & KONKAT)

 

[ECEK]

Hi Ranman
I altered my code to this (as suggested)

CountNumber = DCount("UniqCONCAT", "tblDataTable", "UniqCONCAT =" & KONKAT)

However I get a SYNTAX error (missing operator) in query expression.
Thank you for trying to help though.

Am I going about this the right way? I just want to check if a concatenation of fields is already in a table. I figured that counting howmany times it appears in the table would do the trick?

I'm sure it's got to be my syntax but I've tried all manner of permutations, to no avail !! Arrrrrrrrrrrrrrr!

My code doesn't pull any errors, it just doesn't work!

I have tried putting MsgBox periodically to see if the data is correct (and it is) so it MUST be the syntax of this DCount Line.

 

[Minty]

You need to escape the string on your statement;

CountNumber = DCount("UniqCONCAT", "tblDataTable", "UniqCONCAT = '" & KONKAT & "'")

so surround the value in single quotes.

 

[ECEK]

Hi Minty (long time no see)

And once again you are my Angel of Sweet Tray !!

Mark as complete.
I trust you are well, I was just revisiting our old posts (2016) Thanks for your solution.