dlookup help 2

[patrichek]

Hi,

i have a query that calculates the amount of weeks since a patient had a surgery. it works fine except i'd like the total to show up on a form correctly.

I'm able to make it show up but its showing the same total for every patient.

I'm sure it something i'm not doing right in my query. here's the code for the query:

SELECT [PatStatusTbl].Date, [PatStatusTbl].Type, [PatStatusTbl].Status, DateDiff("y",[PatStatusTbl].[Date],Date())/7 AS WeeksPost, StatsTbl.StatsId
FROM [PatStatusTbl] RIGHT JOIN StatsTbl ON [Patient Status Table].ContactID = StatsTbl.ContactId
WHERE ((([PatStatusTbl].Date) Is Not Null) AND (([PatStatusTbl].Type)="Surgery") AND (([PatStatusTbl].Status)="Performed"));

I've tried pointing my query to the ID field in my form but i received #error# in doing that.

Also, how do i round off the amount of weeks to 1 decimal place?

thanks in advance!

 

[Pampers]

Hi patrichek,
Eh, don;t see DLookup, but
the rounding could be done like this:

Weekspost: Round(DateDiff("y",[PatStatusTbl].[Date],Date())/7,1)

I (re)created the beneeth query to calculate the weeks spent in the hospital. The result of the query I show in a subform on the mainform, where the Link Master/Child Field is set to ContactID. If you don't set this property the result can be the same for all the patients (being the result of the first record in the query)

SELECT PatStatusTbl.Date, PatStatusTbl.Type, PatStatusTbl.Status, StatsTbl.StatsID, Round(DateDiff("y",[PatStatusTbl].[Date],Date())/7,1) AS Weekspost, StatsTbl.ContactID
FROM PatStatusTbl RIGHT JOIN StatsTbl ON PatStatusTbl.ContactID = StatsTbl.ContactID
WHERE (((PatStatusTbl.Date) Is Not Null) AND ((PatStatusTbl.Type)="Surgery") AND ((PatStatusTbl.Status)="Performed"));

Pampers
Just let it go...

 

[dhookom]

I don't see any DLookup() as your thread title suggests. I would expect to see DateDiff("d",[Date],Date())/7 to get weeks or even DateDiff("ww",[Date],Date()).

You can round to 1 decimal by multiplying by 10 then converting to an integer and then dividing by 10. You might also look at the Round() function.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[patrichek]

sorry guys, i'll post the dlookup:

=DLookUp("WeeksPost","DaysPostSXQRY")

so back to my original problem, I posted the query because i thought it was the culprit as it was giving me the the weekspost for all the records in my db.
So how would i make this only show the weekspost for the current record in my form?

thanks!
by the way the rounding works!

 

[dhookom]

The third argument of DLookUp() is where you specify a where clause describing "th current record in my form". Usually this is something like:

=DLookUp("WeeksPost","DaysPostSXQRY","PatientID=""" & [PatientID] & """")

You have to uniquely identify the record and field from [DaysPostSXQRY]. If you don't, you will return a random value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[patrichek]

update:

i've tried Pampers query, it rounds very nicely but the weeksPost still show the same amount for every record no matter what record i'm on.
i've also tried Duane's argument and i'm getting a constant cursor flash while calculating finally ending in #error#

here's my argument:
=DLookUp("WeeksPost","DaysPostSXQRY","ContactID=""" & [ContactID] & """")

if i go back to my original argument i don't receive the error but get the same amount for every record just like the first scenario.

what do you think fellows?

 

[patrichek]

ok guys, i got it to work using this argument:
=DLookUp("WeeksPost","DaysPostSXQRY","ContactID=" & [ContactID])

but it constantly is calculating and cursor flashing, anyway to fix that?

thanks!

 

[dhookom]

Do you have any code requerying or recalcing the form?
Is the form single or continuous?
Do you need to be able to edit records in the form?
Does your form open with all records or only a single client?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[patrichek]

Duane,
this dlookup is on a subform in datasheet view. Its like a call log and shows all the call records for the record on the main form. We need to edit the records, well really just input new records.
I don't have a requery or recalc code.
I was thinking about moving the dlookup to the main form, it doesn't really need to be on the subform.

 

[Pampers]

Patrichek,
Are you sure that the subform with the Weekpost on it has is Link Child/Master field set???

Pampers
Just let it go...

 

[patrichek]

Pampers,
yes its master and child link is ContactID

 

[dhookom]

If the calculation would work in the main form then don't place it in the subform.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[patrichek]

thanks guys!