Pass paramter to SQL Criteria - Is Not Null And <Date()-1 - (1 Viewer)

Hi Guys


I have tried to figure this out but can't so I'm hoping I'm missing something easy that somebody will help me solve.


I have a query that basically chooses records from a table that contains a list of Invoices with a field, [InvoiceDate] based upon the following criteria:


Is Not Null And <Date()-1


This works well.


I have another table, "ConfigurationInformation" that has a single record that contains the various configuration and setup parameters for the the application.


It contains a field called [NumberOfDays].


I want to pass the contents of the [NumberOfDays] field the Date so that it looks something like the following:


Is Not Null And <Date()- [NumberOfDays]


I just can't figure it out.


Thanks for any help you can provide.


Cheers
Greg

Have you included the table "ConfigurationInformation" in your query?
Show the whole SQL string.

Hi JHB,


Sorry for not providing the entire query but it is quite big and I was trying to explain the issue more simply.


Yes, I did include the table in my query but it displayed the attached error message.


Here is the query as it currently stands:


SELECT Workorders.WorkorderID, Customers.ContactFirstName, Customers.ContactLastName, Customers.MobNumber, Customers.EmailAddress, Workorders.DateReceived, [Sum Of Payments Query].[Total Payments], Customers.CustomerID, Workorders.Date_WO_Sent, Workorders.ProblemDescription, Workorders.DatePickedUp, Format([Parts Total],"Currency") AS WOTotal, [Parts Total]-[Total Payments] AS [Total Owing], Workorders.WorkOrderType, [Parts Totals by Workorder Money Owing].*, Workorders.PurchaseOrder, Workorders.ReminderCount, Workorders.DateRequestDeposit, Workorders.DateRequestBalance, Workorders.Valid, Workorders.CancelledDate, Workorders.ExcludeFromAutoReminder, Int([DatePickedUp]) AS ReminderDateCheck, tWorkorderType.WorkOrderTypeDesc, DateDiff("d",[DatePickedUp],Date()) AS Expr1
FROM tWorkorderType INNER JOIN (Customers INNER JOIN ((Workorders LEFT JOIN [Sum Of Payments Query] ON Workorders.WorkorderID = [Sum Of Payments Query].WorkorderID) INNER JOIN [Parts Totals by Workorder Money Owing] ON Workorders.WorkorderID = [Parts Totals by Workorder Money Owing].WorkorderID) ON Customers.CustomerID = Workorders.CustomerID) ON tWorkorderType.ID = Workorders.WorkOrderType
WHERE ((([Sum Of Payments Query].[Total Payments])="$0.00") AND ((Customers.CustomerID)<>51 And (Customers.CustomerID)<>1) AND ((Workorders.Date_WO_Sent) Is Not Null Or (Workorders.Date_WO_Sent)<Date()-[NumberOfDays]) AND ((Workorders.DatePickedUp) Is Null) AND (([Parts Total]-[Total Payments])>0) AND ((Workorders.PurchaseOrder)<>Yes) AND ((Workorders.Valid)<>No) AND ((Workorders.ExcludeFromAutoReminder)<>Yes) AND ((tWorkorderType.WorkOrderTypeDesc) Like "*Parts and Other Items*" Or (tWorkorderType.WorkOrderTypeDesc) Like "*Wheels*"))
ORDER BY Workorders.WorkorderID DESC;



Cheers
Greg

Sorry, I can't see you've included the table "ConfigurationInformation" in your query, from where you should get the "[NumberOfDays]"?
Could you post your database with some sample data, (zip it) + name of your query.

Hi JHB,


I'm in the process of stripping down my database into a smaller version. I have also switched to using the DateDiff function in the query...give me a few minutes.

I'm back!


Ok, I have attached a small sample database with the following:


1. Table - Workorders
2. Table - My Company Information

3. Query - Workorders Query
4. Form - fWorkorders


In the query, you will see where I am using the following to filter the records in the Workorders table:


WoDays: DateDiff("d",[Date_WO_Sent],Date())


Is Null Or >=2


There is a field in the table "My Company Information" called [AutoRemindersDays] that contains the number of days I wish to use in place of the number "2" in the Is Null Or >= 2 criteria.

I hope I have made myself clear with the above explanation.

Cheers

I change value of [AutoRemindersDays]=6 so you can see the result.

Thanks JHB but adding the My Company Information table to the actual complex query that I am using causes the error message that I attached in my previous post....

Is the table all ready added, (and is the table actually called "My Company Information").?

Another way is to create a query for table "My Company Information", see attached example.

Same error message and the table is called My Company Information

Sorry, if you can't figure out, then I think the only way to help you is to get the real query (and tables)!

I will put together a cut down version of the database tomorrow and get it to you...thanks for helping me with this.


Cheers

just like to point out

Is Null Or >=2

the is null is irrelevant, if Date_WO_Sent is null, the datediff function will fail with an error before it even gets to the criteria

Hi CJ,


I'm not seeing any errors.


How would I change the code to handle this type of condition?

presumably you are not seeing errors because Date_WO_Sent is always populated.

how you handle it if it can happen depends, but typically you would use the nz function

The Date_WO_Sent is not always populated that is why I was checking for nulls...I'll have a play with the NZ function

if you want nulls always to be shown use

DateDiff("d",nz([Date_WO_Sent],Date()),Date())

and if you don't

DateDiff("d",nz([Date_WO_Sent],Date()+3),Date())

(think I've got that the right way round

Thanks mate