DCount Combining Property Name & Wildcard

[tailored]

Hi

I'm building a report that uses a specific table [tblDataRecords] and I'm using textboxes to present specific results that have stripped out certain other results.
In order to achieve the desired output, I've created the following DCount but need to combine a textbox property name and a wildcard.

The textbox contains a formatted date in this format: yyyymmdd

Here are my Control Source DCounts that do not work :

=DCount("[RCODE]","tblDataRecords","[RCODE] = '99' AND [NAME] ='George Lucas' AND [REFERENCE] LIKE [RefDate]'*'")

=DCount("[RCODE]","tblDataRecords","[RCODE] = '99' AND [NAME] ='George Lucas' AND [REFERENCE] LIKE [RefDate]&'*'")

To elaborate, my [REFERENCE] column in my table is made up of mixed data and I want to find only the records that start with a date, as formatted above, while also filtering for the other criteria in the DCount.

I have managed to get the following source to work :

=DCount("[RCODE]","tblDataRecords","[RCODE] = '99' AND [NAME] ='George Lucas' AND [REFERENCE] LIKE'202*'")

... where "202" represents the beginning of 2021... but I am using a referenced date in the textbox [RefDate] as this is dynamic and changes based on =Now()-x ...

So, as the date will have changed each time the Access file is opened, the following is my query:

• I need to combine the property name [RefDate] and a wildcard of '*' in order to search for fields in the [REFERENCE] that match yyyymmdd and the next 10 unknown numeric characters.
  
  • for example
    
    • 202103161234567890

The finished control source calculation will be placed in another text box.

Thanks in advance for any guidance.

[Welshgasman]

You do not use quotes for numerics if that RCODE is numeric?
I do not think you can use Like with Dates?

Use the Year(),Month(), Day() functions perhaps?

[accesstos]

Give a try to this way:

"[RCODE] = '99' AND [NAME] ='George Lucas' AND [REFERENCE] LIKE '" & [refDate] & "*'"

or

"[RCODE] = '99' AND [NAME] ='George Lucas' AND [REFERENCE] LIKE '" & Left(year([refDate]),3) & "*'"

[tailored]

Thanks accesstos

The second version has worked... couldn't get the first one to play nicely.
Thanks for your help... very much appreciated.

[tailored]

Thanks Welshgasman

The RCODE is numeric however I don't need to use it in calculations, so interpreting it as text is fine in this instance.
How would I format your suggestion into my existing DCount formula?

Thanks again

[accesstos]

Thanks accesstos

The second version has worked... couldn't get the first one to play nicely.
Thanks for your help... very much appreciated.

You are welcome!

At first, I posted the first version assumed that the textbox contains text and then I realized that is about formatted date.

You can also use the Format() function to specify the date range of desired records for the specific
year: Format([RefDate],"yyyy")
month: Format([RefDate],"yyyymm")
or day: Format([RefDate],"yyyymmdd")


For example, to get the count for the specific month, the criteria become:

"[RCODE] = '99' AND [NAME] ='George Lucas' AND [REFERENCE] LIKE '" & Format([RefDate],"yyyymm") & "*'"

Cheers,
John