Query to get earliest date 1

[Dophia]

Hi Everyone: I would appreciate any help with this problem. I would like to see if an assessment was done, and if not, enter the date it should be done.

Example:
1st Assessment
2nd Assessment: 7 days after 1st
3rd Assessment: 21 days after 1st
etc

I have the following in a query to estimate the next assessment dates, if they are not done.

WeekOneDate: Format(IIf([MaxDate]-[Arrival_Date]<7,([Arrival_Date]+7),"-"[/color red]),"Mmm-dd-yy")

The problem is the "-"[/color red] in the above expression. I cannot get the earliest (least) date from the assessments, in the expression below, since it doesn't find a date. Any suggestions? Sophia

TheLeast: Format(GetLeastDate(Nz([WeekOneDate],0),Nz([WeekSecondDate],0),Nz([MthOneDate],0),Nz([MthlyDate],0)),"Mmm-dd-yy")

 

[Remou]

Are you saying that WeekOneDate is never zero as a dash is used for zero entries? If so, IIf should suit:


[tt]TheLeast: Format(GetLeastDate(IIf([WeekOneDate]="-",0,[WeekOneDate]),IIf([WeekSecondDate]="-",0,[WeekSecondDate]),IIf([MthOneDate]="-",0,[MthOneDate]),IIf([MthlyDate]="-",0,[MthlyDate])),"Mmm-dd-yy")[/tt]

http://lessthandot.com

 

[dhookom]

It looks like you have created a spreadsheet with multiple date columns. I don't think I would do this. Also, you have created expressions that return strings when I would limit the returns to Null or a date. I also don't care for hard-coding most numbers into either expressions or column names.

Assuming you are tracking client assessments. My normalized table structure would be something like:

[tt][blue]
tblClients
======================
cliCliID autonumber primary key
cliFName
cliLName
cli....

tblAssessments
=====================
asmAsmID autonumber primary key
asmName values like "Week One", "Week two",...
asmDaysFrom values like 7, 14, 30, 60

tblClientAssessments
======================
claClAID autonumber primary key
claCliID links to tblClients.cliCliID
claAsmID links to tblAssessments.asmAsmID
claRecommendDate
claActualDate
claComments
[/blue][/tt]


Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Thanks Remou. That works great!

Sophia

 

[Dophia]

Duane: Thanks for your reply. I do feel that I have normalized the tables. I didn't show all my data since I was only having trouble with the above expression. My "assessment" table is as follows:

tblAssessment_Details
PS_NO= Links to Animal table
Assess_Date = date
Assess_Type = 1st, 2nd, etc Assessment.

The WeekOneDate, WeekSecondDate, etc are expressions in my query, for a report that I am sorting by "earliest date".

Thanks,
Sophia

 

[dhookom]

That's better than I had thought based on your first post. Glad to read you got your question resolved.

Duane
Hook'D on Access
MS Access MVP