A checkox is not doing what I intended... 2

[rmork]

I have a form that prints a report based on a ship's sailed date. Some ships have no sailed date, and I would like to print those separately. To achieve this I have put in a checkbox, that when is checked only those ships with sailed date = null will print:
The checkbox is called "SD", and the criteria used in the reports query is:
"IIf([Forms]![Avd01OrderListSD]![SD]=Sann;([AVD01Orders].[Sailed]) Is Null;([AVD01Orders].[Sailed]) Between [Forms]![Avd01orderListSD]![PeriodFrom] And [Forms]![Avd01OrderListSD]![PeriodTo])"

Sann in this statement is the same as True (norwegian access 2003).

The checkbox is not doing what I intended. Why?
Do I have to do something with the checkbox to get it to work (define true/false or something)??

 

[PHV]

What about this ?
"AVD01Orders.Sailed Between [Forms]![Avd01orderListSD]![PeriodFrom] And [Forms]![Avd01OrderListSD]![PeriodTo] OR ([Forms]![Avd01OrderListSD]![SD]=Sann AND AVD01Orders.Sailed Is Null)"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rmork]

Thanks PHV, but that did not do it..
It lists both with and without sailed date. I wanted a separate report for those without sailed date, so when you tick the box it should ignore the "from" "to" - fields, and only list blank sailed dates...

Am I making sense?

 

[TheAceMan1]

How are ya rmork . . .

[blue] . . . when is checked only those ships with sailed date = null will print . . .[/blue]

If I'm not mistaken there's only two criteria above. [blue]Sailed Date = Null[/blue] and [blue]SD = True[/blue].

Would it not be easy enough to set the criteria in a query or SQL? . . . or am I missing something?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[PHV]

Point taken:

"([Forms]![Avd01OrderListSD]![SD]<>Sann AND AVD01Orders.Sailed Between [Forms]![Avd01orderListSD]![PeriodFrom] And [Forms]![Avd01OrderListSD]![PeriodTo]) OR ([Forms]![Avd01OrderListSD]![SD]=Sann AND AVD01Orders.Sailed Is Null)"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rmork]

Hi TheAceMan1..
I'm not a programmer, so I'll just have to take your word for it...
To me, it made sense to use the form that was already made, and ad a little more functionality to it. Since I already had the report and form made, and the code was in there I thougt that this was the easyest way out...

The code above is pasted from the criteria-field in the query of the report...
Is this not what you mean?

Please explain to a novice...

When I look at my query in SQL-view it looks like this now:

SELECT qryAVD01ProjectsPrincipalsBooked.PrincipalNumber, qryAVD01ProjectsPrincipalsBooked.PrincipalName, AVD01Orders.OrderNumber, AVD01Projects.Projectnumber, Vessels.Vesselnumber, Vessels.Vesselname, Clients.ClientNumber, Clients.ClientName, AVD01Orders.Sailed, funcAVD01OrdersGetClientTermDateLast([AVD01Orders].[AVD01OrderID]) AS LastDate, funcAVD01OrdersGetCurrency([AVD01Orders].[AVD01OrderID]) AS [Currency], funcAVD01ProjectGetLumpSum([AVD01Projects].[AVD01ProjectID]) AS TotalAmount, funcAVD01OrdersQueryGetCalcCommissionPercent([AVD01Orders].[AVD01OrderID]) AS CommissionPercent, ([Commission]*[exchangerate])-([Installments]) AS Difference, AVD01Orders.FullyPaid, AVD01Orders.ExpectedDueDate, Currencies.ExchangeRate, AVD01Orders.InvoiceNumber, AVD01Orders.InvoiceDate, AVD01Orders.OrderDate, funcAvd01OrdersGetLumpsum([AVD01Orders].[AVD01OrderID]) AS LumpSum1, funcAVD01OrdersQueryGetCalcCommission([AVD01Orders].[AVD01OrderID]) AS Commission, funcAVD01OrdersGetInstallmentsTotal([AVD01Orders].[AVD01OrderID],False)*[exchangerate] AS Installments, qryAVD01ProjectsPrincipalsBooked.PrincipalID, AVD01Orders.AVD01OrderID, qryAVD01ProjectsPrincipalsBooked.LumpSum, Currencies.ExchangeRate
FROM (Clients INNER JOIN Vessels ON Clients.ClientID = Vessels.ClientID) INNER JOIN (((AVD01Projects INNER JOIN qryAVD01ProjectsPrincipalsBooked ON AVD01Projects.AVD01ProjectID = qryAVD01ProjectsPrincipalsBooked.AVD01ProjectID) INNER JOIN Currencies ON qryAVD01ProjectsPrincipalsBooked.CurrencyID = Currencies.CurrencyID) INNER JOIN AVD01Orders ON AVD01Projects.AVD01ProjectID = AVD01Orders.AVD01ProjectID) ON Vessels.VesselID = AVD01Projects.VesselID
WHERE (((AVD01Orders.Sailed) Between [Forms]![Avd01orderListSD]![PeriodFrom] And [Forms]![Avd01OrderListSD]![PeriodTo]) AND ((qryAVD01ProjectsPrincipalsBooked.PrincipalID)=IIf([Forms]![Avd01Orderlistsd]![PrincipalID]=-1,[qryAVD01ProjectsPrincipalsBooked].[PrincipalID],[Forms]![Avd01orderlistsd]![PrincipalID])) AND (([Forms]![Avd01OrderListSD]![SD])<>True)) OR (((AVD01Orders.Sailed) Is Null) AND ((qryAVD01ProjectsPrincipalsBooked.PrincipalID)=IIf([Forms]![Avd01Orderlistsd]![PrincipalID]=-1,[qryAVD01ProjectsPrincipalsBooked].[PrincipalID],[Forms]![Avd01orderlistsd]![PrincipalID])) AND (([Forms]![Avd01OrderListSD]![SD])=True)) OR ((([Forms]![Avd01OrderListSD]![SD])=True));

Ehhh...?

 

[rmork]

I made it...
I must be sleeping!!
Thanks guys. You came through again...! The code was working, I was using it wrong. (Not used to SQL view.. )
Have a star!