Dlookup help needed! (1 Viewer)

[sperato]

Hi all,

I'm new to the site and fairly new to Access.

I'm sure this is easy but I'm struggling and have searched a lot and can't seem to get this to work.

I have a form that a worker uses to record details of runaways (children). Each time this happens they input into a new record with dates and details etc.

I have a query:

SELECT DISTINCT RHI.[Name of YP], Count(RHI.[Name of YP]) AS [CountOfName of YP]
FROM RHI
GROUP BY RHI.[Name of YP];

Which gives me the number of times that person has gone missing.

What I want to do is display on a form (my form is called RHI) in an unbound txtbox the number of times the person on that form record has gone missing, the txtbox on the form that their name is in is called "Name of YP".

So for example, if a worker is looking at a record for Ben, and Ben has 3 records (and my Query - TimesMissing - returns 3) I want 3 to be showing in the box "txtMissingTimes".

ANY help is greatly appreciated, I think I need to use a Dlookup but can't get it to work - I am trying this:

=DLookUp([TimesMissing]![CountOfName of YP],[TimesMissing],[Name of YP])

and variations but no luck, only a #name error.

TIA

 

[ConnorGiles]

Try the control source of the unbound text box and add "=Count([Name of YP])"

 

[sperato]

Try the control source of the unbound text box and add "=Count([Name of YP])"

Thanks, I tried this, but this is now just returning the total number of records, rather than the total number of records for the YP who's name is in the Name of YP textbox.

 

[ConnorGiles]

is this text box bound to the query in which you type the name of the young person?

maybe that's your issue.

 

[sperato]

is this text box bound to the query in which you type the name of the young person?

maybe that's your issue.

and my newbie status now comes to the front as I'm not 100% sure what you mean - I think it is unbound. My understanding of it was that it was unbound unless there was a control source. I tried your suggestion of the control source value and that came back with the total number of records.

Just to clarify, my query dose not have parameters and just returns a table with names and the number of records associated with those names.

Thanks again,

 

[CazB]

If you are using a form to put the person's name in, and the query you are wanting to lookup the TimesMissing is not the recordsource for the form, then you could try this, in an unbound textbox on your form?

=DLookUp("[CountOfName of YP]","TimesMissing","[Name of YP]=" & [Forms]![RHI]![Name of YP])

 

[sperato]

If you are using a form to put the person's name in, and the query you are wanting to lookup the TimesMissing is not the recordsource for the form, then you could try this, in an unbound textbox on your form?

=DLookUp("[CountOfName of YP]","TimesMissing","[Name of YP]=" & [Forms]![RHI]![Name of YP])

Thanks, but that is now showing #error

 

[ConnorGiles]

Ahh, Thanks for the clarification.

All you would need is a find duplicates query.

make the criteria on [Names of YP]

and it should then show the number of times each person has been missing.

then set your control source of the report to the find duplicates query

 

[CazB]

could be I got some field / table / query names wrong... check those?

 

[pr2-eugin]

How about using a DCount instead?

= DCount("*", "RHI", "[Name of YP] = '" & Forms![COLOR=Blue][B]yourFormName[/B][/COLOR]![COLOR=Red][B]theNameOfThecontrol[/B][/COLOR] & "'")

Try matching the highlighted sections to match your design !

 

[sperato]

thanks all but i cant get any of these to work - im sure its something im doing! as its not imperative think ill leave it for now!

 

[ConnorGiles]

I wouldn't just leave it sperato,

if you would, post a sample database and I shall personally try and solve this issue for you.