dcount wildcards

[gbmarlysis]

if i have a field filled like this.....lathl15/06/11 how can i use a dcount to calculate how many records are before a certain date shown in a text box.
thanks

[June7]

what is the 'lathl' part - does this change in characters and length?

[gbmarlysis]

its a description of who hosted the race meet, always 4 letters but characters change

[gbmarlysis]

was upposed to show r1 at the end to indicate race number

[June7]

I see 5 characters before the date. Will have to extract the date part. Assuming the date structure can be relied on (dd/mm/yy with placeholder zeros).

Unfortunately, CDate(Mid("lathl15/06/11",6)) reads the 15 as year so this gets a little complicated.

x = "lathl15/06/11"
CDate(Mid(x,9,2) & "/" & Mid(x,6,2) & "/" & Right(x,2))

Now apply date value as filter criteria on this constructed field.

[gbmarlysis]

is it easier to extract the date in a table.
make it its own field in Race results table

[June7]

Didn't see your other post.

CDate(Mid(x,9,2) & "/" & Mid(x,6,2) & "/" & Mid(x,12,2))

You mean a calculated field in table? Maybe, haven't used that new Access 2010 datatype yet. Try it.

[gbmarlysis]

that code you typed june where would it go in a dcount expression, and if you could explain it a bit that would help me understand the layout for future expressions
TY

[June7]

I am suggesting you use that to create a field in a query, like:

RaceDate: CDate(Mid(x,9,2) & "/" & Mid(x,6,2) & "/" & Mid(x,12,2))

Use field name in place of x.

Then use the query in place of table in the DCount

=DCount("[Athlete ID]", "[query name here]", "[Athlete Id]='" & Me.Combo35 & "' AND RaceDate<#" & Forms!mainformname.datetextboxname & "#")

If you use report Grouping & Sorting with aggregate calcs functionality to manipulate and organize data, the DCount might not be required.

[gbmarlysis]

what does the code Mid(x,6,2) mean
ty

[gbmarlysis]

hi i typed this '=CDate(Mid("[Race Id]",9,2) & "/" & Mid("[Race Id]",6,2) & "/" & Mid("[Race Id]",12,2))' into the criteria of a new field in a query
raceid as the field
says data type mismatch in criteria expression

[June7]

Not what I suggested. I said create a field in query with that expression.

RaceDate: CDate(Mid("[Race Id]",9,2) & "/" & Mid("[Race Id]",6,2) & "/" & Mid("[Race Id]",12,2))

Then use reference to the form textbox as criteria under this constructed field.

You really should keep these data in separate fields.

[gbmarlysis]

sorry no idea what all that means thanx for your help anyway. i created a new field in the query not sure where to put the expression. going to go try a dif approach.

[June7]

Query in design view.

The expression goes on the Field row of query.

Reference to textbox goes on Criteria row of query under that field.

[gbmarlysis]

that makes it so much clearer... thanks June. i took your advice on creating a new date field and used the above AND function with the date and it works great. if theres no value in the date text box the dcount textbox says error but i can live with that, as soon as i put in a date it works.