Change null values in lookup query 1

[danwand]

For the purpose of explaining the problematic situation I am only including those fields that are relevant to this particular query.
I have the following main table with the fields shown below:

tblOngoingHist:
OHID (int-primary key)
StaffID (int)
Time (int)
TopicDate(date/time)
AuditDate(date/time)

The field 'Time' holds an integer value that references another table(tblLookUpTime) that stores both the integer value and its corresponding string in terms of hours and minutes...
So tbkLookUpTime holds the following values:

TimeInt(Int - Primary Key) TimeStr(String)
15 15mins
30 30mins
60 1 hour
75 1hr 15mins
90 1hr 30mins
etc..(in 15 minute intervals upto 4hrs(integer value 240)

The query i am building should display the following fields:
tblOngoingHist.StaffId(matches specified criteria, i.e. a combobox on a form)
tblOngoingHist.TopicDate
tblOngoingHist.AuditDate
tblLookUpTime.TimeStr (INNER JOIN tblOngoingHist ON tblLookUpTime.TimeInt = tblOngoingHist.Time)

this works fine if all fields have a value in them, however there will be cases where just an audit date will be entered without a value for tblOngoingHist.Time.
When this is the case the following query doesn't work as expected:

SELECT tblOngoingHist.TopicDate, tblLookUpTime.TimeStr, tblOngoingHist.Auditdate
FROM tblLookUpTime INNER JOIN tblOngoingHist ON tblLookUpTime.TimeInt = tblOngoingHist.Time
WHERE (((tblOngoingHist.StaffID)=[Forms]![frmTrainSwitch]![subTrain].[Form]![cmbemp]));

It doesn't show results for those records that do not have a value in tblOngoingHist.Time.

Could anyone suggest a workaround for this situation, i know there is both the Nz and IIf functions but i can't seem to make them work for this situation where all records should be shown irrelevant of which feilds have values or not.

 

[danwand]

Sorry...

ideally i would like to display "N/a" in the field tblLookUpTime.TimeStr = no integer value exists in the field tblOngoingHist.Time

Thanks in advance
Dan

 

[PHV]

You may try this:
SELECT tblOngoingHist.TopicDate, tblLookUpTime.TimeStr, tblOngoingHist.Auditdate
FROM tblOngoingHist LEFT JOIN tblLookUpTime ON tblOngoingHist.Time = tblLookUpTime.TimeInt
WHERE (((tblOngoingHist.StaffID)=[Forms]![frmTrainSwitch]![subTrain].[Form]![cmbemp]));


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[danwand]

Thanks PHV that worked fine, i don't suppose you could spare a little time to explain the logic behind this solution, because although i can see it works, i don't fully appreciate why it works!

If not - no problem, thanks for your time anyway...

Dan

 

[PHV]

I used a left outer join between Hist and Time, ie take ALL the records from Hist and only the related from Time.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244