SQL Query problem

[feedz87]

StrSQLQ1 = "SELECT " & _
    "CustomerCharges.JobOrderID, CustomerCharges.ClientID, CustomerCharges.ActivityDate, CustomerCharges.TaskName, CustomerCharges.Charges, CustomerCharges.LineSeqID, CustomerCharges.IIDNo,CustomerCharges.consignee CustomerCharges.marking, CustomerCharges.rate, CustomerCharges.m3, CustomerCharges.tonn,CustomerCharges.labor, CustomerCharges.storg, CustomerCharges.overtm, " & _
    "JobOrderHeader.JobType, JobOrderHeader.CustomerName, JobOrderHeader.LedgerNo, " & _
    "JobOrderLine.LorryOutDt, JobOrderLine.FL, JobOrderLine.VesselIn, JobOrderLine.ContainerTruckNo, JobOrderLine.ContainerSz " & _
    "From " & _
      " { oj (WMS.dbo.JobOrderLine JobOrderLine INNER JOIN WMS.dbo.CustomerCharges CustomerCharges ON " & _
        "JobOrderLine.JobOrderID = CustomerCharges.JobOrderID AND " & _
        "JobOrderLine.LineSeqID = CustomerCharges.LineSeqID) " & _
        "INNER JOIN WMS.dbo.JobOrderHeader JobOrderHeader ON " & _
        "JobOrderLine.JobOrderID = JobOrderHeader.JobOrderID AND JobOrderLine.deletion_flag <> 'D'AND " & _
        " " & stringwithoption & _
        " " & billstatus & _
        "AND JobOrderHeader.JobType =  '" & Left$(job, 6) & "' AND CustomerCharges.dgrefno = 'None'} " & _
        
    "Order By " & _
        "CustomerCharges.ClientID ASC, " & _
        "CustomerCharges.JobOrderID ASC," & _
        "CustomerCharges.LineSeqID ASC "

- The query above is about query for 3 table.This query running as well.But 1 want to put one more query is JobOrderCargos.IIDNo.

- What i want is if JobOrderCargos.IIDNo = 'No data in DB' all data which JobOrderCargos.IIDNo=null will be shown.

- Anyone can help me how to put the new table because im beginner..

[prit005]

while creating the table you can add the defualt value to it

[BitBlt]

Just use the ISNULL function on JobOrderCargos.IIDNo just before the ORDER BY clause like so:

...
WHERE ISNULL(JobOrderCargos.IIDNo, 'No data in DB') = 'No data in DB' 
...

This will cause the non-null values in JobOrderCargos.IIDNo to be used for comparison (resulting in unequal), and NULL values to be replaced with the literal, which will cause the comparison to be equal.

I have to assume that you get the literal from some field in a form someplace (like a dropdown?) so you have to adjust the ISNULL parameter to refer to that field rather than the literal.

Hope this helps!