date calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a database to track prescriptions and when they will need to be
refilled. Can someone help me with a query that calculates a “projected
refill dateâ€. I have fields called “Date last filledâ€, “QTYâ€, “Amt used per
dayâ€.

I though the formula would be something like

[Projected Refill Date] =[Medication]![Date Prescription Last
Filled]+([Medication]![Quantity]/[Medication]![Amount Used per Day]) but it
will not work. Any suggestions?
 
I am creating a database to track prescriptions and when they will need to be
refilled. Can someone help me with a query that calculates a “projected
refill date”. I have fields called “Date last filled”, “QTY”, “Amt used per
day”.

I though the formula would be something like

[Projected Refill Date] =[Medication]![Date Prescription Last
Filled]+([Medication]![Quantity]/[Medication]![Amount Used per Day]) but it
will not work. Any suggestions?
Click to expand...

Take a look at the DateAdd() function. In a vacant Query Field cell
you should be able to type

[Projected Refill Date]: DateAdd("d", [Quantity]/[Amount Used Per
Day], [Date Prescription Last Filled])

A couple of points: the = sign is valid in VBA code but not in
Queries; the colon : indicates an alias name for the field. And you
may want to consider using shorter fieldnames without blanks (you can
set the Caption property of fields to a human-readable string), but if
you'll ever migrate this data to client/server you'll find that blanks
can cause problems. A fieldname of [AmtUsed] will keep your queries
more compact, readable, and efficient!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top