Merging Last and First names and a Yes/No question..

[airbus2]

My DB has a table that lists our school Instructors. With three of the text fields being "Instructor", "FirstName",
"LastName". How can I get the "Instructor" field to show "LastName, FirstName" I can create it using an "unbound" on my "Instructors" Form but I want it in the table if possible. Also. I have a field called "Certified". This is a Yes/No field and also a "CertifiedDate" field. I have created an unbound field on the Form for "ValidThroughDate" that is 365 days later than the "CertifiedDate". How can I get the Yes/No to auto say "Yes" if "todays date" is between the two dates and "No" if it is not? Much thanks from this novice.

 

[dhookom]

Do you have a good reason to store a value that can be easily calculated? Typically that is not recommended.

Duane
Hook'D on Access
MS Access MVP

 

[airbus2]

No reason. Not sure what part of my request for help you are refering to though. The first section as it relates to the name; I was just wanting to know if there is a way to store it in a table. If not, thats cool. The second part of my question; I have attempted numerious calculations to no avail. Since this post I have changed the Yes/No table field to an unbound field on the form. Still trying to make an expression to do what I need that field to do. "Certified" or "Not Certified" if todays date is between the two other dates. All of the examples I have read discuss how to separate records to meet an expression through a query.

 

[dhookom]

You don't want to store any of the calculated parts even the second part. Assuming you have a date field CertifiedDate that is the most recent certification then you use two calculated values in forms or reports:
CertifiedThroughDate: =CertifiedDate + 365
Certified: =(CertifiedDate + 365) > Date()

Duane
Hook'D on Access
MS Access MVP

 

[airbus2]

Excuse me for not explaining this is a cohesive way. Each instructor has five (one year) certifications that they need to maintain. I have three fields for each certification 1) [Certified Date] (Date they took their certification class. 2) [Valid Through Date] using this formula ”=DateSerial(Year([ Certified Date]),Month([Certified Date])+12,Day([Certified Date]))” to make it one year later. The filed I need help with is field 3)[Certification Status]. This unbound field should be populated with one of three responses based on a “current date”. Response 1 “No Certification Present” (There is no date in [Certified Date] field because they have not taken the class yet. Response 2 “Certified” (Today’s date is between the [Certified Date] and [Valid Through Date]. Response 3”Certification Overdue”. I know this is probably an easy calculation but I am just not getting it. Any help you lend is greatly appreciated.

 

[dhookom]

Excuse me for not explaining myself correctly. You don't want to store any value other than the CertifiedDate. The other two values can be calculated and displayed. Don't store them. I would set a control source of a text box to:

 =IIf(IsNull(CertifiedDate),"No Certification Present",IIf((CertifiedDate + 365) > Date(),"Certified","Certification Overdue"))

Do you actually have one record for all 5 certifications or does each certification have its own record? Can you share your table structure(s)?

Duane
Hook'D on Access
MS Access MVP

 

[airbus2]

One record has the five certification date fields. Thank you sooooooo much. It worked! Imagine that? lol. Im slowly getting there, be patient with me. How do I get the [Valid Through Date] field to not show error when there is no date in the CertifiedDate field? Im using this formula "=DateSerial(Year([Certified Date]),Month([Certified Date])+12,Day([Certified Date]))

 

[dhookom]

What do you want to display

CertifiedThroughDate: =IIf(IsNull(CertifiedDate),Null,DateAdd("yyyy",1,CertifiedDate))

If you record the five certifications in one record then IMO, this is wrong. Each certification should be in its own record. What would you do if you needed to add another certification? This is normalization.

Duane
Hook'D on Access
MS Access MVP