Date Question

  • Thread starter Thread starter scott04
  • Start date Start date
S

scott04

Hello everyone,

I have a database that has two fields that are used in order for a record to
be complete. The are DateSatRsp (Date of Satisfactory Response) and
DateUnsatRsp (Date of Unsatisfactory Response). When the user enters the
record is complete code is generated to make sure a date is enter is at least
one of these fields.

Here come my query question and i apologize if this should be in the report
forum. I am trying to run a report than gives me turntime but it would be
turntime from one of these dates. Would i need to have a query to decide if
one field is present use the other field? Any thoughts are appreciated.
 
TurnTime: [RecordStartDate]-IIF([DateSatRsp] Is Null, [DateUnsatRsp]
,[DateSatRsp])
 
While it might the the common approach if you were using a spreadsheet,
having two separate columns for dates that include data as part of the
column name (i.e., Satisfactory, Unsatisfactory) is not a good use of the
relational database Access represents.

If you want to get the best (and easiest) use of Access'
relationally-oriented features/functions, you can't feed it 'sheet data.

Consider the following change:

tblYourTable
YourTableID
ResponseDate (date/time field)
ResponseType (this is where you'd note "Satisfactory" or not)

In fact, you might be able to get away with using a simple Yes/No field
(?Satisfactory Response?).

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top