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..

Recommended Answers

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

Jump to Post

All 2 Replies

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

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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.